To my way of thinking, there are two types of tables. Entity Tables and Transaction Tables.
It is important to understand the difference.
Describes an entity and its attributes. Customers are entities, Products are entities. You can edit an entity’s attributes. I always like to use a Log table that takes a copy of the record after the edit. Why after? Well you could take a copy before but not if it’s a new record. So taking the snapshot after ensures that the log table has at least one copy of each record. But before the record is saved, I update two fields – Edit_Date and Edit_By. This captures the date and time and user name of the person making the edit. So this info is saved along with the edits. I rarely allow deletions but in cases where I do then I capture the Edit info, along with an extra column, Edit_Type, in this case ‘Delete’ and save the record before the delete.
Describes an action, for example an Order or an Invoice. As a general rule, once a record is committed then it should not be edited. This is very true in the case of an Invoice, but maybe not quite so true in the case of an Order. Although the latter should, if changes are allowed, have some kind of version identifier.
Transactions often affect other transactions or entities. For example, if you create an invoice to sell an item, this transaction may also change the stock quantity for that item. Hence the saving of this transaction should ensure that if one action occurs then the corresponding action occurs.
Take for example a Bank Transaction. This transaction affects two accounts. So when money is deducted from one account it is credited to the other. This kind of Transaction requires a Commit or Rollback so that if one action fails then the other is rolled back. In code we create a Workspace, initiate a Transaction, perform both actions then Commit. If an error occurs with either then we Rollback.
Transactions often require Line Items so, in the case of Invoice, the items on the invoice are recorded in the Invoice Line table