Advance understanding of software Development (27) database table and index creation

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

The table structure is simple and complex, which requires a developer. How to design a table's fields is the best? How do you name a table's fields? 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 one of the projects that the author has worked on, there is an example of a table script (based on Sybase database) as follows:

--XXX
    
CREATE TABLE tb_xxx
    
 (
    
    AAA                varchar () not         null,-   AAA
    
    BBB                 int is 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

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/Programming/project/

As you can see, the above table scripting has at least the following problems:

(1) The name of the field is not very appropriate. Processtime1, Processtime2, and Processtime3, as shown in the red font, do not know what they mean after reading. Therefore, for the name of the field, to be intuitive and understandable, do not let others to guess.

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

(3) The number of indexes established is too small and no index is established on the Time field. You can consider increasing the number of indexes by creating a number of fields in a table with only two indexes set up. In addition, there are multiple time fields in the table, but not indexed on them, which requires that you consider indexing them whenever a Time field appears in the table.

2. After the modified table-building script

The modified script sample is 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 (24 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, C-C
    
Yyyy.mm.dd Hh24:mi:ss ...
    
Go to 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 shown in the red font. The default value for the Nextprocesstime field has been modified compared to the previous script, the number of indexes has been increased to 3, and the index has been established on the Time field. In addition, according to the general experience, the number of large table indexes is no more than 5, and the maximum number of fields is not more than 4.

3. Summary

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