Let you know in advance software Development (27): Creating Database Tables and Indexes

Article 2 part of the database SQL language

Creation of database tables and indexes

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

The table structure is simple and complex, which requires the developer to make a request.

How to design a table field is the best? What is the name of the table field? How do I define the type of a table field? How do I build an index? Wait a minute.

1. Create a table script before changing

In a project that the author has worked on, there is a build table script ( based on the Sybase database ) sample scale as follows:

--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, there are at least one of the following issues with the above table script:

(1) field naming is not very appropriate . As seen in the red font processtime1,processtime2,processtime3, after reading, still do not know what they mean. Therefore, for the name of the field, to be intuitive to understand. Don't let others guess.

(2) The default value for the Time field is empty .

The default value of the nextprocesstime field, as seen in the red font, is null. Generally speaking. For the Time field in the Database Build table script. If there is no special purpose, the default value is best set to the current time.

(3) The number of indexes established is too small and is not indexed on the Time field . There are very many fields in the table, but only two indexes are set up and the number of indexes is small, consider adding an index. In addition, there are multiple time fields in the table, but no indexes are built on them. Requires that only the time fields appear in the table, and that the index should be considered.

2. Post-change script for the build table

The script sample proportions after the change are as follows:

--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 changes are seen in the red font. Compared to the previous script, the default value of the nextprocesstime field was changed, the number of indexes was added 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

A table is one of the most important data structures in a database. In the process of creating a table, you must follow the principles of naming conventions, accurate information, and proper indexing.

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

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

Let you know in advance software Development (27): Creating Database Tables and Indexes