Why the Accounting DB Schemas do not allow to maintain a clean Products Table? As far as I know, the database schema of the vast majority of Accounting Systems follows this architecture:


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?
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.