asked on

database design help

I have two three tables "Invoices" "Items" and "InvoiceItems".
Everytime an invoice is created I insert a new record on Invoices

InvoicesTable.Insert();

then inside the invoices form I have an option to start adding Items so I add items to the InvoiceItems.
But InvoiceItems has a reference ID on the Invoices but the ID is not yet know:

InvoiceItems.FieldByName('invoice'id)'.AsInteger := {we dont know yet the invoice ID}
InvoiceItems.FieldByName('itemid').AsInteger := Items.FieldByName('itemid'); // item id is known
InvoiceItems.Insert;

can someone help me understand how you solve this problem?

For your information I use Delphi and UniDac with SQLite DB. Although I need help on idea/design not specifically commands of UniDac.

Mez4343

The referenceID on InvoiceItems is a foreign key to the Invoice table. This means that the code deisgner requires that an Invoice be created before any InvoiceItems can be assigned to the Invoice.

This is a good practice to insure data integity. So based on your snipit, I would investigate how to get the InvoicesID from the InvoicesTable.Insert() method.

Post the Insert() method code if you need more help.

ASta

See docs/samples from UniDac and SQLite DB?

- you need get invoice Id and write it to InvoiceItems (in firebird - before post using generator, in oracle - before post using sequence?, in mssql - after post using select @@identity?, in SQLite - i don't know

ASta

add:

http://www.sqlite.org/faq.html#q1

find "sqlite3_last_insert_rowid()" - in UniDAC docs/samples? :)))

dinko1

ASKER

I already know about @@identity and last_insert_row but the problem is that Delphi handles this without query. Its a table entity and you just Table.Insert(); and when you are ready Table.Post();

ASKER CERTIFIED SOLUTION

ASta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASta

____InvoiceItems.Edit;
____InvoiceItemsINVOICEID.AsInteger := InvoiceID.AsInteger; // ! create fileds in dataset on design
____InvoiceItems.Post;