Let you know in advance. Software Development (27): Creation of database tables and indexes

2nd part Database SQL language

Creation of database tables and indexes

Data Sheet ( or table ), is one of the most important components of the database. A database is just a framework, and a data table is the substance of its content. For example, a database is like an empty house, and the data sheet is the furniture inside, and the Unfurnished house is just a shell. Depending on the classification of the information, a database may contain several data tables of different uses.

The table structure is simple and complex, which puts forward the requirements for developers. How to design a table field is the best? How are the fields of the table named? How do I define the type of a table field? How do I build an index? Wait a minute.

1. Modify the previous build table script

In a project that the author has worked on, there is an example of a build table script ( based on a Sybase database ) that looks like this:

--XXX

CREATE TABLE Tb_xxx

(

AAA varchar (+) NOT NULL,--AAA

BBB int NOT NULL,--BBB

. . . . . .

. . . . . .

processtime1 varchar (") default (") NULL,--YYYY.MM.DD Hh24:mi:ss

processtime2 varchar (") default (") NULL,--YYYY.MM.DD Hh24:mi:ss

processtime3 varchar (") default (") NULL,--YYYY.MM.DD Hh24:mi:ss

    . . . . . .

nextprocesstime varchar (") default (") not NULL,--YYYY.MM.DD Hh24:mi:ss

. . . . . .

)

Go

Create unique index idx1_tb_xxx on tb_xxx (AAA)

Create INDEX idx2_tb_xxx on tb_xxx (BBB)

Go

As you can see, the above-mentioned script has at least the following issues:

(1) field naming is not very appropriate . processtime1,processtime2andProcesstime3, as shown in the red font, do not know what they mean after reading it. Therefore, for the name of the field, to be intuitive to understand, do not let others to guess.

(2) The default value for the Time field is empty . The default value of the nextprocesstime field, as shown in the red font, is null. In general, the default value for a Time field in a database-built table script is best set to the current time, if no special purpose is used.

(3) The number of indexes established is too small and is not indexed on the Time field . There are many fields in the table, but only two indexes are set up, the number is small, and you can consider increasing the indexes. In addition, the table has multiple time fields, but does not have an index on them, and requires that the index be considered whenever a time field appears in the table.

2. Revised build Table script

The following script example is modified:

--XXX

CREATE TABLE Tb_xxx

(

AAA varchar (+) NOT NULL,--AAA

BBB int NOT NULL,--BBB

. . . . . .

. . . . . .

firstprocesstime varchar (") default (") NULL,--YYYY.MM.DD hh24:mi:ss

secondprocesstime varchar (") default (") NULL,--YYYY.MM.DD Hh24:mi:ss

thirdprocesstime varchar (") default (") NULL,--YYYY.MM.DD Hh24:mi:ss

    . . . . . .

nextprocesstime varchar default convert (Varchar,getdate (), 102) + ' +convert (Varchar,getdate (), 108) NOT NULL,--YYYY.MM.DD Hh24:mi:ss

. . . . . .

)

Go

Create unique index idx1_tb_xxx on tb_xxx (AAA)

Create INDEX idx2_tb_xxx on tb_xxx (BBB)

Create INDEX idx4_tb_xxx on tb_xxx (nextprocesstime)

Go

The modified place is shown in the red font. Compared to the previous script, the default value of the nextprocesstime field was modified, the number of indexes was increased to 3 , and the index was established on the Time field. In addition, according to the general experience, the number of large table index is not more than 5 , the maximum number of indexed fields not more than 4 .

3. Summary

Table is one of the most important data structures in the database, in the process of creating the table, we must follow the principles of naming specification, accurate information and proper index.

(I Weibo: Http://weibo.com/zhouzxi?topnav=1&wvr=5, No.: 245924426, welcome attention!) )