MySQL database design learning-five requirements for database design standardization

1. Avoid empty columns in the table;

2. duplicate values or columns should not exist in the table;

III:
The record in the table should have a unique identifier.

When designing a database table, the database administrator should develop a good habit of using an idnumber.
It uniquely identifies a row record, rather than distinguishing records by names, numbers, and other fields. Each table should have an ID column, and neither of the two records can share the same ID value. In addition, this ID value is the most
You must have a database for automatic management instead of assigning this task to the foreground application. Otherwise, the ID values may be inconsistent.
In addition, it is best to design databases
Enough to add the row number. For example, in sales order management, the ID number cannot be maintained by the user. However, you can maintain the row number. For example, in the sales order line, you can adjust the size of the line number
Sort. Generally, the ID column is progressive in units of 1. However, the row number must be progressive in the unit of 10. In this case, the row numbers are expanded in order of 10, 20, and 30. If you need
Adjust the record with the row number of 30 to the first line. In this case, you can change the row number if you cannot change the ID column. If you can change the row number to 1, you can sort it by the row number.
. In this case, the record whose original row number is 30 is now changed to 1 and can be displayed in the first row. This is an effective supplement to the ID column in the actual application design. This content does not exist in textbooks.
Yes. This technique can be mastered only in actual application design.

4. database objects must have a uniform prefix name

A complicated application system usually has thousands of database tables. It may be difficult for the database administrator to see the object name and understand the role of the database object. In addition, when the database object is referenced, the database administrator also has a headache for failing to quickly find the desired database object.
　
To this end, I have established that it is best to spend some time developing a database object prefix naming convention before developing the database. For example, when I design a database, I like to negotiate with the front-end application to confirm
Reasonable naming rules. What I usually use most is to define the background database object prefix name based on the module of the foreground application. For example, tables related to the material management module can be prefixed with m, while tables related to order management can be prefixed with M.
Use C as the prefix. The specific prefix can be defined based on your interests. However, it should be noted that this naming convention should reach a consensus between the database administrator and the front-end application developers, and
The object name is defined strictly in accordance with this naming convention.
Second, tables, views, and functions should also have a uniform prefix. For example, a view can use V as the prefix, while a function can use F as the prefix. In this way, the database administrator can find the desired object in the shortest time in both daily management and object reference.

5. Try to store only data of a single object type

The object type is not the same as the data type. Entity types described here
It refers to the object to be described. Let me give an example to illustrate the content. For example, there is now a library system with two entity objects: basic information of books and author information. If the user
You can also store the information of these two object in the same table. For example, you can design a table as a book name or author. However, such design will cause a lot of trouble for subsequent maintenance.
　
For example, when a book is published in the future, the author information needs to be added for each published book. This will undoubtedly increase the additional storage space and the length of the record. In addition, if the author's situation changes, such as the address change
Later, you need to change the records of each book. If all the books of this author are deleted from the database, the information of this author will disappear. Obviously, this does not meet the needs of standardized database design.
.
In this case, I suggest you break down the table above into three independent tables, including the basic information table of books, the basic information table of authors, and the corresponding table of books and authors. After this design, all the problems mentioned above will be solved.