A bad database design will inevitably lead to many problems, from adding or deleting fields to the failure of the system. Let me first talk about the unreasonable performance of database design:
1. does not meet the requirements.
For this reason, the amount of changes is often the largest. If you enter the coding stage, it is likely to make you collapse directly.
2. Low performance
There are too many associations between tables with large data; SQL query statement is very complicated because the query has no reasonable field design; There is no effective method to deal with tables with large data; Abuse of views, etc.
3. Loss of data integrity
The design of related fields between tables with primary key and foreign key relationship is unreasonable, which makes the program prone to errors or imperfections after updating and deleting operations; Old data that has been deleted or lost.
4. The expansibility is too poor
The design of the table is too tightly and simply bound to the business, which leads to poor expansibility and modifiability of the table and can not meet the new requirements.
5. Unnecessary data redundancy is too large.
Too much useless junk data storage not only occupies resources, but also affects query efficiency.
6. Not conducive to calculation or statistics
The necessary related or statistical fields are missing or the fields used to calculate statistics are scattered in multiple tables, which makes the steps of calculating statistics complicated or even impossible.
7. There is no detailed data record information.
The lack of necessary fields makes it impossible to track data changes, user operations and analyze data.
8. The coupling between tables is too large.
The relationship between multiple tables is too close, which leads to the change of one table and affects other tables.
9. Inappropriate site design
The field length is too short or the field type is too clear, resulting in too little room for development and expansion.
Most programmers are not very clear about the starting point of software development, and always think that it is very important to realize functions. After a brief understanding of the basic requirements, we rush into the coding stage, and the database design is less and less considered, and more and more simple, and most of the design only stays on the surface, which is often fatal and will leave many hidden dangers for the system. Either problems were found in the process of coding, or problems appeared shortly after the system went online, which may increase a lot of workload for later maintenance. If you want to modify the database design or optimization at that time, it is equivalent to overthrowing.
Database is the foundation of the whole software application and the starting point of software design, which plays a decisive role in qualitative change. Therefore, we must attach great importance to database design and cultivate the habit of designing a good database, which is the basic quality condition that an excellent software designer must have!
So to what extent are we right? Let's talk about the principles of database design.
1. The database design should occupy at least 40% of the whole project development time.
Database is an intuitive response and expression of requirements, so the design must effectively meet the needs of users, communicate with users many times to refine the requirements, and integrate the requirements into the requirements and into every change of database design. If the demand is not clear, it is necessary to analyze the uncertain factors, and when designing the form, it is necessary to reserve flexible fields in advance, which is the so-called "being prepared."
2. Database design is not limited to the surface of page presentation.
The fields needed for page content are only a part of database design, and there are also fields needed for system operation, module interaction, transit data, and relationship between tables, so database design is definitely not a simple basic data storage, but a logical data storage.
3. After the database design is completed, 80% of the design and development of the project is completed in your mind.
The design of each field has its necessary significance. When you design each field, you should have figured out how to use these fields in the program and how to reflect the relationship between multiple tables in the program. In other words, after you finish the database design, all the ideas and ways to realize the program have been considered in your mind. If we can't reach this level, then when we enter the coding stage, the database can't support the technology or the way to realize it. It will be very troublesome to change the database at this time, which will cause a series of unpredictable problems.
4. Efficiency and optimization should be considered when designing the database.
At first, we should analyze which tables will store more data. The table design of big data is often coarse-grained, and some necessary fields will be redundant, which achieves the purpose of storing massive data with the least number of tables and the weakest table relationship. Moreover, when designing a table, a clustered index is usually established on the primary key, and it is necessary to index the table with a large amount of data to provide query performance. When there are requirements for calculation, data interaction, statistics, etc., we should also consider whether it is necessary to adopt stored procedures.
5. Add necessary (redundant) fields
Each table must include fields such as creation time, modification time, remarks, operating user IP and other requirements (such as statistics). Not only the data used in the system will be stored in the database, but some redundant fields are added to facilitate future maintenance, analysis and expansion, which is very important, such as hacking, tampering with data and so on.
6. Design reasonable table association
If the relationship between multiple tables is complicated, it is suggested to use the third mapping table to maintain the relationship between the two tables, so as to reduce the direct coupling between the tables. If multiple tables involve a large amount of data, the table structure should be as simple as possible to avoid association.
7. Binding associations such as primary keys and foreign keys are not added when designing tables, but are added after the system coding stage is completed.
The purpose of this is to help the team develop in parallel and reduce the problems encountered in coding. The relationship between tables is controlled by the program. After coding, add associations and tests. However, some companies simply don't add table association.
8. Select the appropriate primary key generation strategy.
The primary key generation strategy can be roughly divided into: int self-growth (identity, sequence), manual growth (establishing a single table for maintenance), manual maintenance (such as userId) and string type (uuid, guid). Int type has the advantages of simple use and high efficiency, but it is easy to have problems when merging data between multiple tables. Manual growth type and string type can solve the problem of data merging between multiple tables, but they also have disadvantages: the former has the disadvantages of adding a database access to obtain the primary key and maintaining an additional primary key table, which increases the complexity; The latter takes up a lot of storage space, and the efficiency of table associated query and index is not high, which is just the opposite of int type.
To sum up, we can see the important role of database design in the whole software development, especially the first point of my design principle. Database and demand complement each other. I often compare software development to automobile manufacturing. Automobile manufacturing will go through the steps of drawing design, model making, prototype manufacturing, small batch trial production and final mass production. The whole process is interlocking, and the latter process is based on the premise that the former process is correct. If defects are found in the drawing design stage, we can redesign the drawing. If this error is found in the prototype manufacturing stage, then we will start again from the drawing design to the prototype manufacturing stage. The more design problems are discovered later, the greater the cost and the more difficult it is to modify.
In fact, the difficulty of database design is much more difficult than the simple technical implementation. It fully embodies a person's overall design ability and control ability, so everyone must focus on cultivating this ability in future projects. Here I share my experience with you, hoping to help you.