002-Database Naming Development specification

I. Overview

The design specification is more to ensure the rationality of the database design, for the final coordination of the project stability, and the naming specification is more to ensure the formal and unified design. To be fair, the table field in the database and so on what kind of naming, take specific what name, and does not directly affect the stability of the project.

The direct purpose of norm-setting is to restrain design behavior, and the ultimate goal is to ensure the reasonable unification of design. Although the specification is made by people with rich experience in the project, it is not the will of the individual but the will of the project, which is of great benefit to the project, because it is meaningful to abide by this norm. So the specification is for the benefit of the project in order to maximize the formation of a contract in the team (the seemingly agreed English word convention itself has a normative meaning), all involved in the design of the staff to abide by this agreement, all involved in the development of the people will interpret the design according to this agreement. We agreed that all the primary key unified named ID, the result is a designer violates the Convention to name a non-primary key field ID, the contract was broken, the consensus was broken, designers, developers and designers communication between the gap.

Design specifications More is for the sake of rationality, naming norms more for the unification, teamwork, unity in some degree than the local design and development of the good or bad is more important. Violation of the Convention, the local design and development of the better, but may affect the overall stability and coordination of the project.

In the "design specification" mentioned a number of naming conventions, but also detailed about the table, the type of the field, comments and other properties of the settings, why the primary key is required to be named ID, unified to char (32) Type, why the need to unify the floating-point value of the decimal type? We want people in the team to see the results of the design, and at a glance we can see what this field does and what it means, but not just the name. Moreover, the current development model, the front-end code and database documents, program documents, interface documents and so on are mostly generated by the tool, and its bottom-up is the database, table, the name of the field of the comments will affect the tool-generated documents, the Class property method in the code and even the foreground page named comments, The specification of database design naming relates to the specification of the entire project.

Second, the common naming method in programming

A Hungarian nomenclature. Proposed by a Microsoft Hungarian programmer Charles Simonyi, a relatively complex, first-letter lowercase, the basic principle is: variable name = attribute + Type + object description, where each object name requires a clear meaning, you can take the name of the object or part of the name. Hungarian nomenclature is mainly used in process-oriented programming languages such as C or C + +, which is awkward if used in the process-oriented language of Java and C #.

b Camel naming law. That is, the camel-named method, the first letter lowercase, the name of the nomenclature looks like camel hump as high and low ups and downs. There are two forms of the Camel Naming act:

The first is to mix uppercase and lowercase letters, such as Englishname, Farthercode. In Java, property names and method names are generally used in this way, and in C # only property names are named in this way, and as we have previously stated, the naming of fields in SQL Server is also used in this way.

The second is to underline the words, such as English_name, Farther_code. We have previously stated that Oracel and MySQL tables and fields are named in this way, but we require Oracle to use all uppercase letters, and MySQL to use all lowercase letters. Furthermore, all constants, whether in Java or C #, or even in JavaScript, use this naming method, but they are all capitalized in the same way that Oracle table fields are named, such as the dictionary type when the data dictionary table is introduced in the previous design specification, The encoding and textual information of a dictionary item needs to be acquired immediately, and the usual habit is to establish a constant class in the program, and all the dictionary data used in it are marked with constants, and this is how the constant number is named.

C Pascal named Law. That is, the Pascal nomenclature, similar to the Camel Naming Act, is simply capitalized. In C #, class names and method names are typically named this way, and in Java class names are generally used in this way. As stated earlier, the naming of databases and tables in SQL Server is also used in this way.

　　

Third, the basic norms

1. Naming specification

With 26 letters (case-sensitive) and 0-9 of these 10 natural numbers, plus the underscore ' _ ' composition, a total of 63 characters. A combination of variable names.

No numbers are allowed for database and table names, and field names do not allow numbers unless special circumstances occur.

Convention is greater than configuration "convention over Configuration"

Precautions:

1) The above name must not exceed the system limit of 30 characters . The length of the variable name is limited to 29 (not including the identity character @).
2) The names of data objects and variables are written in English characters, singular, and forbidden to be named in Chinese. Never leave spaces between the characters of the object name.
3) Be careful to keep the word, and make sure that your field name does not conflict with reserved words , database systems, or common access methods
4) Maintain the consistency of field names and types, and ensure consistency when naming fields and specifying data types for them. If the data type is an integer in one table, then the other table will not become a character type.
5) Many-to-many relationship tables, ending with Mapping , such as xxmapping

2. Name length limit

　　

3, the word abbreviation

It is recommended that when the table name is more than 15 characters, the field name is more than 20 characters, you should try to rename the word abbreviation, if the name length within this, in principle, as far as possible without abbreviations to make the expression clear, the table, the final name of the field is strictly controlled within 30 characters. The word abbreviation rules are as follows:

A If you can find the abbreviation of a word in the dictionary, use this as an abbreviation, for example: Monday=mon, December=dec, can find some abbreviations of English words under this website: http://shortof.com/;

b You can delete the word vowel (except for the first letter of the word) and repeat the letter for each word to abbreviate a word. For example: current = crnt, Address = Adr, Error = Err, Average = AVG;

C For master-Slave tables, if the primary table name does not have an abbreviation and the name of the table requires an abbreviation, the first noun is abbreviated from the second word, and it is consistent with the primary table as much as possible from a table name. For example, the Enterprise Basic information table name is enterprise, the corporate litigation table enterprise_litigation can be abbreviated to ENTERPRISE_LTG, Enterprise certificate Form enterprise_certificate can be abbreviated to enterprises _crt. The final database tables and the programs generated by the database tables are arranged by name in the integrated development environment (ide,integrated development environment), so that the similar functions of the tables and class files are arranged so that the developer is easy to work with.

