database table name Definition specification (reprint others)

This article only for backup purposes, the original address: http://blog.csdn.net/u013628152/article/details/43229155

One: Table name definition specification:
(1) The name of the database table is the plural form of the noun and all lowercase, such as cities, categories, friends, etc.

This is not a mandatory rule, you can look at personal habits, unity is good.

(2) If the table name consists of several words, the words are separated by an underscore ("_"), such as subscribed_pois,poi_categories, etc.

(3) The name of the table is as full as possible

(4) The table name is limited to 30 characters characters. Abbreviations can be used to reduce the length of a table name when the full name of the table exceeds 30 characters, such as description-----desc;information---info;address addr, etc.

(5) Homogeneous database objects must have a uniform prefix name
A more complex system, database tables are often many, to quickly locate their own needs of the table, not too easy. Therefore, you can add a uniform prefix to the table in the same module.
For example, the Rights management related tables can be uniformly plus a prefix p_. User table p_user; character table p_role; Organization table p_orgnization;
Permission table P_power;
Intermediate table plus prefix rel_




Two: Field name definition specification

(1) All database fields are in lowercase English words

(2) field length should not be too long, as concise and clear
Field names are limited to 30 characters characters. When the field name exceeds 30 characters, the abbreviation can be used to reduce the length of the field name.

such as description---desc;information---info;address, addr, etc.

(3) If the name of a table or field is only one word, it is recommended not to use abbreviations, but to use complete words.

(4) If the table or field is composed of multiple words, the words are separated by "_".

The first letter of the entity attribute is lowercase, and if there are multiple words, the first letter of the other word is capitalized.
Like what:
/**
* Creation Time
*/
@Column (name = "Creation_time")
Private long creationtime;

(5) Commonly used fields such as name, not directly with the name, preferably defined as xx_name.

To prevent associated queries, the two-table field names are the same and inconvenient to handle. may also need to alias as

(6) field names try to avoid Chinese pinyin

(7) Avoid keywords in the field as much as possible

(8) A nullable column should be avoided in the table.
Although empty columns are allowed in the table, empty fields are a special type of data.
When the database is processed, special processing is required. In this case, the complexity of database processing records is increased.
When there are more empty fields in the table, the performance of the database processing is much lower under the same conditions.
Workaround: One is to avoid the generation of empty fields by setting default values.


(8) fields that cannot be empty are best added with default values
All fields at design time, except for the following data types Timestamp, datetime, must have a default value.
The default value of the character type is an empty character value string ';
The default value of the numeric type is the value 0;
The default value for the Boolean is 0, the value 0 in all logical types in the system is false, and the value 1 is true.
A field of datetime, smalldatetime type has no default value and must be null.

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------

Attach the Data Warehouse specification developed by the predecessor:

I. Data Warehouse hierarchy Specification Ii. Basic Hierarchical structure

The content stored in each information model is described below

I. ODS: Data preparation layer, table structure and business library structure are consistent. Modeling with 3NF range.

DW: Detail data layer containing fact and dimension table data, fine-grained data split modeling by dimension and fact

Third, DM: Data mart layer, contains coarse-grained data summary.

RP: For the report application layer, including front end reports, analysis charts, KPIs, dashboards, topics and other analysis, for the end-result users

Five, ODI ODI database use

Vi. Use of Saiku Saiku

Vii. temporary tables used in Odi_work:odi work tasks

Physical table structure prefixes for each layer

Level	Hierarchy name	Physical table prefixes
Ods	Detail data Layer	T_ods_
Dw	Dimension of	t_dim_
	Fact	T_fact_
Dm	Data mart Layer	T_dm_
Rp	Data Application Layer	T_rp_

Database object naming specification table space objects

Level	Table Space Name	Data files
Ods	Tbs_ods	TBS_ODS01.DBF,TBS_ODS02.DBF,...
	Idx_tbs_ods	IDX_TBS_ODS01.DBF ...
Dw	Tbs_dw	TBS_DW01.DBF,...
	Idx_tbs_dw	IDX_TBS_DW01.DBF,...
Dm	Tbs_dm	TBS_DM01.DBF,...
	Idx_tbs_dm	IDX_TBS_DM01.DBF,...
Rp	Tbs_rp	TBS_RP01.DBF,...
	Idx_tbs_rp	IDX_TBS_RP01.DBF,...
Odi	Tbs_odi	Tbs_odi.dbf
Odi_work	Tbs_odiwork	TBS_ODIWORK01.DBF,...
Saiku	Tbs_saiku	TBS_SAIKU01.DBF,...

database table Naming Conventions

Level	Hierarchy name		Physical table prefixes
Ods	Detail data Layer		T_ods_
Dw	Dimension of		t_dw_dim_
	Fact	Sales topics	T_dw_fact_sales
		Customer Topics	T_dw_fact_cust
		Financial topics	T_dw_fact_fin
		Temp table	Temp_
Dm	Data mart Layer	Official table	T_dm_
		Temp table	Temp_
Rp	Data Application Layer	Official table	T_rp_
		Temp table	Temp_

Database table Index

Named with idx+ table name + example: idx_t_ods_busi_user_user_id; If the table name is too long, you can use the abbreviated form

database table Key Values

The primary key is named with the pk+ table name + example: Pk_ t_ods_busi_user_user_id; If the table name is too long you can use the abbreviated form

Foreign key naming to fk+ table name + example: Fk_ t_ods_busi_user_user_id; If the table name is too long you can use the abbreviation form

database field Naming conventions

database field names contain words to select one or more English words that summarize the contents of a table, and use between multiple words

Underline segmentation, if the word is too long can be used abbreviated form.

Some examples of basic field names are:

User ID user_id

User name user_name

Each field must have a comment and be generated when the SQL script is generated, and a note must be created when the table is created.

Maintain the consistency of field names and types, and the same field names must maintain the same data type in different tables. Data type

Length in the definition should be slightly larger than the current standard length, with space to exchange for future changes caused inconvenience.

Database stored Procedure Specification

(1) Stored procedure naming rules: p_ target table.

(2) The stored procedure requires a comment that lists the creator, creates the purpose, and creates the time.

(3) Stored Procedure Modification specification

Changes should be made with a clear revision of the person, modify the date, change the reason and modify the content.

Database function Naming conventions

function naming rules f_ functions, such as F_ip_tranc.

Database View Naming conventions

View naming rules v_[hierarchy]_ table name (function),

database table name Definition specification (reprint others)