B4J Question Databases

[bas1]

Don't know if this is the right forum - oops sorry wrong Forum B4A should be B4J !

I've been looking at B4J and SQLite

I have a 20+ year old DOS program which has structure below and would like to keep with that - well it works !!!

Customer
Account,Company,Address,City,PostCode

InvoiceMaster
InvoiceNo,Account,InvDate,OrderNo,Good,VAT,Total

InvoiceDetail
InvoiceNo,Line,PartNumber,Description,Price,Vat,Total

Invoice Master Has 1 to Many relationship with Invoice Detail
Customer table has 1 to Many relationship with Invoice Master

I've been looking at SQLite and MySQL and can't get my head around with ID & Foreign keys
I've been looking at SQLite examples but not seeing the wood for the trees I guess

can anyone point me in the right direction?

Account is ALPHANUMERIC
Invoice Sequence changes every year 100001, 110001, 120001 etc

 

[Erel]

While this example is quite old it can help you get started with B4J and SQLite: https://www.b4x.com/android/forum/threads/dbutils-example.34611/#post-202874

It is recommended to use the latest version of DBUtils module should is available here: https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/

 

[MarkusR]

we using always a recordset id as link. it have pros and cons.
so you have
CustomerId template for addresses
InvoiceMasterId in this table here its better to save the customer address because when you edit customer data all invoices would change.
InvoiceDetailId and in detail you link to parent with field InvoiceMasterId
Invoice Sequence i would just use an own field, in database as unique.
for id i would use auto number increment. minimum 32 bit integer.
you need also a table contract & contract positions as template for invoice detail.
beside invoice detail you should also have the base data , a product/material or something.
in detail if you have 5 pieces for x money you need descripe what is 1 piece.

 

[bas1]

Thanks

Yes, I too do believe "Normalisation" is a good idea for Invoice type application!
So InvoiceMaster has the address details too..Customer file is used as a Lookup
and details copied to InvoiceMaster

My old DOS application has Compound key InvoiceNo + Line Number
Which means Line number is Incremented for each Invoice - which is very useful

But I guess I'll have to manage with Invoice Detail with a Line Number only as Primary key
they use Select * from InvDetail where InvNo=xxx

Yes, I have other files too such as Product file, Tax file etc... I just simplified for the question

 

[KMatle]

You can export and import via *.csv files to/from any DB by tools. I use SQLite and MySQL a lot. You can export all the data and import it to a new environment.

SQLite: http://sqlitebrowser.org/
MySQL: via console

Excel is a good helper, too (as long as the tables aren't too big). You can replace values very easy or add columns. Then export the sheet as *.csv and import it to a table.

Using autoincrement is a standard. You don't have to care about the "numbering" and can access every row by it's primary key like you described.