Database design: Table design named 10 points of attention

Source: Internet
Author: User
Tags field table

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.