Database Naming conventions

1. Design principles

1) Standardization and normalization
Standardization of data helps eliminate data redundancy in the database. There are several forms of normalization, but the third normal form (3NF) is generally considered to be the best balance in terms of performance, scalability, and data integrity. Simply put, the table design principle for a database that adheres to the 3NF standard is that "one Fact in a place" is a table that includes only its own basic properties, which need to be decomposed when it is not a property of its own. Relationships between tables are connected by foreign keys. It has the following characteristics: There is a set of tables dedicated to the associated data connected by a key.
Example: A 3NF database that holds customers and their related orders may have two tables: Customer and order. The order table does not contain any information about the customer that the order is associated with, but the table holds a key value that points to the line in the Customer table that contains the client information.
In fact, for the sake of efficiency, it is sometimes necessary to not standardize the table.
2) data-driven
With data-driven rather than hard-coded methods, many policy changes and maintenance can be much more convenient, greatly enhancing the flexibility and scalability of the system.
For example, if the user interface is to access external data sources (files, XML documents, other databases, etc.), the corresponding connection and path information may be stored in the User interface support table. Also, if the user interface performs tasks such as workflow (sending mail, printing letterhead, modifying record status, etc.), the data that generates the workflow can also be stored in the database. Role Rights 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 various changes
When designing a database, consider which data fields may change in the future.
For example, the surname is the same (note that western surnames, such as women after marriage from the husband's surname, etc.). Therefore, when establishing the system 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, so that the change of this data entry can be tracked.


2. Database involves character specification

Use 26 English letters (case sensitive) and 0-9 of these 10 natural numbers, plus the underscore ' _ ' composition, a total of 63 characters. No other characters (except annotations) can appear.
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 the data objects and variables are all English characters, and they are 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
5) 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.


3. Database Naming conventions

database, data tables use prefixes

The official database name is made up of lowercase English and underscores, as far as possible the application or system is used. For example:

Web_19floor_net
Web_car

The backup database name consists of the official library name plus the backup time, such as:

web_19floor_net_20070403
web_car_20070403

4. database table Naming conventions

The data table name is made up of lowercase English and underscores to indicate that the application or system is in use.
The data table of the relevant application uses the same prefix, such as the table of the forum uses the cdb_ prefix, the blog's data table uses the Supe_ prefix, the prefix name generally does not exceed 5 words
Like what:

Web_user
Web_group
Supe_userspace

The Backup data table name is made up of the official table name plus the backup time, such as:

web_user_20070403
web_group_20070403
supe_userspace_20070403

5. Field Naming conventions

Field names are completed with a word combination, the first letter is lowercase, and the first letter of the following word is capitalized, preferably with a table name prefix.
Fields such as the Web_user table:

Userid
UserName
UserPassword

The associated field between the table and table is a uniform name.
As the UserID in the Web_user table corresponds to the UserID in the Web_group table

6. Field type specification
Rule: Store data in one field with as little storage space as possible.
For example, you can use int without char or varchar.
You can use tinyint without int.
You can use varchar (20) without varchar (255)
Timestamp field as far as possible with the int type, such as created: representing the number of int seconds starting from ' 1970-01-01 08:00:00 ', using the past style of English words; gmtcreated: Time for datetime type, i.e. ' 1980-01-01 00:00:00 ' time string, the corresponding type in Java is timestamp

7. Database Design Documentation Specifications

All database designs are written as documents, and the documents are expressed in a modular form. The approximate format is as follows:

‘-------------------------------------------
' Table name: Web_user
' Aeolus (silly fish)
' Date: 2007-04-11
' Version: 1.0
' Description: Save user profile
' Specific content:
' UserID int, automatic incremental user code
' UserName char (12) User name
‘ ......
‘--------------------------------------------


8. Principles of Index use:

1) The logical primary key uses a unique group index, with a unique non-group index on the System key (as a stored procedure), and a non-group index on any foreign key column. Consider how large the database is, how the table is accessed, and whether these accesses are primarily used for reading and writing.
2) Most databases index the auto-created primary key fields, but do not forget the index foreign keys, which are also frequently used keys, such as running a query to display the main table and all the associated tables of a record is available.
3) Do not index fields such as Blob/text, do not index large fields (there are many characters), this will make the index occupy too much storage space.
4) do not index small tables that are commonly used
Do not set any keys for small data tables, even if they are frequently inserted and deleted. Index maintenance on these insert and delete operations can be more time consuming than scanning the table space.

9.SQL Statement Specification

All SQL keywords are capitalized, such as Select,update,from,order,by, and all table and library names are included
Such as:
SELECT COUNT (*) from ' cdb_members ' WHERE ' userName ' = ' aeolus ';
10. Other Design techniques
1) Avoid using triggers
The functionality of triggers can often be implemented in other ways. Triggers can become interference when you debug a program. If you do need to use triggers, you'd better focus on documenting them.
2) use common English (or any other language) instead of coding or pinyin acronyms
It is best to sort by English names when creating drop-down menus, lists, and reports. If you need to encode or pinyin initials, you can attach the user-known English.
3) Save Common Information
It is useful to have a table dedicated to general database information. In this table, the current version of the database, the most recent check/fix (for access), the name of the associated design document, the customer, and so on. This enables a simple mechanism to trace the database, This is especially useful for non-client/server environments when customers complain that their databases are not meeting the desired requirements. 
4) contains the version mechanism  
A version control mechanism is introduced in the database to determine the version of the database in use. The user's needs will always change as time goes on. The database structure may eventually be modified. It is more convenient to store the version information directly in the database.  
5) Prepare the document  
for all shortcuts, Naming conventions, restrictions, and functions are documented.  
uses database Tools annotated with tables, columns, triggers, and so on. Useful for developing, supporting, and tracking changes.  
documents the database, either internally or independently of the database itself. This way, when it takes more than a year to go back to the 2nd version, the chances of making a mistake are greatly reduced.  
6) test, test, repeat Test  
After the database is created or revised, the data fields must be tested with the user's newly entered data. Most importantly, let the user test and work with the user to ensure that the selected data type meets the business requirements. Testing needs to be done before the new database is put into service.  
7) Check design  
A common technique for checking database design during development is to check the database through its supported application prototypes. In other words, the prototype application for each of the final expression data ensures that you examine the data model and see how the data is fetched.  

Database Naming conventions