Database Table naming rules and database table naming rules

Database Table naming rules and database table naming rules

1. Table names are generally implemented using [module name_specific table name]. The prefixes of the same module are the same. (Oracle is case sensitive. You do not need to use "_" in SQL, because it can be written in case. This is also possible)

2. The table name should not be too long (generally no more than three English words, Chinese Pinyin is not recommended, and the total length should not exceed 30 characters ). The reason why the table name is used in English is that some projects have English version requirements, or when the project is for foreign countries, using English is a basic requirement. It should be said that this is a habit problem, learning more English is not a bad thing. 3. do not use tab or tb as the table prefix (originally a table, why ). 4. for multiple-to-multiple-join tables, you can use the prefix of two tables as the table name, for example, User_Login, User_GroupInfo, the two tables create many-to-many relationships with the name User_Group_Relation ). Note that when the primary key is used as the foreign key of another table or when referenced by another table, the field description and field name should be consistent as much as possible. For example, the user field in the post table BBS_Topic should be written as UI_ID, in this way, it is consistent with the primary key UI_ID of User_Info in the user information table. It looks comfortable, has a clear relationship, and is not easy to make mistakes. It is easy to understand the inconsistency between the two. 5. When there are a few duplicate values in the system, use dictionary tables to save storage space and optimize queries. For example, user code of the region or system. These values do not change during the running period of the program, but must be stored in the database. Generally, a database has a data Dictionary table used to save the basic data used by the system. Large Field tables, such as the Dictionary tables of provinces and cities, are prefixed with Dictionary. 6. it is related to fields. Some special fields are used by default. For example, in some business processing tables, apart from adding the generated automatic ID (usually used as the primary key ), the time when the record was created, CreateDate (Creation Time). The creator of the record, CreatBy (note that there is no UI_ID (the primary key UI_ID of User_Info in the user information table), because there is a modifier ), last modified by LastEditBy, last modified by LastEditDate. (These can directly use Chinese characters instead of encoding to improve query efficiency.) At the same time, you must note that when you delete a record, you do not actually delete it, but add a flag, for example, XX_DeleteStaus is deleted. 1 is valid, and 0 is invalid. 7. When naming a table, the name is expressed in the singular form. For example, use Employee instead of Employees. 8. the database should create such a table, that is, the field information of the database itself, the description of the table, that is, a table in the database design document for convenient query and use, any unknown information can be directly queried from the database. database documents may be lost, and comments may be lost. 9. each table should have a primary key, which is preferably a number and is incremental. Many tables have primary keys encoded with 32 characters, the purpose of doing so is to consider security. Because there are many characters in the index, the indexing efficiency is low, and the auto-incrementing column is not very small. For example, when you add a master table or a slave table, the primary key of the master table is the foreign key of the slave table, at this time, the return value is also obtained and then added. It cannot be added at the same time. You can use custom rules for primary keys. Most of them use the MAX (ID) method. You can also customize a sequence table, which is a bit like a sequence, or use the time of the year, month, and second to the specific millisecond. For column naming, we recommend that you also standardize the data type, because it is easy to determine that there are only four main types: Numbers, characters, time, and logical values, these specifications can be set and unified in terms of type and length. 10. Operation Log table and logon log table, which are two essential tables in the database, need to be further saved. There are two scenarios: one is the operation log specific to a single field, and the other is the operation log of the entire table.

Common tables: operation log table Sys_OperateLog, logon log table Sys_LoginLog,

System dictionary table Sys_Dictionary, system dictionary table Type Sys_DicType

 

Operation Log table Sys_OperateLog

Chinese name	Field name	Note
Operation log number	OL_ID	Index column, log ID
Operation Type	OL_Type	Is to add, modify, delete, query and other class capacities (can be placed in a common dictionary table)
Operation Module	OL_Module	The operation module, such as the news module, is associated with the menu table number.
Operation content	OL_Content	The more specific the operation is, the better (before and after modification)
Operator	UI_ID	User Information
Operation Time	OL_AddDate	Log Creation Time
Operation IP Address	OL_IP	Operator's IP address
Remarks	OL_Remarks	Remarks, some other information that needs to be described

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