Iv. recommended naming rules

1. Naming methods

　　

2. About table prefixes

A system table (S_): System configuration-related basic information table. System User table (S_user), System Role Table (S_role), System menu (s_link_menu), Operation Log (s_operation_log), login log (s_login_log), System dictionary (s_dictionary), System dictionary type (S_dictionary_type), and so on.

b Dictionary table (d_): Dictionary, a dictionary table outside the system dictionary. In the "Design Specification"--"related notes"--"dictionary fields" mentioned in the definition of the dictionary table, in addition to the General dictionary table in the database, there are some common tables, such as the regional table (d_region), ICD Code (D_ICD), etc., is also a dictionary table, where the D_ The prefix is prepended to this type of dictionary table name.

C Intermediate table (R_): relationship, multi-to-many relationship intermediate table. The specific naming method is: r_ The Main Table name _ from the table name, in many-to-many relationships in fact, do not sub-master table, here we specify the core table is the main table, and the other one from the table. For example, in user role relationships, the user table (S_user) is the primary, the role (s_role) table is from, and the intermediate table is named R_user_role. When the middle table name is very long, the master-slave table name is abbreviated according to the actual situation, and the preferred abbreviation from the table name is recommended.

D Business Table (b_): A basic information table for business, core businesses. Here the business is non-system configuration business-related, such as login, registration, permissions the tables involved in the business are related to the system configuration, the prefix should be s_, not b_. For example, the online mall in the project order business involved in the table is the core business table, consultation system in the consultation sheet business involved in the table is the core business table, if the project is large, involving more business, you can continue to add a single letter after B to differentiate different business, Ba_, Bb_, bc_ ..., there is no need to correspond with an English It's just a code name, and the project team can explain it.

3. About field naming

A The primary keys in all tables are uniformly named ID, the primary key is uniformly used uuid, and the type is uniformly char (32). It is not recommended to use composite primary keys, even in the middle table of a many-to-many relationship, or to suggest a separate field for the main key, and a composite field to add a unique constraint.

b All table fields except the key, the other field names do not have to be deliberately prefixed, and do not appear in front of the field name. The foreign key here is a generalized foreign key that includes not only the foreign key field that references the primary table's primary key from the table, but also the extension field that holds the key information for the main table.

For example, the patient table (Patient), the primary key is the ID instead of the pateint_id, and the name is names rather than patient_name. But for foreign keys, such as other tables referencing the primary key of the patient table that is patient_id, the name field corresponding to the patient table is patient_name. If there is more than one foreign key (field) in a table that references (corresponds) to the same field of a table, then another identity is used.

C For dictionary fields, the code suffix followed by the coded field, and the text field with the text suffix, such as Gender_code, Gender_text.

D In this specification, all fields that represent datetime are required to have a suffix, if only the exact day is the suffix of date, and if you want to be accurate to the minute or seconds, use datetime as the suffix. A field that requires a DateTime type, as accurate as possible to a time and seconds, even if it is a field such as a birthday (birth_date), is usually stored only to date, but is recommended or datetime rather than date when selecting a field type. So the suffix here is not corresponding to the specific field type, but according to the actual business situation, the data stored in this field is accurate to date or time of the second, then the suffix corresponding to date or DateTime.

Note: The datetime is minimized by using time as a suffix, as there is also a very common meaning of times. For example, the log table has the user last logon Time field login_time, not to see the contents of the table, it is easy to interpret login_time as the number of logins.

E This specification is recommended whether to unregister, success, and so on similar Boolean fields, the name is preceded by the unified is prefix, such as whether the success (is_success), whether logoff (is_active), whether the display (Is_display) and so on.

F. Use as few storage space as possible to store data for one field, for example: you can use int instead of varchar, char, and varchar (16) instead of varchar (256);

The IP address preferably uses the int type;
Fixed-length types are best used with char, for example: ZIP code; use tinyint to avoid smallint,int;
It is best to give each field a default value, preferably not null;

4, about constraint control naming

Uniqueness and PRIMARY KEY constraints, FOREIGN KEY constraints, check constraints, NULL values constraints, default value constraints

FOREIGN KEY constraint: use FK to prefix, followed from table name and Main Table name: Fk_ from table name _ Main Table name.

Five, view character encoding

Mysql

show Collation; -- to see how the database is encoded  like ' character% '  --  Database code like'collation%'

Recommendation: Set the character set to UTF8 in the MySQL database, set the collation to Utf8_bin, and set Lower_case_table_names=1 in the database configuration file.

Vi. Paradigm of the database

The first paradigm (1NF): Field values are atomic and can no longer be divided (all relational database systems satisfy the first normal form);
For example: Last Name field, where the last name and first name are a whole, if you distinguish the first and last name then you must set up two separate fields;

Second Normal (2NF): A table must have a primary key, that is, each row of data can be uniquely differentiated;
Note: First paradigm must be satisfied first;

Third paradigm (3NF): A table cannot contain information about non-critical fields in other related tables, that is, the data table cannot have a sink field;
Note: The second paradigm must be satisfied first;

Note: Often we do not adhere to the third paradigm in the design table, because the reasonable sink residual field will give us to reduce the join query;
For example: The album table will be added to the image of the click Number field, in the album Picture table will also add the image of the click Number field;

002-Database Naming Development specification