Database architecture: standardization (for example ERP)
There is a concept of work with the database based on ORM, CodeFirst with their advantages and disadvantages. We offer here the unification of the database is based primarily on the approach Database First.
The schema of the database applications with a complex domain model (which include the ERP system) usually consists of
several hundreds of tables.
Therefore, at the initial stage of designing the database to avoid multiple duplications and swelling of the important schema
to decide on several base tables for storing General properties of the basic entities of the application
all the other tables already projecting as an auxiliary or Supplement the master tables.
design Example document:
the
- For each document type with its own specific fields creates a separate table
pragoinvest to the General table. To reduce the number of field-indices to the overall FK table do PK. When displaying the list of documents we show only the common fields from the underlying table, and when displaying a specific document already use join, so performance does not suffer.
the - is Functionally the same type of document fields (especially if they vary for different types of documents) put in a separate shared table. This
the-
the
- links to counterparties (e.g. the court, the Complainant, interested person or third person to document the "Withdrawal of appeal").
the - links to people playing in certain roles document (author, recipient, contractor,
matching, responsible, clerk, Manager).
the - links to other documents (basis of a travel document, a reference to the contract, invoice and a discrepancy report, contract).
Complement these table field — type links (for PostgreSQL is a base enum). While a request to
a specific document acquires joyname, but you get the benefits of unifying the management of data is huge:
check when you delete a document, save a document, copy a document for all fields of the common tables will be placed specifically for each of the hundreds of document types, and once.
Plus we have the possibility of multiple links (multiple recipients, contracts, third parties) for a single document.
the - links to counterparties (e.g. the court, the Complainant, interested person or third person to document the "Withdrawal of appeal").
- on - each subsystem of ERP (budgeting, logistics, document management warehouses, CRM, ..) has its own documents with the same General properties. You must be able to list all documents for one subsystem and a list of all the permanent attributes (States, types, folders) documents for one subsystem.
Create the enum module, which characterizes the subsystem
theCREATE TYPE ref.module AS ENUM ( 'bdg', 'crm', 'ecm', 'wms', 'scm', ... );
and add a field of type module in these tables. In the result we have PK for all application documents, common code to handle CRUD, the ability to link from any document on the document other subsystems, the overall system of rights to actions and documents etc.
Common properties for all documents placed in a separate table. the
As a result, the number of tables and code working with the database will be reduced considerably. All we can do is to spread this approach to other documents.
the basic essence of the app:
constants (types and statuses of documents, attributes of partners, types of links, documents, access modes, types send), and editable reference (tags, roles, ..).
Create two tables const and ref and two enum describing the types of records in these tables. And two total tables in the Annex of doc.folder and ref.folder to maintain the tree structure of documents and editable dictionaries.
One of the disadvantages of such harmonization is a soft limit fields at the database level (ie, the "link to the tag of the document" will have FK in the edited guide).
Thank you for your attention, comments are welcome.
Links:
the
-
the
- ERP River
