Occasionally, I see junior and even sometimes not-so-junior programmers struggling with basic business software design concepts that are not exactly fashionable but are essential in the design of good business software. One of them is the concept of the integrity of commercial transactions. Let me go back to basics and talk about the importance of the integrity of commercial transactions.
Let’s say you are designing an invoicing system. As you may have guessed, invoices are commercial transactions. Let’s go deeper and think about what invoices are. Invoices prove that two parties involved in a commercial transaction have exchanged goods or services for a given sum of money. Invoices are issued by the party providing services or goods and expecting payment. Invoices are also accounting documents. The sum of all invoices for the financial period is the company’s sales. Furthermore, the sum of all a company’s unpaid invoices from customers represents its Account receivables and is a significant financial asset that needs to be tracked closely. Invoices have three sections: A header, line items, and a footer. The header has, at minimum, an invoice number, a transaction date, and basic information about the customer, such as its address. Line items contain part numbers, part descriptions, quantities sold, unit prices, and price extensions. And finally, the footer has a subtotal, taxes, and total. It can be a lot more complex than that, but you’ve got the big picture.
Commercial transactions, such as invoices, are immutable. It means that it should never change over time. For instance, the customer’s address at the time of the transaction is part of that immutable data. Therefore, storing it with the transaction ensures you retain a snapshot of the transaction as it occurred. If you print an invoice ten years after its creation, the customer’s address on the invoice should remain the same. Even if the customer file (or a CRM system) tells you that the customer has moved since the invoice was issued.
Let’s get technical and talk about database design. It takes a minimum of two database tables to represent an invoice in a relational database. One table is for storing information about the invoice header and footer, and another one is for storing line items. But let’s continue our discussion about the customer’s address. The customer address is part of the invoice header table from a database design standpoint. Details of a customer address must be columns on the header table. You can have a foreign key that points out to a customer file, but your transaction cannot rely on it to retrieve the details of the customer’s address. I’ve often heard that argument: why add complexity by adding extra columns in the database when we have the same information in the customer file? Does it not break the normalization principle of not duplicating data? The short answer is no, it does not, and it’s a terrible idea. Here’s why:
First, the customer’s address in the transaction serves a different purpose from the one stored in the customer file. The transaction record (invoice) represents historical data on a specific business event. The address is part of that event’s context and must remain unchanged for auditing, legal, and reporting purposes. The customer file holds the current, up-to-date information about the customer, such as the address where they currently reside or receive communications.
Second, denormalization is necessary for practical reasons. In this case, storing the customer’s address in the transaction (even though it’s also in the customer file) avoids potential data issues such as address changes over time that would corrupt historical transaction data and invoices needing to be consulted as they were at the time of the transaction. This denormalization is acceptable because it preserves the integrity of business records, ensuring that each transaction remains accurate regardless of future changes to the customer’s information.
In conclusion, I used the customer address as an example in this article, but any information must remain unchanged. A commercial transaction is an aggregation of information coming from different sources. The sources of data of a commercial transaction are: 1) some master data such as a customer file, a pricing system, an inventory system, etc. 2) Directly coming from a user interface such as a POS, a transactional website, etc. 3) Some calculations based on the business rules, such as Subtotal or tax amounts. Whatever the source of information, it must remain unaltered over time to preserve the integrity of the commercial transaction. In future articles, I could write more about why transaction integrity is so important: It ensures that accounting data is reliable, accurate, and consistent, which forms the foundation for correct financial statements, reporting, and compliance.