Database architecture: unification (for example, ERP)

There are concepts of working with the base, based on the ORM, CodeFirst with its own advantages and disadvantages. The database unification proposed here is based primarily on the Database First approach.

The database schema of applications with a complex domain model (which include ERP systems) usually consists of
several hundred tables.
Therefore, at the initial stage of designing the base, it is important to avoid multiple duplications and swelling of the scheme.
decide on several base tables to store the general properties of the basic entities of the application
and all other tables are already designed as auxiliary or additions to the main tables.

Document Design Example:

• General properties of all documents are placed in a separate table.
• For each type of document with its own, specific fields, a separate table is created, which
  joining the general table. To reduce the number of FK index fields to the common table, we do PK. When displaying the list of documents, we display only common fields from the base table, and when showing a specific document, we already use join, so performance does not suffer.
  
• Functionally, the same type of document fields (especially if they are different for different types of documents) are placed in separate general tables. it
  
  • references to counterparties (for example, court, complainant, interested party, third party for the document “Response to complaint”).
    
  • references to people playing certain roles in the document (author, recipient, performer,
    coordinating, responsible, clerk, leader).
    
  • references to other documents (basis, travel document, reference to the contract, invoice, protocol of disagreements, contract).
    
  
  We supplement these tables with a field — a reference type (for PostgreSQL, enum is appropriate). With this request to
  to a certain document, it acquires joins, but the gain in unifying data handling is huge:
  checking when deleting a document, saving a document, copying a document for all fields of common tables will not be done specifically for each of the hundreds of document types, but once.
  Plus, we have the possibility of multiple links (multiple recipients, contracts, third parties) for a single document.
  
• Further, each ERP subsystem (budgeting, logistics, EDS, warehouses, CRM, ..) has its own documents with the same general properties. You must be able to display a list of all documents for one subsystem and a list of all permanent attributes (states, types, folders) of documents for one subsystem.
  Create an enum module characterizing the subsystem
  

  CREATE TYPE ref.module AS ENUM ( 'bdg', 'crm', 'ecm', 'wms', 'scm', ... ); 

  
  and add a field of type module to these tables. As a result, we have a common PK for all application documents, a common code for processing CRUD, the ability to link from any document to a document of other subsystems, a common system of rights to actions with a document, etc.
  

As a result, the number of tables and the size of the code working with the database will be reduced by an order of magnitude. All that remains for us to do is to extend this approach to documents to others.
')

basic application entities:

constants (types and statuses of documents, attributes of counterparties, types of document links, access modes, types of sending) and editable directories (tags, roles, ..).
We create two const and ref tables and two enum characterizing the record types of these tables. And two more common application tables doc.folder and ref.folder for maintaining the tree structure of documents and editable directories.
One of the drawbacks of this unification is the non-strict limitation of fields at the base level (ie, the field “link to the document tag” will have FK to the edited directory).
It is assumed that the record type of the edited “Tag” directory is controlled at the application level.
Thank you for your attention, comments are welcome.

---

References:

• ERP River