1

As far as I know, the database schema of the vast majority of Accounting Systems follows this architecture: enter image description here

Considering all the FKs, I know that the product table is going to grow in time and, if I don't want to corrupt anything, it will not let me to delete any discontinued product. Same goes for most of the other tables. I understand the reason behind using all those FKs, but following this scenario, I am mortgaging myself using those FKs today for a great pain in the future. If we use this DB in a high volume transaction company for some years, I'm almost certain that we will encounter performance and maintenance issues.

I'm asking from an amateur point of view, does anybody know if there exists another DB schema for Accounting Systems that allow to clean those tables?

6
  • 3
    Foreign keys don't prevent you from "cleaning those tables" (whatever "those" are), only you obviously need to clean their child tables as well. You do that according to the accounting, taxation, and other compliance rules in your jurisdiction.
    – mustaccio
    Commented Jan 4, 2019 at 18:24
  • @mustaccio by 'those' I mean the Masters tables that are entangled. Whenever you do an invoice, you are entangling products, accounts and periods. Those entanglements occur because they are fk. If you delete a product, an invoice line is going to be null, losing that info
    – thorstorm
    Commented Jan 4, 2019 at 19:01
  • 2
    Right, so what's the question? You cannot delete a product without deleting the invoice for the product, otherwise your invoice becomes invalid from the business point of view -- it does not reflect the truth at the invoice time anymore. Therefore, you cannot delete anything from your database that is needed to re-create the truth unless laws and regulations permit you to. Then you delete complete business objects.
    – mustaccio
    Commented Jan 4, 2019 at 19:09
  • @mustaccio my question is this: is there another accounting system DB schema that allows it without losing anything?
    – thorstorm
    Commented Jan 4, 2019 at 19:11
  • 2
    It's logically impossible to delete data without losing those data. You can mark products as "discontinued" if that helps you.
    – mustaccio
    Commented Jan 4, 2019 at 19:13

1 Answer 1

0

One practice I've seen in financial systems is to snapshot all relevant data at the time of the transaction and store it statically. This would entail relevant product, partner, transactional, audit etc. values being serialized (JSON, XML or proprietary binary format) into a single column. "Relevant" includes de-referencing foreign keys and eliminating system-internal surrogate keys.

Once serialized the future life of the transaction need not rely on the original normalized values being available. They can be amended or removed to satisfy the on-going needs of the OLTP system they are there to support.

Yes, this does duplicate the data and increase the size of the database. These systems did not leave the completed transactions in situ for long, however. Batch jobs promptly removed those rows from the OLTP database into fulfillment, archive and warehouse databases.

This had several benefits. The OLTP schema was separated from that of the other databases. Audit trail was guaranteed as the serialized values were never re-written. Patches to the reference tables could never possibly affect in-process activity. And, as in your case, no-longer needed values need not be retained simply to support history.

1
  • I have this mental model where I picture the entire entangled by fks database as a spider. Then, I picture the records as its knitted web. If you kill the spider, its web remains. I was hoping to find some DB schema to do just that. Thank you for the insights Michael. You gave me a good starting point by providing me key words to dig deeper in this matter.
    – thorstorm
    Commented Jan 6, 2019 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.