Database table structure design methods and principles

Database table structure design methods and principles
Author: chancey
 
In the current enterprise information system, the database is still the best way to store data. Although many books have guided us in database design, however, I have been thinking about and summing up the best way to design the table structure of the database, what principles should be followed during the design, and how to use the four paradigms to achieve smooth application. problems, the following is an outline of an article I have prepared and summarized based on my own design experience. You are welcome to discuss this article and brainstorm. I mentioned the concept of Domain Modeling, but I have not explained it in detail. I hope we can discuss this proposition in depth in the future.

1) Databases should not be designed for the entire system, but should be designed for the Services processed by each component according to the components in the system architecture; the associations between database tables corresponding to different components should be minimized. If tables of different components need foreign key associations, try not to create foreign key associations, instead, it only records a primary key of the associated table, ensures the independence between the tables corresponding to the component, and provides the possibility of restructuring the system or table structure.

2) use the domain model-driven approach and top-down thinking to design databases. First, analyze the system business and define the objects according to the responsibilities. The object must comply with the encapsulation characteristics to ensure that the data items related to responsibilities are defined within the same object. These data items can fully describe this responsibility without missing responsibility descriptions. In addition, an object has only one responsibility. If an object is responsible for two or more responsibilities, it should be split.

3) map database tables based on the established domain model. In this case, refer to the second paradigm of Database Design: all non-keyword attributes in a table depend on the entire keyword. A keyword can be either an attribute or a set of multiple attributes. In this way, you must ensure that the keywords are unique. When determining the keywords, ensure that the keywords are not involved in the business and there is no update exception. In this case, the optimal solution is to use an auto-incrementing numeric attribute or a random string as the keywords of the table.

4) because the domain model-driven method described in the first point designs the database table structure, each object in the domain model has only one responsibility. Therefore, the data items in the object do not have the transfer dependency, in this way, the database table structure design satisfies the third paradigm from the very beginning: A table must satisfy the second paradigm, and there is no transfer dependency between attributes.

5) Likewise, because the singularity of the object's responsibilities and the relationship between objects reflect the relationship between business logic, objects in the domain model are divided into primary objects and slave objects, from an object is from the perspective of 1-N or N-N further master object business logic, so from the object and object relationship mapped to the table and table association does not exist to delete and insert exceptions.

6) in the database table structure after ing, further modification should be made based on the fourth paradigm to ensure that there is no multi-value dependency. At this time, the domain model should be fed back to the reverse engineering idea. If the table structure has multi-value dependencies, it proves that the objects in the domain model have at least two responsibilities and should be designed and corrected according to article 1. Fourth paradigm: If a table meets bcnf, there should be no multi-value dependency.

7) after analysis, it is confirmed that all tables meet the 2, 3, and 4 paradigms, try to use weak associations between tables to facilitate the adjustment and restructuring of table fields and table structures. In addition, I think that tables in the database are used to persist the status of an object instance at a specific time and under specific conditions. They are only a storage medium. Therefore, there is no strong association between tables to express the business (Data Consistency). This responsibility should be ensured by the logic layer of the system, this method also ensures the system's compatibility with incorrect data (dirty data. Of course, from the perspective of the entire system, we still need to do our best to ensure that the system will not produce dirty data. From another perspective, the generation of dirty data is inevitable to a certain extent, we also need to ensure the system's fault tolerance in this case. This is a compromise.

8) indexes should be created for the primary keys and Foreign keys of all tables, and composite attribute indexes (for some large data volumes and common retrieval methods) should be established to improve the retrieval efficiency. Although indexing consumes part of system resources, it compares the performance impact of searching the data in the entire table during retrieval, especially when the data volume in the table is large, and the performance impact of sorting operations without indexes, this method is still worth advocating.

9) Use as few stored procedures as possible. There are already many technologies that can replace stored procedures, such as "Object/relationship ing", to ensure data consistency in the database, regardless of version control, development, deployment, and database migration. However, it is undeniable that stored procedures have performance advantages. Therefore, when the hardware available in the system is not improved and performance is a very important quality attribute, you can use stored procedures in a balanced manner.

10) when the cost (often the usability cost) paid for processing the association constraints between tables exceeds the cost of ensuring that no modification, deletion, or change exceptions occur, in addition, when data redundancy is not a major problem, the table design may not conform to the four paradigms. The four paradigms ensure that there is no exception, but they may also lead to an overly pure design, making the table structure difficult to use. Therefore, comprehensive judgment is required during design, but first, it must conform to the four paradigms, then, refined correction is the best method that can be used when you enter the database design field.

11) the designed tables must be highly usable, mainly because of the need to associate multiple tables during queries and the need to use complex SQL techniques.

12) Design tables should minimize data redundancy to ensure data accuracy. Effective control of redundancy helps improve database performance.