When you have a database for example with invoices and invoices lines, they are linked with the invoice_id of the invoice.
it’s good practice for the invoice to have an invoice_id, primary key autoincrement, and the invoice_line_id too.
but when you create the invoice, you don’t have the invoice_id yet, it is filled by the database engine when you store the record with “insert record”
so the question is, how do you manage the time you create invoices_lines records, without having an invoice_id ?
do you force your user to store first the invoice to that you get an invoice_id, and then only allow the user to create invoice_lines ?
do you manage everything in memory and store the whole thing after the user clic save ?
what if you have multiple linked records in different tables ?
[quote=306055:@Jean-Yves Pochez]
so the question is, how do you manage the time you create invoices_lines records, without having an invoice_id ?
do you force your user to store first the invoice to that you get an invoice_id, and then only allow the user to create invoice_lines ?
do you manage everything in memory and store the whole thing after the user clic save ?
what if you have multiple linked records in different tables ?.[/quote]
IMHO, if invoice_line table can be filled without invoice_id than those tables are not related at all.
There are different ways of approaching it, but I have found the best way for me is:
All records have a object Id ( OID ) which uniquely identifies the row from a technical point of view and may contain gaps.
In addition to the OID the invoice table will have a candidate key called invoice_no which identifies an invoice from the business point of view. And never has gaps
The line item will have it’s own OID, an FK to it’s owning invoice, lets call it invoice_id and a line number so you can reproduced the invoice as it was created.
In the object model in your application it should be possible to create an instance of the invoice class with the OID set to say 0 so it is clear that it has not been saved yet. You should the be able to add lines to the invoices lines collection in the same manner.
When the user clicks save, you would submit the new invoice to a save method on your data access class. This method would:
Sart a transaction
Insert the invoice and update the invoice with the new OID
Loop over the line items: adding the reference to the new invoice OID, inserting the line item and updating it with it’s own new OID
If all goes well commit the transaction
Return the invoice object now containing it new OIDs
You start by creating a transaction. This is important for later one.
Then you insert your Invoice record. Get the LastInsertID back for the next step.
then you insert your Invoice line item records using the id from the last step.
If at any point in this process you have a database error (you have to check after each insert) you rollback the entire transaction.
If no errors then commit the transaction. This finalizes all of the inserts. Until then they’re all pending.
This is a very typical parent-child database relationship. And in my many years of doing it I’ve never seen it done differently. Well, I’ve seen it done, wrong, a number of different ways but this is why learning to love transactions is so important.
For parent-child records like this we tend to not save anything until the user clicks save. There are exceptions to this rule but always dictated by the client. Since we use ActiveRecord it’s really easy to have an AR object for Invoice and it would typically have an array of LineItems. The beauty of AR is that by using the AfterSave event anything you do (like save the invoice line item objects in the array) they’re all part of the same transaction and you don’t have to do anything extra.
The more linked records you have the more the transaction is important. Database transactions are your friend.
ok about the transactions.
but what if I have many windows with records in them.
one for the invoice, one for the customer, one for the products for example.
I start a transaction for the invoice and its lines
then if I want to add a new customer, or a new product, I have another window to do it.
but they are not related to the invoice I’m creating.
if I cancel the invoice, I don’t want to cancel the customer or the product.
is there a way to have multiple transactions inside one db connexion ?
I tend to use UUID’s as primary keys preferably returned from the db engine or otherwise generated locally. I do use a transaction when the invoice is actually committed to allocate the number, but that is a very quick two step update command. The invoice number is just an indexed column in the header table (sometimes in another table where multiple invoices are generated from a single order).
To me using an integer as the primary key on a table is a disaster waiting to happen, which is why transactions are so important (in that environment) - but what happens when you have a database replicated over multiple servers geographically separated? In that instance of course there would be a local sequence which may overlap other local sequences.
To get a UUID from MYSQL you would use SELECT UUID() AS newid;, in MSSQL you would use SELECT NEWID() AS newid;, SQLITE doesn’t have this function (I think) but I use @Kem Tekinay’s answer in this conversation.
[quote=306106:@Jean-Yves Pochez]ok about the transactions.
but what if I have many windows with records in them.
one for the invoice, one for the customer, one for the products for example.
I start a transaction for the invoice and its lines
then if I want to add a new customer, or a new product, I have another window to do it.
but they are not related to the invoice I’m creating.
if I cancel the invoice, I don’t want to cancel the customer or the product.
is there a way to have multiple transactions inside one db connexion ?[/quote]
You don’t start the transaction until you actually save. So it doesn’t matter if you have 1 window open with an invoice or 10,000. The transaction is simply telling the database to treat this data as temporary until I say to commit or rollback.
Likewise, you can create all the new customers and items, and whatnot, that goes into your invoice because there is no other transaction going on. IMO, the trickier part is getting your UI to update to reflect changes in the lists.
That’s a good point, but irrelevant since Jean-Yves didn’t say his database was going to be replicated over multiple servers geographically separated. If you don’t have this particular need, then UUID’s are overkill and not as readable (IMO) since they’re not sequential. Don’t get me wrong, they have their place and we’ve used them in quite a few projects but if the concept of transactions is hard to understand I’d prefer to not muddy the waters.
All records have a object Id ( OID ) which uniquely identifies the row from a technical point of view and may contain gaps.
In addition to the OID the invoice table will have a candidate key called invoice_no which identifies an invoice from the business point of view. And never has gaps
While UUIDs are fine from a technical point of view that are not very workable in everyday life, which is why I guess they are loosing their popularity. Trying to figure out what went wrong in a replication is made a little easier if you are facing a see of ints rather than a sea o uuids.