Importance and principles of Database Design

Speaking of database design, I believe everyone understands what is going on. But speaking of the importance of database design, I think everyone just stays on the concept. How is it important? How is it important? Today, I will explain my understanding so far.

A poor database design will inevitably cause many problems. If it is light, the field is increased or decreased, and the system cannot run if it is heavy. Let me talk about the unreasonable Database Design:

1. inconsistent with requirements

For this reason, the change volume is usually the largest. If you enter the encoding stage, it is very likely that you will crash directly.

2. Low performance

There are too many associations between tables with a large amount of data. SQL query statements caused by queries without proper field design are complex. Tables with large data volumes are not processed effectively; abuse views.

3. Data Integrity loss

The Design of fields associated with tables with a primary/foreign key relationship is unreasonable, which leads to errors or imperfections in the program after the update and deletion operations. deleted or lost data is used.

4. poor scalability

The table design is too closely bound to the business and is single, resulting in poor scalability, modification, and new requirements of the table.

5. The amount of unnecessary data redundancy is too large

The useless junk data is stored too much, which not only occupies resources, but also affects query efficiency.

6. Negative computing or statistics

If necessary contact or statistical fields are missing or the fields used for calculation statistics are scattered in multiple tables, the calculation statistics steps are cumbersome and even unable to be calculated.

7. No detailed data record information

If necessary fields are missing, data changes cannot be tracked, user operations cannot be performed, and data analysis cannot be performed.

8. The coupling between tables is too large.

The association between multiple tables is too tight, resulting in a table change and affecting other tables.

9. Inefficient AT Field Design

The length of a field is too short or the field type is too clear, resulting in a small available and expandable space.

Most programmers do not have a clear understanding of the starting point of software development. They always think that implementing functions is important. After simply understanding the basic requirements, they will rush into the coding stage, there are few and relatively simple ideas about database design. Most designs only stay on the surface, which is often fatal and leaves many risks for the system. Either the problem is discovered only during code development, or the problem occurs not long after the system goes online and runs, and it may increase the workload for later maintenance. If you want to modify the database design or optimize it at that time, it is equivalent to overthrowing it.

Database is the foundation of the entire software application and the starting point of software design. It 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, it is essential for an excellent software designer!

So to what extent are we right? The following describes the principles of database design.

1. database design requires at least 40% of the project development time

The database is the intuitive response and performance of requirements. Therefore, the database must meet the needs of users and communicate with users multiple times to refine the requirements, the requirements and each change must be reflected in the database design. If the requirements are not clear, we need to analyze the uncertain factors. When designing a table, we need to reserve reserved fields ".

2. database design is not just on the page demo Surface

The fields required for the page content are only part of the database design, as well as the fields required for system operation, module interaction, transit data, and association between tables, therefore, database design is definitely not a simple basic data storage, but also a logical data storage.

3. After the database design is complete, the design and development of project 80% has been completed in your mind.

The design of each field is necessary. When designing each field, you should have figured out how to use these fields in the program, how is the association between multiple tables reflected in the program. In other words, after you complete the database design, all the Implementation ideas and methods of the program have been taken into consideration in your mind. If this level is not reached, the database cannot be supported only after entering the encoding stage, and it will be very troublesome to change the database, this may cause a series of unpredictable problems.

4. Efficiency and optimization should be taken into account during database design.

At the beginning, we need to analyze which tables will store a large amount of data. For tables with large data volumes, the design is usually coarse-grained, and some necessary fields are redundant, the minimal table and weakest table relationships have been used to store massive amounts of data. During table design, clustering indexes are usually set up for the primary key. For tables with large data volumes, indexing is required to provide query performance. Whether it is necessary to use stored procedures should be considered for requirements such as computing, data interaction, and statistics.

5. add necessary (redundant) fields

Such as "creation time", "modification time", "Remarks", "Operation user IP", and other fields used for other requirements (such as Statistics, each table must have one, not to say that only the data used in the system will be stored in the database. Some redundant fields are added to facilitate future maintenance, analysis, and expansion, this is very important. For example, if hackers attack and tamper with data, we can find and locate the data based on the modification time and user IP address.

6. Rational table Association Design

If the relationship between multiple tables is complex, we recommend that you use the third ing table to maintain the relationship between the two tables to reduce the direct coupling between tables. If multiple tables involve a large amount of data, the table structure should be as simple as possible, and the association should be avoided as much as possible.

7. No binding association such as the primary and Foreign keys is added during table design. After the system coding stage is complete, the binding Association is added.

This is intended to facilitate the team's parallel development and reduce the problems encountered during coding. The relationship between tables is controlled by programs. After the encoding is complete, add the association and perform the test. However, some companies simply do not add table associations.

8. select an appropriate primary key generation policy

Primary Key Generation policies can be roughly divided into: int self-growth type (identity, sequence), Manual growth type (create a separate table for maintenance), Manual maintenance type (such as userid), string type (UUID, guid ). Int type is easy to use and efficient, but data merging between multiple tables is prone to problems. Manual growth type and string type can solve the problem of data merging between multiple tables, but there are also disadvantages: the disadvantage of the former is that it adds a database access to obtain the primary key, and maintains another primary key table, increasing the complexity. The latter occupies a lot of storage space, in addition, the efficiency of table join queries is low, and the indexing efficiency is not high, which is the opposite of that of int type.

In the end, we can see that the database design plays an important role in the entire software development. In particular, the first point of the design principle I mentioned is that databases and requirements complement each other, I often compare software development to automobile manufacturing. Automobile Manufacturing will go through the steps of drawing design, model making, sample vehicle manufacturing, small batch production, and finally batch production. The entire process is interlocking, And the next process is based on the premise that the previous process is correct. If there is a leak in the drawing design stage, we can re-design the drawings. If this error is found in the model manufacturing stage, we should re-start from the drawing design stage to the model manufacturing stage, the more problems with the design are found later, the more expensive the modification is.

Database Design is actually much more difficult than simply implementing technology. It fully reflects a person's global design capability and control ability, therefore, in future projects, everyone must focus on cultivating this capability. Here I will share my experience with you, hoping to help you.