Five requirements of SQL database design Learning---database design normalization

http://blog.csdn.net/taijianyu/article/details/5945490

One: Nullable columns should be avoided in the table;

Two: The table should not have duplicate values or columns;

Three: The records in the table should have a unique identifier
In the database table design, the database administrator should develop a good habit, with an ID number to uniquely identify the row records, and not by the name, number and other fields to distinguish the record. Each table should have an ID column, and no two records can share the same ID value. In addition, this ID value should preferably have a database for automatic management, rather than the task to the foreground application. Otherwise, it is easy to create a case where the ID value is not uniform.
Also, it is best to add line numbers when designing a database. As in sales order management, the ID number is not maintained by the user. However, line numbers can be maintained by the user. In the line of the sales order, the user can sort the order lines by adjusting the size of the line numbers. In general, the ID column is in the 1-per-unit progression. However, the line number will be 10 units progressive. In this case, the line numbers are extended in 10, 20, and 30 in the normal way. If the user needs to turn the line number 30 record to the first line display. At this point, the user can change the row number if the ID column cannot be changed. If you can change the line number to 1, you can sort by line number when sorting. In this case, a record with a line number of 30 now becomes 1 and can be displayed in the first row. This is an effective complement to the ID column in the actual application design. This content is not in the textbook. This technique needs to be mastered in practical application programming.

Four: Database objects must have a uniform prefix name
A more complex application system, the corresponding database table is often in thousands. It would be difficult for a database administrator to see the object name to understand the role of the database object. And when database objects are referenced, the database administrator also has a headache for not being able to quickly find the database objects they need.
To this end, the author established, before the development of the database, it is best to spend a certain amount of time to develop a database object prefix naming specification. As the author in the database design, like the front desk application negotiation, determine a reasonable naming specification. The most common use of the author is to define the background database object prefix name according to the foreground application's module. If the table related to the Material Management module can be prefixed with M, and the order management related, then C can be used as a prefix. The specific prefix can be defined as a user's hobby. However, it should be noted that this naming convention should be a consensus between the database administrator and the foreground application developer, and that the name of the object should be defined in strict accordance with this naming convention.
Second, tables, views, functions and so on are best also have a uniform prefix. The view can be prefixed with V, and the function can be prefixed with F. This allows the database administrator to find the objects they need in the shortest amount of time, both in daily administration and object referencing.

Five: Try to store only data of a single entity type
The entity type here is not the same as the data type, so be aware of the distinction. The entity type described here refers to the object that you need to describe itself. I would like to give an example, it is estimated that everyone can understand the content. If there is now a library system, there are basic information, author information two entity objects. It is also possible for the user to place the two entity object information in the same table. If you can design the table as a book name, book author and so on. However, such a design will bring a lot of trouble to the subsequent maintenance.
When a subsequent book is published, the author's information needs to be added to each published book, which will undoubtedly add additional storage space and increase the length of the record. And if the author's situation changes, if the address changes, you also need to change the record of each book. If the author's book is removed from the database, the author's information is gone. Obviously, this does not conform to the requirements of database design normalization.
In this case, the author suggested that the above table can be decomposed into three separate tables, respectively, the basic information table, the author's basic information table, book and author correspondence table and so on. After this design, all of the above problems have been solved by the edge.

SQL database Design Learning---Five requirements for database design normalization