Generally, we need to have these tables, which may already exist in the system. But we can get them to our own database. One is the table in the Database List (that is, several tables in the database) (number, creation Time, creator, modification time, modifier, table name, comment, whether to delete), and then the field type (number, creation time, creator, modification time, the modifier, field name, field type, field precision, field description, field comment, and table number), that is, the field list. In this case, the log operation table can be designed as follows (number, table name, the modified field name, pre-modification value, modified value, operator, operation time, related module, and Operation IP Address can record the modification record, however, when you add or delete a record, it is not easy to control.

 

Logon log table Sys_LoginLog

Chinese name	Field name	Note
Logon log number	LL_ID	Logon log number
Login person	UI_ID	Login person
Logon Time	LL_AddDate	Logon Time
Logon IP Address	LL_IP	Logon IP Address
Logon status	LL_Status	ID of whether the logon is successful
Log on to the browser	LL_Browser	Log on to the browser
Logon resolution	LL_Resolution	Login Screen Resolution

There is also a data dictionary table. I have read a lot of database designs, and the type tables are one by one. They are not put together. Some are simply written in comments, and some are not at all, this way, no one knows this field when a programmer leaves. Even if the field does not, it may take a long time to forget it. Therefore, it is very important to see a basic data dictionary table, for other regions, such as Sys_DicArea and Sys_DicCharacter (used for conversion of Chinese characters and pinyin), tables are created separately because of the large data volume. This section describes general data dictionary tables.

 

System dictionary table Sys_Dictionary

Chinese name	Field name	Note
Dictionary number	SD_ID	Dictionary number. You can use this primary key encoding directly (note the association relationship when deleting)
Dictionary type	DY_ID	ID of the dictionary type. You need to create a dictionary type table because all the dictionary tables are placed.
Dictionary Encoding	SD_Code	Dictionary encoding, supports self-encoding (the same type is unique, generally Integer type
Chinese Dictionary name	SD_Name	Chinese Dictionary name (such as men and women, such as status, can be placed in the dictionary table as the basis for viewing)
Dictionary remarks	SD_Remarks	Dictionary remarks. The dictionary requires some remarks.
Created
Creation date
Modified
Modification date

 

Sys_DicType

Chinese name	Field name	Note
Dictionary type number	DT_ID	Automatic dictionary Index Number
Dictionary type name	DT_Name	Chinese name of the dictionary type
Dictionary remarks	DT_Remarks	Description of dictionary usage
Dictionary status	DT_Status	Whether the dictionary is deleted or not. It is not in use.

 

Finally, I want to add some content. Generally, the database design looks like this, but it does not rule out some special situations. For the sake of data confidentiality, the table name and field name of the database are all seemingly meaningless characters and numbers, such as Table1 and Col1. However, there is a table indicating the table or corresponding database document design.

 

Note: Some columns describe the unit type, which can be indicated when designing the database, such as HeightIncm and WeightInKg.

Naming rules for acess database tables

ACCESS development naming rules
Database character rules

It consists of 26 English letters (case-sensitive) and 0-9, and underlines (_), 63 characters in total. No other characters are allowed (except comments ).

Database object naming rules

Database objects include tables, views (queries), stored procedures (parameter queries), functions, and constraints. The object name consists of the prefix and actual name, and the length cannot exceed 30. Prefix: Use lowercase letters.

For example:

Table tb
View vi
Stored Procedure sp
Function fn

ACTUAL NAME

The actual name is used to describe the object content. It is composed of words or words. The first letter of each word is uppercase, And the other letters are lowercase. It does not start with a number or.
For example:

Table User_Info
View UserList
Stored Procedure UserDelete

Therefore, valid object names are similar to the following.

Table tbUser_Info and tbMessage_Detail
View vi_MessageList
Stored Procedure sp_MessageAdd

Database Table naming rules

A field consists of a prefix and an actual name. The system tries its best to use the same word as the first word in the actual name.
Prefix: tb, a lowercase letter, indicates the table.
Example: tbMember
TbMember_Info
TbForum_Board
TbForum_Thread1

Field naming rules

Number, character, date/time, lob (large object), miscellaneous, the field is composed of the abbreviation of the table, underline, the actual name and suffix.
Suffix: lowercase letters are used to indicate the attributes of the field.
Example: User_Idint
User_Namestr
User_RegDatedtm

View naming rules

A field consists of a prefix and an actual name, and is connected by an underscore in the middle.
Prefix: Use the lower-case letter vi to represent the view.
Example: vi_User
Vi_UserInfo

Stored Procedure naming rules

A field consists of a prefix and an actual name, and is connected by an underscore in the middle.
Prefix: sp, a lowercase letter, indicates the stored procedure.
Example: sp_User

Database Design Document rules

All database designs should be written into documents, which are expressed in modular form. The general format is as follows:
'-------------------------------------------
'Table name: tbUser_Info
'Created by: UAM_Richard
'Date: 2004-12-17
'Version: 1.0
Description: save user information.
'Specific content:
'Userid int, automatic incremental user code
'Username char (12) UserName
'......
'--------------------------------------------

SQL statement rules

All SQL keywords are capitalized, such as Select, Update, FROM, ORDER, and .... Remaining full text>

Database naming rules?

Currently, online databases cannot use Chinese characters, including databases, tables, and fields:
1. Chinese characters are unavailable
2. There cannot be any invalid characters, such as % ^ &.
3. A combination of letters, numbers, and letters, mathematics, and.