database table name Definition specification (reprint others)

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.