MYSQL database Naming and Design Specifications

Do you have a headache in obtaining MYSQL database naming and designing specifications? If this is the case, the following articles will give you corresponding solutions. The following articles mainly introduce solutions for obtaining MYSQL database naming and designing specifications, the following is a detailed description of the relevant content. 1) help standardize and standardize data

Do you have a headache in obtaining MYSQL database naming and designing specifications? If this is the case, the following articles will give you corresponding solutions. The following articles mainly introduce solutions for obtaining MYSQL database naming and designing specifications, the following is a detailed description of the relevant content. 1) help standardize and standardize data

Do you have a headache in obtaining MYSQL database naming and designing specifications? If this is the case, the following articles will give you corresponding solutions. The following articles mainly introduce solutions for obtaining MYSQL database naming and designing specifications, the following is a detailed description of the relevant content.

1) standardization and standardization

Data Standardization helps eliminate data redundancy in databases. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to have achieved the best balance in terms of performance, scalability and data integrity. To put it simply, the table design principles for databases that comply with the 3NF standard are:

"One Fact in One Place" means that a table only contains its own basic attributes. When they are not their own attributes, they must be decomposed. The relationships between tables are connected by foreign keys. It has the following features: a group of tables specifically store the associated data connected by keys.

For example, a 3NF database storing customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the Customer associated with the Order, but stores a key value. The key points to the row in the Customer table that contains the Customer information.

In fact, for the sake of efficiency, it is sometimes necessary not to standardize the table.

2) data-driven

Using data-driven, not hard-coded methods, many policy changes and maintenance are much easier, greatly enhancing system flexibility and scalability.

For example, if you want to access external data sources (files, XML documents, and other databases) on the user interface, you may wish to store the connection and path information in the user interface support table. In addition, if you execute tasks such as workflows on the user interface (such as sending emails, printing letterhead, and modifying record status), the workflow data can also be stored in the database. Role permission management can also be completed through data-driven. In fact, if the process is data-driven, you can push a considerable amount of responsibility to users to maintain their own workflow processes.

3) consider various changes

When designing a database, consider which data fields may change in the future.

For example, the last name is the same as the last name of a Western person, for example, the last name of a female after marriage ). Therefore, when a system is created to store customer information, the last name field is stored in a separate data table, and fields such as the start date and end date are appended, in this way, we can track the changes of this data entry.

2. database character Specification

It consists of 26 English letters (case sensitive) and 10 natural numbers 0-9 with an underscore '_'. It contains 63 characters in total. Other characters are not allowed (except for comments ).

Note:

1) The above MYSQL database naming rules cannot exceed 30 characters. The variable name length limit is 29 (excluding the logo character @).

2) The names of Data Objects and variables are all English characters. Chinese naming is forbidden. Do not leave spaces between characters of the Object Name.

3) Be careful when retaining words. Ensure that your field names do not conflict with the reserved words, database systems, or common access methods.

5) ensure the consistency of Field Names and types. Ensure consistency when naming fields and specifying data types for them. if the data type is an integer in one table, the Data Type in the other table should not be converted to the numeric type.

3. Database MYSQL database naming rules

Databases and data tables all use prefixes

The formal database name is composed of lower-case English letters and underscores. It should be noted that the application or system is in use. For example:

 
 

  
  
web_19floor_net 
  
  
web_car 
 
 

The backup database name is composed of the official database name and the backup time, for example:

 
 

  
  
web_19floor_net_20070403 
  
  
web_car_20070403 
 
 

4. Database Table MYSQL database naming rules

The data table name is composed of lower-case English letters and underscores. Try to indicate that the application or system is in use.

Data Tables of related applications use the same prefix. For example, tables in the Forum use the cdb _ prefix, and data tables in the blog use the supe _ prefix. The prefix name generally cannot exceed 5 characters.

For example:

 
 

  
  
web_user 
  
  
web_group 
  
  
supe_userspace 
 
 

The backup data table name is composed of the formal table name and the backup time, for example:

 
 

  
  
