Database specification (table name, field name)

Database name specification
A named entities and attributes.
1.Common words are abbreviated, in the naming process, according to the semantic abbreviation can be pieced together. Note that, because the ORCAL database will be unified into a field name in uppercase or lowercase, so the underlined
Give an example:
The definition of the acronym Sales: Sal sales,
Order: Ord orders,
Detail: Dtl details,
The sales order list name: Sal_Ord_Dtl;
2. If the table or field name is only a word, then should not use abbreviations, but with the full word.
Give an example:
The acronym Material Ma items defined,
Item name: Material, not Ma.
But the field product code is: Ma_ID; instead of Material_ID
3. All of the stored value front list with the prefix Z
Objective the value list sort in the database at the end.
4. All redundant class name (mainly the cumulative table) is preceded by the prefix X
Redundancy is to improve database efficiency, non standardized database entry field. Or table
5. After the Association class by connecting the two basic types of use underscores, named coupled with the prefix R way back in alphabetical order, make two table or table name abbreviation.
Relational table is used to save the many to many relationship.
If the associated table names longer than 10 characters, must be the original table name abbreviation. If for no other reason, suggested use abbreviations.
For example: table Object and there is a many to many relationship, save a many to many relationship table named: R_Object,
Tables Depart and Employee; there is a many to many relationship; the associated table named R_Dept_Emp
6.Each table will have an automatic ID as the main health, logic of the main health as the first set of candidate master health to define, if the database automatically generated code, unified named: ID; if it is custom logic coding is abbreviated to “ ID” method naming.
Example: the sales order number field named: Sal_Ord_ID; if there are automatic numbering a database generated, named for the: ID.
7.All the attributes and is related to the type of suffix suffix, type abbreviations defined in file type suffix abbreviation "definition", pay attention to, if need other suffix, are placed on the type suffix before.
Two the name.
The relationship between naming basically according to; if there are special circumstances, can be flexible.
[must/may/can/should][verb/verb+prep][a/many/exatly num][or a/many]Structure named
The three domain names.
The four trigger name.
Five. There are a few points about the default description
1. Master detail table strict dependencies, master list suffix Main can not write.
2. Data type is a text field, type the TX suffix can not write.
3. Field some types more obvious, can not write type suffix.
4. Very obvious, can not write
///////////////////Another article//////
Database design specifications

The database table named specification
This specification covers the table name naming conventions, index naming conventions, foreign key name specification and field name naming conventions.
Table name naming rules
The database table named is the plural form of the noun and for lowercase letters, such as cities, categories, friends etc.
If the table name consists of several words, words with underscore ("_") segmentation, such as subscribed_pois, poi_categories etc.
Table name to use the full name
Table name is limited to 30 characters in. When the table name exceeds 30 characters, can be used to reduce the length of the table name abbreviations, such as description --> DESC; information; --> info; address --> addr
Table field name naming rules
The field name to lowercase
Field names for meaningful words, or an abbreviation of the word
If the field consists of multiple words, words with underscore ("_") segmentation, such as client_id, post_code etc.
The field names are limited to 30 characters. When the field name exceeds 30 characters, can be used to reduce the length of the field name abbreviations, such as description --> DESC; information; --> info; address --> addr
The index of naming rules
The index must be in accordance with IDX_table_<</SPAN>column>_<</SPAN>column>,Among them<</SPAN>table>Is the name of the table indexing, <</SPAN>column>Field names index
The index name is limited to 30 characters in. When the index name exceeds 30 characters, can be used to reduce the length of the index name abbreviations, such as description --> DESC; information; --> info; address --> addr
Primary key, foreign key naming rules
The key to PK_<</SPAN>table>The naming, the<</SPAN>table>For the database table name
The only key to UK_<</SPAN>table>_<</SPAN>column>The naming, the<</SPAN>table>For the data block table name, <</SPAN>column>For the field name
The foreign key in accordance with FK_<</SPAN>pppp>_<</SPAN>cccc>_<</SPAN>nn>The naming, the<</SPAN>pppp>Parent table name, <</SPAN>cccc>As the table name, <</SPAN>nn>For the serial number

Some of the basic requirements of database normalization design:
One, the table should avoid nullable columns.
Though the list allows null columns, however, the empty field is a special data type. The database at the time of processing, the need for special treatment. If so, will increase the complexity of database records. When there is an empty field more table, under the same conditions, the performance of database processing will reduce many.
Solution: one is by setting the default form, to avoid empty fields. Two if a table, allowing more empty rows, close to the table all the column number 1/3. Moreover, these columns in most cases, are not essential. If the database administrator to encounter this kind of situation, suggestions and build a side table, to save these columns.
Two, the table should not have duplicate values or columns.
There is a unique identifier record should be three, table.
Four, the database object to have uniform prefix.
Five, try to store only a single entity type data.
Six, try to meet several model database.

Design specification
All of the fields in the design, in addition to the following data types timestamp, image, datetime, smalldatetime, uniqueidentifier, binary, sql_variant, binary, varbinary, with a default value must be. The default character value is an empty string value string ’ ’ numerical model; the default value for the number 0; the default logic value is a value of 0,
The values of all logic: System in 0 expressed as “ ” false; a value of 1 for “ really”.
datetime, Field of type smalldatetime does not have a default value, must be NULL.
When the field is defined as string use varchar instead of nvarchar.
The field description
Each field description in the database (Description) are as follows:
Table of each value can only be expressed once
Each row in the table should be the only mark
Should not be stored depends on other key non key information table
If the field is in fact related to other table key link and not designed for foreign key references, the need to build index.
If the field is associated with the field of other tables, need to build index.

SQL development code specification
We specified SQL statement code programmers need to follow the following rules:
All the letters a all keys must be uppercase. If a constant composed of several words, we shall use the underscore (between two words _) division. Table name, column name, name of the view or their name must be consistent and their definitions.
B notes must be standardized.

Other design techniques 
1)Avoid the use of flip flops  
Trigger function can usually be achieved by other means. May trigger when debugging the program become interference. If you really need the trigger, you'd better focus on the document.
2)Use of English (or any other language) instead of using the code to create drop-down menu, list, report the best ranked according to english. If need to code, can be attached to the user knows English in code at.   
3)Save the common information. Make a list for general database information is very useful. In the current version, database table to store recent inspection / repair, related design documents, information such as the name of the customer. This can be achieved with a simple mechanism for tracking database, when customers complain that their database does not meet the requirements of the contact you want, it is especially useful for non client / server environment.  
4)Contains the version mechanism. Introduction of version control mechanism to determine the use of the database version in the database. For a long time, the demand of the user will change. The final may modify the database structure. The version information is directly stored in the database is more convenient.  
5)Documentation. On the shortcut, all naming norms, constraints and the function to document. The table, column, trigger annotated database tools. Very useful for development, support and tracking modification. The database files, or in the database itself or a separate document. So, when after more than a year after the back to do the second versions, the opportunity to make mistakes will be greatly reduced.
6)Repeated the test, test, test. After the establishment or revision of the database, we must use the new input user data field test data. Most importantly, allows users to test with users and ensure a selected data types satisfy business requirements. Testing needs to be done before the new database into actual service.  
7)Check the design. Common technical design check database during development is through the support of application prototypes to check the database. In other words, for every kind of final expression of prototype application data, ensure that you check the data model and show how to extract data.

Posted by Tracy at November 22, 2013 - 10:54 PM