MYSQL database naming and design specification
- 2020-05-07 20:32:07
- OfStack
1. Design principles
1) standardize and standardize
Standardization of data helps to eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to be the best balance between performance, scalability, and data integrity. In simple terms, the table design principle for a database that complies with the 3NF standard is "One Fact in One Place", which means that a table contains only its own basic properties and needs to be decomposed when they are not their own properties. Relationships between tables are joined by foreign keys. It has the following characteristics: there is a set of tables dedicated to the associated data linked by keys.
For example, a 3NF database for customers and their related orders might have two tables: Customer and Order. The Order table does not contain any information about the customer associated with the order, but it contains a key value that points to the row containing the customer information in the Customer table.
In fact, it is sometimes necessary not to standardize tables for efficiency reasons.
2) data driven
With a data-driven rather than hard-coded approach, makes many policy changes and maintenance much easier, greatly increasing the flexibility and scalability of the system.
For example, if the user interface wants to access external data sources (files, XML documents, other databases, and so on), store the connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (sending an email, printing a note, modifying record status, and so on), the data that generates the workflow can be stored in a database. Role authority management can also be done through data-driven. In fact, if the process is data-driven, you can put considerable responsibility on the user to maintain their workflow process.
3) consider variations
designs the database taking into account which data fields might change in the future.
For example, this is true of surnames (note that they are western surnames, such as those of women who follow their husbands after marriage). So, when you set up a system to store customer information, you store the last name field in a separate table, with fields such as start and end dates attached, so you can keep track of changes to the 1 data entry.
2. The database involves the character specification
It is composed of 26 English letters (case sensitive) and 10 natural Numbers (0-9), plus the underscore '_', and contains 63 characters. No other characters (except comments) are allowed.
Notes:
1) the above names shall not exceed the system limit of 30 characters. The length limit of the variable name shall be 29(excluding the identity character @).
2) the names of data objects and variables are in English characters, and it is forbidden to use Chinese names.
3) be careful with reserved words and make sure your field names do not conflict with reserved words, database systems, or common access methods
5) keep field names and types 1, 1 must be 1 when naming a field and specifying a data type for it. If the data type is an integer in one table, it will not be a character in another table.
3. Database naming specification
Database, data table 1 law USES prefix
The official database name is in lowercase English with an underscore to indicate which application or system is using it. For example:
web_19floor_net
web_car
The backup database name consists of the official library name and the backup time, such as:
web_19floor_net_20070403
web_car_20070403
4. Database table naming specification
Data table names are in lowercase English with underscores to indicate which application or system is using them.
The data table of related applications is prefixed with the same 1, such as the forum table is prefixed with cdb_, the blog data table is prefixed with supe_, the prefix name 1 is not more than 5 words
Such as:
web_user
web_group
supe_userspace
The backup data table name consists of the formal table name and the backup time, such as:
web_user_20070403
web_group_20070403
supe_userspace_20070403
5. Field naming specification
The name of the field is composed of words with a lowercase first letter followed by an uppercase first letter, preferably with a table name prefix.
For example, the fields of web_user table:
userId
userName
userPassword
The associated fields between tables should have the same 1 name,
For example, userId in web_user corresponds to userId in web_group
6. Field type specification
rule: use as little storage space as possible to store 1 field of data.
If you can use int instead of char or varchar
If you can use tinyint, you can't use int
varchar(20) instead of varchar(255)
Try to use int for the timestamp field, such as created: for the number of int seconds starting from '1970-01-01 08:00:00 ', using the past tense of the English word; gmtCreated: represents the time of type datetime, that is, the time string shaped like '1980-01-01 00:00:00 '. The corresponding type in Java is Timestamp
7. Database design document specification
All database design should be written as a document, which is expressed in a modular form. The general format is as follows:
'-- -- -- -- -- -- -- -
'table name: web_user
'by Aeolus(silly fish)
Date: 2007-04-11
'version: 1.0
Description: save user data
'specific contents:
'UserID int, automatically increments the user code
'UserName char(12) user name
'...
'-- -- -- -- -- -- -- �
8. Principles of index use:
1) logical primary keys use 1-only grouped indexes, system keys (as stored procedures) use 1-only non-grouped indexes, and any foreign key columns use non-grouped indexes.
2) most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used, such as running a query to display a record of the primary table and all associated tables.
3) do not index fields such as blob/text, and do not index large fields (with many characters) as this will take up too much storage space.
4) do not index commonly used small tables
Do not set any keys for small data tables, especially if they often have insert and drop operations. Index maintenance for these inserts and drops can take more time than scanning a table space.
9.sql statement specification
All sql keywords all caps, such as SELECT, UPDATE, FROM, ORDER, BY etc., all the table name and the library should use "contains
Such as:
SELECT COUNT(*) FROM 'cdb_members' WHERE 'userName' = 'aeolus';
10. Other design tips
1) avoid triggers
Triggers can often be implemented in other ways. Triggers can be disruptive when debugging programs. If you do need triggers, you'd better focus on documenting them.
2) use common English (or any other language) instead of coding or pinyin acronyms
When creating drop-down menus, lists, and reports, it's best to sort them by English names.
3) save common information
Make a table of special database information is very useful for 1. The current version is stored in this table database, recent inspection/repair (to Access), associated design documents, such as customer information. The name of the tracking database that can realize a simple mechanism, when a customer complain that their database not reached the requirements of hope and contact you, do this for the client/server environment particularly useful.
4) include version mechanism
Introduce a version control mechanism in the database to determine the version of the database in use. Over time, user requirements will always change. Eventually, the database structure may need to be modified. It is more convenient to store the version information directly in the database.
5) documentation
Document all shortcuts, naming conventions, restrictions, and functions.
Database tools that annotate tables, columns, triggers, etc. Useful for developing, supporting, and tracking changes.
Document the database, either within the database itself or separately, so that when you go back to version 2 after more than a year, the chances of making a mistake are greatly reduced.
6) test, test and repeat test
After the database is created or modified, the data fields must be tested with the new data entered by the user.
7) check the design
A common technique for reviewing database design during development is to examine the database through the supported application prototypes. In other words, for each prototype application that ultimately represents the data, make sure you examine the data model and see how the data is retrieved.
1) standardize and standardize
Standardization of data helps to eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to be the best balance between performance, scalability, and data integrity. In simple terms, the table design principle for a database that complies with the 3NF standard is "One Fact in One Place", which means that a table contains only its own basic properties and needs to be decomposed when they are not their own properties. Relationships between tables are joined by foreign keys. It has the following characteristics: there is a set of tables dedicated to the associated data linked by keys.
For example, a 3NF database for customers and their related orders might have two tables: Customer and Order. The Order table does not contain any information about the customer associated with the order, but it contains a key value that points to the row containing the customer information in the Customer table.
In fact, it is sometimes necessary not to standardize tables for efficiency reasons.
2) data driven
With a data-driven rather than hard-coded approach, makes many policy changes and maintenance much easier, greatly increasing the flexibility and scalability of the system.
For example, if the user interface wants to access external data sources (files, XML documents, other databases, and so on), store the connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (sending an email, printing a note, modifying record status, and so on), the data that generates the workflow can be stored in a database. Role authority management can also be done through data-driven. In fact, if the process is data-driven, you can put considerable responsibility on the user to maintain their workflow process.
3) consider variations
designs the database taking into account which data fields might change in the future.
For example, this is true of surnames (note that they are western surnames, such as those of women who follow their husbands after marriage). So, when you set up a system to store customer information, you store the last name field in a separate table, with fields such as start and end dates attached, so you can keep track of changes to the 1 data entry.
2. The database involves the character specification
It is composed of 26 English letters (case sensitive) and 10 natural Numbers (0-9), plus the underscore '_', and contains 63 characters. No other characters (except comments) are allowed.
Notes:
1) the above names shall not exceed the system limit of 30 characters. The length limit of the variable name shall be 29(excluding the identity character @).
2) the names of data objects and variables are in English characters, and it is forbidden to use Chinese names.
3) be careful with reserved words and make sure your field names do not conflict with reserved words, database systems, or common access methods
5) keep field names and types 1, 1 must be 1 when naming a field and specifying a data type for it. If the data type is an integer in one table, it will not be a character in another table.
3. Database naming specification
Database, data table 1 law USES prefix
The official database name is in lowercase English with an underscore to indicate which application or system is using it. For example:
web_19floor_net
web_car
The backup database name consists of the official library name and the backup time, such as:
web_19floor_net_20070403
web_car_20070403
4. Database table naming specification
Data table names are in lowercase English with underscores to indicate which application or system is using them.
The data table of related applications is prefixed with the same 1, such as the forum table is prefixed with cdb_, the blog data table is prefixed with supe_, the prefix name 1 is not more than 5 words
Such as:
web_user
web_group
supe_userspace
The backup data table name consists of the formal table name and the backup time, such as:
web_user_20070403
web_group_20070403
supe_userspace_20070403
5. Field naming specification
The name of the field is composed of words with a lowercase first letter followed by an uppercase first letter, preferably with a table name prefix.
For example, the fields of web_user table:
userId
userName
userPassword
The associated fields between tables should have the same 1 name,
For example, userId in web_user corresponds to userId in web_group
6. Field type specification
rule: use as little storage space as possible to store 1 field of data.
If you can use int instead of char or varchar
If you can use tinyint, you can't use int
varchar(20) instead of varchar(255)
Try to use int for the timestamp field, such as created: for the number of int seconds starting from '1970-01-01 08:00:00 ', using the past tense of the English word; gmtCreated: represents the time of type datetime, that is, the time string shaped like '1980-01-01 00:00:00 '. The corresponding type in Java is Timestamp
7. Database design document specification
All database design should be written as a document, which is expressed in a modular form. The general format is as follows:
'-- -- -- -- -- -- -- -
'table name: web_user
'by Aeolus(silly fish)
Date: 2007-04-11
'version: 1.0
Description: save user data
'specific contents:
'UserID int, automatically increments the user code
'UserName char(12) user name
'...
'-- -- -- -- -- -- -- �
8. Principles of index use:
1) logical primary keys use 1-only grouped indexes, system keys (as stored procedures) use 1-only non-grouped indexes, and any foreign key columns use non-grouped indexes.
2) most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used, such as running a query to display a record of the primary table and all associated tables.
3) do not index fields such as blob/text, and do not index large fields (with many characters) as this will take up too much storage space.
4) do not index commonly used small tables
Do not set any keys for small data tables, especially if they often have insert and drop operations. Index maintenance for these inserts and drops can take more time than scanning a table space.
9.sql statement specification
All sql keywords all caps, such as SELECT, UPDATE, FROM, ORDER, BY etc., all the table name and the library should use "contains
Such as:
SELECT COUNT(*) FROM 'cdb_members' WHERE 'userName' = 'aeolus';
10. Other design tips
1) avoid triggers
Triggers can often be implemented in other ways. Triggers can be disruptive when debugging programs. If you do need triggers, you'd better focus on documenting them.
2) use common English (or any other language) instead of coding or pinyin acronyms
When creating drop-down menus, lists, and reports, it's best to sort them by English names.
3) save common information
Make a table of special database information is very useful for 1. The current version is stored in this table database, recent inspection/repair (to Access), associated design documents, such as customer information. The name of the tracking database that can realize a simple mechanism, when a customer complain that their database not reached the requirements of hope and contact you, do this for the client/server environment particularly useful.
4) include version mechanism
Introduce a version control mechanism in the database to determine the version of the database in use. Over time, user requirements will always change. Eventually, the database structure may need to be modified. It is more convenient to store the version information directly in the database.
5) documentation
Document all shortcuts, naming conventions, restrictions, and functions.
Database tools that annotate tables, columns, triggers, etc. Useful for developing, supporting, and tracking changes.
Document the database, either within the database itself or separately, so that when you go back to version 2 after more than a year, the chances of making a mistake are greatly reduced.
6) test, test and repeat test
After the database is created or modified, the data fields must be tested with the new data entered by the user.
7) check the design
A common technique for reviewing database design during development is to examine the database through the supported application prototypes. In other words, for each prototype application that ultimately represents the data, make sure you examine the data model and see how the data is retrieved.