web_user_20070403 
  
  
web_group_20070403 
  
  
supe_userspace_20070403 
 
 

5. Field MYSQL database naming rules

The field name is a combination of words. The first letter is lowercase, And the last letter is uppercase. It is best to have a table name prefix.

For example, fields in the web_user table:

 
 

  
  
userId 
  
  
userName 
  
  
userPassword 
 
 

The fields associated with the table must use a uniform name,

For example, the userId in the web_user table corresponds to the userId in the web_group table.

6. Field Type specification

Rule: use up a small amount of storage space to store data with a few fields.

For example, if int is used, char or varchar is not required.

Int is not required if tinyint can be used.

Varchar (255) is not used when varchar (20) is used)

The time stamp field should be int type as much as possible, such as created: indicates the int seconds starting from '2017-01-01 08:00:00 ', using the English word's past formula; gmtCreated: indicates the datetime type of time, that is, the time string in the format of '2017-01-01 00:00:00 '. The corresponding type in Java is Timestamp.

7. database design document specifications

All database designs should be written into documents, which are expressed in modular form. The general format is as follows:

'-------------------------------------------

'Table name: web_user

'Author: Aeolus (silly fish)

'Date:

'Version: 1.0

Description: save user information.

'Specific content:

'Userid int, automatic incremental user code

'Username char (12) UserName

'......

'--------------------------------------------

8. indexing principles:

1) the logical primary key uses a unique group index, and uses a unique non-group index for the system key (as a stored procedure) and a non-group index for any foreign key column. consider the size of the database space, how tables are accessed, and whether these accesses are mainly used for reading and writing.

2) most databases index primary key fields automatically created, but do not forget to index Foreign keys, which are also frequently used keys, for example, running a query to display a record of the master table and all associated tables can be used.

3) do not index blob/text or other fields, or index large fields (with many characters). This will make the index occupy too much storage space.

4) do not index frequently used small tables

Do not set any keys for small data tables. Do not do this if they often have insert or delete operations. the index maintenance for these insert and delete operations may consume more time than the scan tablespace.

9. SQL statement Specification

All SQL keywords are capitalized, such as SELECT, UPDATE, FROM, ORDER, BY, etc. All table names and database names must contain''

For example:

Select count (*) FROM 'cdb _ members 'WHERE 'username' = 'aeolus ';

10. other design skills

1) Avoid using triggers

The functions of a trigger can be implemented in other ways. When debugging a program, the trigger may become interfering. If you really need to use the trigger, you 'd better document it in a centralized manner.

2) use common English (or any other language) instead of coding or initials

When creating drop-down menus, lists, and reports, it is best to sort them by English name. If you need to encode them or use the initials of Pinyin, you can attach them to the English you know.

3) save common information

It is very useful to allow a table to store general database information. store the current database version, recent check/repair (Access), name of the associated design document, and customer information in this table. in this way, a simple mechanism can be implemented to track databases. When customers complain that their databases do not meet the expected requirements and contact you, this is especially useful for non-client/server environments.

4) include version Mechanism

Introduce the version control mechanism in the database to determine the version of the database in use. after a long period of time, users' requirements will always change. in the end, you may need to modify the database structure. it is more convenient to store version information directly in the database.

5) document preparation

All shortcuts, MYSQL database naming conventions, restrictions, and functions must be documented.

Database tools that add annotations to tables, columns, and triggers are useful for development, support, and tracking and modification.

Document the database, or create a document in the database itself or independently. In this way, after more than a year, we will go back to the 2nd version, which will greatly reduce the chance of making mistakes.

6) tests, tests, and repeated tests

After creating or revising the database, you must use the new data test data field entered by the user. the most important thing is to allow users to perform tests and ensure that the selected data type meets the commercial requirements. the test should be completed before the new database is put into the actual service.

7) Check Design

The common technology used to check the database design during development is to check the database through its supported application prototype. in other words, for each prototype application that ultimately expresses data, you must check the data model and view how to retrieve the data.