database table Design Principles

(1) The database design should not be designed for the whole system, but should be based on the components of the system architecture, and the database design of the component unit for the business handled by each component; The association between the database tables corresponding to the different components should be minimized, If a table between different components requires a foreign key association, try not to create a foreign key association, but simply record a primary key of the associated table, ensuring that the components correspond to the independence of the table, providing the possibility of refactoring the system or table structure.

(2) Using domain model-driven approach and top-down thinking for database design, first analyze system business, define object according to responsibility. Objects conform to the encapsulated nature, ensuring that the data items associated with the duties are defined within an object that fully describes the responsibility and does not present a lack of responsibility description. And an object has and has only one responsibility, if an object is responsible for two or more than two responsibilities, it should be split.

(3) According to the established domain model to map the database table, the second paradigm of database design should be referenced: all non-keyword attributes in a table depend on the entire keyword. A keyword can be a property or a collection of multiple properties, and in either case, ensure that the keyword is guaranteed to be unique. When determining a keyword, you should ensure that the keyword is not involved in the business and that there are no update exceptions, and the optimal solution is to use a self-increment numeric attribute or a random string as the table's keyword.

(4) because the domain model-driven approach of the 1th describes the design of a database table structure, each object in the domain model has only one responsibility, so the data item in the object has no transitive dependency, so the database table structure design of this idea satisfies the third normal form from the beginning: a table should satisfy the second normal form, And there is no transitive dependency between attributes.

(5) Similarly, because of the uniqueness of the object's responsibilities and the relationship between the objects reflects the relationship between the business logic, the objects in the domain model exist the main object and the division from the object, from the point of view of 1-n or n-n to further the business logic of the main object, Therefore, there is no delete and insert exception for table and table association relationships mapped from object and object relationships.

(6) In the database table structure after mapping, it should be further modified according to the fourth paradigm to ensure that there is no multi-valued dependency. At this time, we should feedback to the domain model according to the idea of reverse engineering. If there is a multivalued dependency in the table structure, it proves that the objects in the domain model have at least two or more responsibilities, and that the design corrections should be made according to the first article. The Forth paradigm: if a table satisfies bcnf, there should be no multivalued dependency.

(7) After analyzing and confirming that all the tables satisfy the two or three or four paradigms, the association between the table and the table should use weak correlation to adjust and reconstruct the table fields and tables structure as far as possible. And, I think the table in the database is used to persist the state of an object instance at a specific time and under certain conditions, but only a storage medium, so there is no strong association between tables and tables to express the business (consistency between data), this responsibility should be ensured by the logical layer of the system, This approach also ensures that the system is compatible with incorrect data (dirty data). Of course, from the point of view of the whole system, we still have to do our best to ensure that the system does not produce dirty data, from another point of view, the generation of dirty data is also inevitable to some extent, we also want to ensure that the system fault tolerance of this situation. This is a compromise solution.

(8) Index the primary key and foreign key of all the tables, and set up the index of the combined attribute (for some large data volume and common retrieval method) to improve the retrieval efficiency. While indexing consumes some of the system resources, it is still worth advocating to compare the performance impact of searching for data in the entire table, especially when the data in the table is large, and the performance impact of sorting operations without indexes.

(9) To minimize the use of stored procedures, there are many technologies can replace the functions of stored procedures such as "object/relational mapping", and so on, the guarantee of data consistency in the database, regardless of version control, development and deployment, as well as the migration of the database will have a great impact. However, there is no denying that the stored procedure has a performance advantage, so when the hardware that the system can use is not improved and the performance is a very important quality attribute, the stored procedure can be considered balanced.

(10) When the cost of dealing with association constraints between tables (often the cost of usability) exceeds the cost of ensuring that no modifications, deletions, changes to exceptions are made, and that data redundancy is not the primary issue, the table design can be inconsistent with four paradigms. The four paradigms ensure that no anomalies occur, but may also lead to overly pure design, making the table structure difficult to use, so it is necessary to make a comprehensive judgment at design time, but first ensure that it conforms to four paradigms, and then refinement correction is the best way to enter the field of database design.

(11) The design of the table to have a good usability, mainly reflected in the query if you need to correlate multiple tables and also need to use complex SQL skills.

(12) The design of the table to minimize data redundancy, to ensure the accuracy of data, effective control of redundancy can improve the performance of the database.

From:http://www.blogjava.net/jack2007/archive/2008/05/05/198412.html

database table Design Principles