Database architecture: unification (for example, ERP)

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

An application database schema with a complex domain model (which includes ERP systems) usually consists of
several hundred tables.
Therefore, at the initial stage of designing the database, in order to avoid multiple duplications and swelling of the scheme, it is important to
decide on several basic tables for storing the general properties of the basic entities of the application
and already design all other tables as auxiliary or additions to the main tables.

An example of designing documents:

• The general properties of all documents are placed in a separate table.
• A separate table is created for each type of document with its own, specific fields, which is
  joined to a common table. To reduce the number of FK index fields to a common table, do PK. When displaying a list of documents, we display only the general fields from the base table, and when displaying a specific document we already use join, therefore, performance does not suffer.
  
• Functionally the same type of document fields (especially if they differ for different types of documents) are placed in separate general tables. it
  
  • references to counterparties (for example, a court, a complainant, an interested party, a third party for a document “Response to a complaint”).
    
  • links to people playing certain roles in the document (author, recipient, executor,
    approver, responsible, clerk, manager).
    
  • references to other documents (basis, travel document, reference to the contract, account, protocol of disagreements, contract).
    
  
  We supplement these tables with a field - type of link (for PostgreSQL - enum is suitable). At the same time, a request to a
  specific document is surrounded by 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 be done not specifically for each of hundreds of document types, but once.
  Plus, we have the opportunity of multiple links (multiple recipients, contracts, third parties) for one document.
  
• Further, each ERP subsystem (budgeting, logistics, EDS, warehouses, CRM, ..) has its own documents with the same general properties. It is necessary to be able to display a list of all documents for one subsystem and a list of all the constant attributes (states, types, folders) of documents for one subsystem.
  Create an enum module that characterizes 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 act 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 is to extend this approach to documents to others.

basic entities of the application:

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

---

References:

• ERP River