SQL Server database table primary key design

1. Preamble

At present, with the rapid increase in information, for the storage and management of data, enterprises are gradually getting rid of the previous reliance on file systems (text files or Excel) or some desktop small database systems (such as access, Foxbase or dBASE) state, Instead, you manage your enterprise's information through a number of large databases. These large database systems include Oracle, MS SQL Server, or IBM DB2. Although the database system is developing to object-oriented database system, the traditional relational database system is still occupying the main position.

From the late 90, the author has developed a customized management software for clients based on the relational database system. The main is to PowerBuilder as the foreground development tool, developed some columns of C/s structure of software. Entering into this century, especially in the last two years, the author has developed some B/s structure using Visual Studio 2003 as a tool. However, the background database system is still a relational database system, regardless of which development tool is used or what structure software is developed. According to the customer's application environment, mainly in the MS SQL Server database development, of course, there are Oracle database based software development.

Because of the increase of the amount of data, the use of large relational database system as the data storage management of the enterprise requires that the developers based on database development must follow the corresponding specification in database design. On the database logic design, the most important is the database table design, have a corresponding theoretical support, such as to meet the corresponding paradigm requirements. Generally speaking, database table design satisfies the second or third normal form.

In the development process, as far as possible to follow the corresponding rules, but because of previous experience or in the detailed design of the work is not sufficient, resulting in some table structure is not very reasonable. It is these unreasonable, there are some problems, and has been reflected in some systems.

The logical design of database is a very broad problem. This paper mainly aims at the present situation of the author's development and Application, and discusses the problems and corresponding solutions to the design of table's primary key when designing the table on MS SQL Server.

2. Primary key design status and problems

2.1 Status Quo

As for the primary key design of database tables, it is generally based on business logic to form the primary key according to the business requirements.

For example, when sales to record sales, generally need two tables, one is the summary description of the sales list, records such as sales number, the total amount of a class of cases, another table records the quantity and amount of each commodity. For the first table (the primary table), we usually take the document number as the primary key; for the list of merchandise sales (from the table), we need to put the main table's document number into the BOM of the commodity, so that it can be correlated to form a master-slave relationship. The document number, together with the code of the commodity, forms the joint primary key of the schedule. This is just the general situation, we slightly extend this question: if in the details, each of our products may be sold in different prices. Some are sold at discounted prices, and some are sold at normal prices. To record these cases, we need a third table. The primary key of this third table requires the document number of the first table and the commodity number of the second table together with the information that it needs to form a joint primary key, or otherwise, in the first primary table itself, the joint primary key is formed in a joint way, You also need to combine multiple fields from the table to form your primary key together.

The author in the previous project, also basically take such table design ideas to design the system's table structure and primary key.

2.2 Problems in existence

In the above section, we describe the current state of the table's primary key design. It is not difficult for us to see the existence of such a problem:

Data redundancy storage: With the extension of this master-slave relationship, data that needs to be stored repeatedly in the database will become larger and bigger. Or when the primary table itself is a federated primary key, you must store all the fields from the table again.

Increased SQL complexity: when there are joint primary keys for multiple fields, we need to associate multiple fields of the primary table with multiple fields of the child table to get all the detail records that meet certain criteria.

Increased program complexity: Multiple parameters may need to be passed.

Reduced efficiency: The database system needs to judge more conditions, and the SQL statement length increases. Also, federated primary keys automatically generate federated indexes

Web Paging Difficulty: because it is a federated primary key (for most child tables), it is difficult to process on a Web page when it is being paginated.