To my way of thinking, there are two types of tables. Entity Tables and Transaction Tables.
It is important to understand the difference.
Entity Tables
Describe an entity and its attributes. Customers are entities, Products are entities.
Entity table records are mutable. That is to say, they can be edited. You can edit an entity’s attributes and you can even change the name of the entity and, because entity records join to transaction tables using a hidden ID (the Primary Key) any transactions will now display the new entity name.
Because entity tables are mutable, we often need to see what changes have been made and by whom. This is why we 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 just after the add or edit 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 add or edit. So this info is saved along with the edits.
I rarely allow deletions and, even in those rare occassions, only if the entity has not been used by any transactions. My preferred method is to use two columns in each entity table, just like the add and edit columns, to capture the user name and date/time of the deletion. Then filter lists to exclude entities that have a Delete Date. This method ensures we don’t lose any related transaction records. If we were to lose transaction records then we may find, for example, that the total of our annual sales has changed and/or our accounting no longer balances.
Transaction Tables
Describe an action, for example an Order or an Invoice. As a general rule, once a record is committed then it should be immutable, that is to say it cannot 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 an Invoice, the items on the invoice are recorded in the Invoice Line table
