Database design: Table design named 10 points of attention

Original link: http://www.cnblogs.com/netsql/archive/2010/05/04/1727323.html

1. Table names are generally implemented as "module name _ Specific table name", and the same prefix is the same for a module. (Oracle is case sensitive and can be used in SQL without "_" because it can be written together in uppercase and lowercase.) This is also possible)

2. The table name should not be made too long (generally not more than three English words, not recommended to use Chinese pinyin, the total length of not more than 30 characters). Table name Use English reason, some projects have English version of the need, or this project is to foreign do, the use of English is the basic requirement, should say this is a habit problem, learn a little English is not bad  3. Do not use Tab or TB as the table prefix (which is a table and why).  4. Some of the tables as many-to-many joins can use the prefix of the two tables as the table name: For example: User Login table User_login, user Group table User_groupinfo, these two tables establish a many-to-many relationship table named: User_group_ Relation (Unity of relationship with Relation). Note that when the primary key is making foreign keys to other tables, or when referenced by other tables, the field descriptions and field names are kept as consistent as possible, such as the user field in the posting table Bbs_topic is written as ui_id, which is consistent with the primary key ui_id user_info the user Information table, looks comfortable, The correspondence is clear, it is not easy to be wrong, and it is confusing when inconsistent.   5. When there are some small, recurring values in the system, use a dictionary table to conserve storage space and optimize queries. such as the name of the user type in the region and system. Such values do not change during the program's run time, but need to be stored in the database. In the general database, there is a data dictionary table, which is used to save the basic data used by the system, a large field table such as the dictionary table of the province city area, unified with Dictionary_ as the prefix.      6. In relation to fields, some of the default special fields,  many tables, such as some business processing tables, in addition to adding the generated AutoNumber IDs (typically used as primary keys), the time CreateDate (creation time) of the record created, the creator of the record Creatby (note here, no ui_ ID (User Information table User_info primary key ui_id), because there are modified people), finally modified the person Lasteditby, the last modification time lasteditdate. (these can be used directly with Chinese characters, without coding, to improve the efficiency of the query) at the same time, it is necessary to note that the deletion of the record does not really delete, but instead of adding an identity bit, such as Xx_deletestaus delete state. 1 is valid, and 0 is invalid.  7. When a table is named, the name is represented in the singular form. For example, use Employee instead of Employees.  8. Database should be established such a table, is the database itself field information, table description, that is, the database design textFile of a table, convenient query use, what is unclear can be directly from the database query, database documents lost, comments lost, can be re-function.  9. Each table should have a primary key, the primary key is preferably a number, and is incremented, there are many tables of the primary key with 32-bit character encoding, the purpose of this is more security considerations. Because the index is inefficient when the character than characters, and the use of the self-increment column is not very small, such as adding the main table and operation from the table, the primary key of the main table is the foreign key from the table, this time also take the return value, and then add, can not be added at the same time. The primary key can be used with custom rules, most with the practice of Max (ID), or you can customize a sequence table, sort of like a sequence, or a time-of-month-day-second specific to milliseconds. With respect to the naming of columns, it is recommended to also do some specification for the data types, because it is easy to determine that there are only four main types: numbers, characters, time, logical values, which can be set in the type and length of the specification, unified together.      10. The Operation log table, log table, which is a necessary two tables in the database, this record also needs to be further saved. This has two situations, one is the operation log specific to a single field, and the other is the operation log for the entire table.

Several common tables are described in detail: Operation log table Sys_operatelog, log table Sys_loginlog,

System Dictionary table Sys_dictionary, System dictionary table type Sys_dictype

Operation Log Table Sys_operatelog

Chinese name	Field name	Comments
Operation Log Number	ol_id	Index column, number of the log
Type of operation	Ol_type	Is add, modify, delete, query and other class capacity (can be placed in the Universal Dictionary table)
Operation Module	Ol_module	The operation module, such as the news module, is associated with the menu table number
Action Content	Ol_content	What to manipulate, the more specific the better (before, after modification)
Operating person	ui_id	User's information
Operating time	Ol_adddate	Log Record creation time
Operating IP	Ol_ip	Operator's IP address
Notes Information	Ol_remarks	Note Information, some other information that needs to be explained

Such an operation log is more general, not specific to the specific field value update, if you want to specific to a specific value of the update, you need to design a new database

In general, these tables are required, but we have access to our own database, a table of database lists (number of tables in the database) (numbering, creation time, creator, modified time, modified person, table name, comment, whether deleted), Then is the database table below the field type (number, creation time, creator, modified time, modified person, field name, field type, field precision, field description, field comment, table number), that is, the field list, then the log Operation table can be designed such (number, table name, modified field name, modified value, Modified value, operator, operation time, related module, operation IP) This can record the change record, but the addition and deletion of the record is not very convenient to control.

Log in log table Sys_loginlog

Chinese name	Field name	Comments
Login log Number	ll_id	Log number of log in
Login person	ui_id	Login person
Logon Hours	Ll_adddate	Logon Hours
Login IP	Ll_ip	IP address of the login
Login status	Ll_status	Identity bit for logon success
Login Browser	Ll_browser	Login Browser
Login resolution	Ll_resolution	Screen resolution for Login

There is a data dictionary table, I have seen a lot of database design, type table one by one, not put together, and some simply write in comments, some simply do not, so that a programmer to go, this field no one knows, even if not go, oneself also may have long forgotten, so, It is important to see an underlying data dictionary table, other such as regional tables (sys_dicarea), Hanyu Pinyin (Sys_diccharacter) (for kanji and pinyin conversion) because of the large amount of data, the table is built separately. A Common Data dictionary table is introduced here.

System Dictionary Table Sys_dictionary

Chinese name	Field name	Comments
Dictionary number	sd_id	The number of the dictionary, which can be encoded directly using this primary key (note the associated relationship at the time of deletion)
Dictionary type	dy_id	Dictionary type ID, the dictionary type table needs to be established, because all the dictionary tables are placed
Dictionary encoding	Sd_code	Dictionary encoding to support your own encoding (the same type is unique and is generally integer type
Dictionary Chinese name	Sd_name	Dictionary Chinese names (such as men and women, such as status, can be placed in the dictionary table, as a basis for viewing)
Dictionary notes	Sd_remarks	Dictionary notes, dictionaries require some note information
Create person
Date Created
Modify People
Date Modified

System Dictionary Table type Sys_dictype

Chinese name	Field name	Comments
Dictionary type number	dt_id	Automatic index number of dictionary
Dictionary type name	Dt_name	The Chinese name of the dictionary type
Explanation of the Dictionary notes	Dt_remarks	Description of the notes used by the dictionary
Dictionary status	Dt_status	Whether the dictionary is deleted, not in use

Finally add some content, the general design database is like this, but do not rule out some special situation, in order to the confidentiality of data, the database table name and field names are some seemingly meaningless character numbers, such as table1,col1, but there is a table is a description table, or have a corresponding database document design.

Add: Some columns describe the unit type, which can be shown when designing the database, such as HEIGHTINCM, weightinkg. So at a glance.

Database design: Table design named 10 points of attention