How to Design the primary key of a time-based table on SQLServer

The logical design of databases is a wide range of issues. This article focuses on the primary key design issues and corresponding solutions for tables on MSSQLServer during development and application. Primary Key design status quo and problems the primary key design for database tables is generally based on business needs and business logic.

The logical design of databases is a wide range of issues. This article focuses on the primary key design issues and corresponding solutions when designing tables on ms SQL Server during application development. Primary Key design status quo and problems the primary key design for database tables is generally based on business needs and business logic.

The logical design of databases is a wide range of issues. This article focuses on the primary key design issues and corresponding solutions when designing tables on ms SQL Server during application development.

Primary Key design status quo and problems

The primary key design of database tables is generally based on business needs and business logic to form a primary key.

For example, to record the sales situation during sales, two tables are generally required. One is the brief description of the sales order, such as the sales order number and total amount, the other table records the quantity and amount of each item. For the first table (master table), we usually use the document as the primary key; for the detail table (slave table) of product sales ), we need to add the document number of the main table to the item list to associate it to form a master-slave relationship. At the same time, this document number is encoded with the commodity to form the joint primary key of the detail table. This is only the general situation. We will extend this question a little: in details, we may sell each product at a different price. Some are sold at discounted prices, and some are sold at normal prices. To record these situations, we need the third table. In this case, the primary key of the third table requires the document number of the first table and the commodity number of the second table together with the information needed to form the joint primary key. or in other cases, in the first primary table, the Union primary key is formed. You also need to add multiple fields in the primary table to join them to form your primary key.

Redundant data storage: as the master-slave relationship expands, data that needs to be repeatedly stored in the database will become larger and larger. Or when the primary table itself is the Union primary key, all fields must be re-stored from the table.

SQL complexity increases: when multiple fields join the primary key, we need to associate multiple fields in the master table with multiple fields in the sub-table to obtain all detailed records meeting certain conditions.

Increasing Program Complexity: multiple parameters may need to be passed.

Lower efficiency: the database system needs to determine more conditions, and the length of SQL statements increases. Meanwhile, the Union primary key automatically generates the Union index.

Difficulties in WEB paging: Due to the union of primary keys (for most sub-tables), it is difficult to process self-association when paging is performed on WEB pages.

Solution

From the above, we have seen that the existing structure has many drawbacks, mainly because of the complexity of the program, reduced efficiency, and unfavorable paging.

To solve the preceding problem, this article proposes that when there is a master-slave relationship between the database tables in the background of the application system, a non-business field is added to the database table as the primary key, and this field is Numeric; this design should also be considered when the table needs to be queried by page in the application. Generally, we can add a field unrelated to the business logic to almost any table as the primary key field of the table.

Because this field is used as the primary key of the table, the first condition is that it must be unique in the table. In addition, you can create an auto-increment column for the SQL Server database based on its own characteristics:

Create TABLE T_PK_DEMO
(
U_ID bigint not null identity (1, 1 ),
-Unique ID of the record
COL_OTHER VARchar (20) not null,
-Other Columns
CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED
(U_ID)-defined as the primary key
)

However, there is an embarrassing fact in the auto-increment column in SQL Server, that is, once this field is defined and used, the user cannot directly intervene in the value of this field, which is completely controlled by the database system itself:

Full database system control, user cannot modify Value

When publishing and subscribing to databases, using auto-incrementing columns is troublesome.

It is troublesome to use auto-increment columns to restore some data.

The value of this column must be obtained after data is inserted.

In view of this, we recommend that you do not use the auto-incrementing column method to define it. Instead, you can refer to the sequence in the Oracle database system to implement the sequence function similar to the Oracle database system in the SQL Server system. This is described in the following section. We only need to modify the table definition as follows:

Create TABLE T_PK_DEMO
(
U_ID bigint not null,-Unique ID of the record
COL_OTHER VARchar (20) not null,-other columns
CONSTRAINT PK_T_PK_DEMO primary key nonclustered (U_ID)-defined as a PRIMARY KEY
)

With reference to the Oracle sequence function, we need to create a new table in the SQL Server database to manage the sequence values:

Create TABLE T_DB_SEQ
(
SEQ_NAMEVARchar (50) not null,-sequence name
SEQ_OWNER VARchar (50) not null default 'dbo ',
-Sequence owner (SYSTEM_USER)
SEQ_CURRENT bigint not null default 0,-current Sequence Value
SEQ_MIN bigint not null default 0,-minimum value of the sequence
SEQ_MAX bigint not null default 0,-minimum value of the sequence
SEQ_MAX bigint not null default 0,-maximum Sequence Value
SEQ_STEPINT not null default 1,-sequence growth step
IF_CYCLEINT not null default 0,-whether to cycle (0, NOT loop; 1, loop)
CONSTRAINT T_DB_SEQ PRIMARY KEY CLUSTERED
(SEQ_NAME, SEQ_OWNER)-primary key
)

The application system creates a sequence name for the table that needs to create an auto-incrementing column, which is reflected as a row in the database in the table "T_DB_SEQ.

First, you need to create a sequence for the table that requires sequence creation. Method: F_create_SEQ (sequence name ). This function is used to input the sequence name and insert a row in the table "T_DB_SEQ. The sequence owner. The system variable SYSTEM_USER is used.

Second, obtain the next value. Use F_GET_NEXT_SEQ_VAL (sequence name ). This function obtains the next value of the sequence based on the sequence name, and obtains the value based on the current value and the Growth step. At the same time, this function ensures concurrency consistency when obtaining the same sequence at the same time.

Third, return the return value to the application for use.

In addition, to ensure application integrity, some methods may need to be overloaded, and some other methods are also provided:

Obtain the current Sequence Value: F_GET_SEQ_CUR_VAL (sequence name)

Set the Sequence Value: F_SET_SEQ_VAL (sequence name)

Delete sequence: F_DEL_SEQ (sequence name)

Determine whether a sequence exists: F_SEQ_exists (sequence name)

In the design of a master-slave table, the child table also uses the sequence field as the unique primary key and uses the sequence field of the parent table as the foreign key Association:

Create TABLE T_PK_DEMO_C
(
U_ID bigint not null,-Unique ID of the record
COL_OTHER VARchar (20) not null,-other columns
P_ID int not null,-parent table ID
CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY
NONCLUSTERED (U_ID)-defined as the primary key
CONSTRAINT FK_T_PK_DEMO_C foreign key (P_ID)
REFERENCES T_PK_DEMO (U_ID) ON delete CASCADE,
)

Sequence Problems and Solutions