The application of application and database development in the past time, the norms do not have a truly specification, and everyone can share it. Error, opinion, advice, please contact me!
Database coding specification
V1.2
2004
November
12th
category
file name:
Database coding specification
version number:
V1.2
Version date:
2004-11-12
Author:
Nick
date:
2004-11-12
table of Contents
1 purpose. 2
2 range. 3
3 terminology. 3
4 Design summary. 3
4.1 Design Environment. 3
4.2 Designing Tools. 4
4.3 Design principles. 4
4.4 Update for design. 4
5 Name Overall Principles. 4
6 Name Specification (Logic Object). 5
6.1 Database Structure Named. 5
6.2 Database object named. 6
7 script comments. 8
7.1 Store procedure or trigger. 8
7.2 Custom Function. 9
8 database operation principles. 10
8.1 Establish, delete, modify the library table operation. 10
8.2 Add, delete, modify table data. 10
9 Common field name. 10
9.1 System Information Common fields. 10
9.2 Customer personal information common field. 10
In order to unify the design of the company's software development on naming specifications and specific work during the design of database design, this specification is specially specialized in communication and maintenance.
2. area
This specification applies to all personnel of the development group, acting on database design and maintenance stages of software project development.
3 terminology
Ø Database object: In database software development, the objects involved in the database server include objects of physical structures and logical structures.
Ø Physical structural object: means the device management element, including the name, size, directory plan, the server where the data file and the transaction log file, the server calculates the extreme name, mirror, etc., should have a specific configuration plan. Generalize the management procedures for database server physical devices, planning throughout the project / product.
Ø Logical structure object: refers to the management element of the database object, including database name, table space, table, field / domain, view, index, trigger, stored procedure, function, data type, database security related design, database configuration. Design and other characteristics of other characteristics in the database, etc.
4 design summary
4.1 Design Environment
a) Oracle 9i
database
Oracle 9i
operating system
SUSE Linux 7.1 or above, display graphical operation interface; Redhat 9 or above
CPU
P iii
1.7G
the above
RAM
512M
the above
hard disk space
The remaining space of 5GB, if you want to create more, large databases need more hard disk space
b) MS SQL Server 2000
database
SQL Server 2000 Enterprise Edition
Play SP3 or more patch and safety patches
operating system
Windows 2000 Server or
Windows 2000 Advanced Server
Hit SP4 or more patch
CPU
P iii
1G
the above
RAM
256M
the above
hard disk space
1G
The above remaining space To create more, large databases need more hard disk space
4.2 Design Tools a) Use PowerDesigner as a design tool for the database, requiring a detailed description for the main fields.
b) Customize the Word format report through PowerDesigner, and export the Word document, saved as a data dictionary, and the format can be referred to Attachment 1. (PowerDesigner V10 has the function of customizing the exported Word format report)
c) Write the database built database, build a database object, initialize the data script file
4.3 Design principle
a) use multi-data files
b) Prohibition of excessive data files, UNIX systems are not more than 2GB, the Window system does not exceed 500MB
c) The index must be built in the index table space in the Oracle database.
d) Basic information table Allocate enough storage space when establishing, prohibiting its automatic expansion
e) Big text: BLOB column must have a table independently, this table is only ID and blob (or big text) columns
4.4 Update for Design
a) Maintain by a database administrator or a member of the designated project group in the design phase.
b) The running phase is maintained by the database administrator.
c) If you modify the table structure, you should modify it in PowerDesigner, then re-export the Word document, and finally modify it in the database. If you modify the database dictionary table, you must be done by the database administrator.
d) Generate SQL code using PowerDesigner, disable database operations from PowerDesigner directly
e) Modify the database to via SQL, prohibit other ways to modify data
f) SQL to modify the database to save the checkup
5 naming overall principles
Ø Set prefix us with lowercase letters
Ø Logo name Name all lowercase
Ø The entire naming full length must not exceed 30 letters
Ø All letters and underlined '_', can not use Chinese and other characters, there is a special case to allow the end number number. For example: t_finace1, t_finace2 ...
Ø Name name comes from business, all in English words
Ø Excessive English words can adopt common abbreviations, try to express the meaning of business
Ø If you need more than two English words, you need to use the underlined '_' connection between words.
Ø Name is all consisting of nouns, the noun is named from a wide range to small range
Ø Complete the name of a function, such as functions and procedures, name this
6 Name Specification (Logic Object)
6.1 Database Structure Name
a) Database named
The database's naming requires the English letters associated with the database meaning, and takes "DB_",
That is, DB_ .
For example: The CHINA CARE database is named DB_CCNET;
The customer's data database is named DB_CUSTOMER_INFO.
b) Database log design naming
Name the database log named _ .log format. Among them, is a meaningful database log name. For example: DB_CCNET_LOGREDO.LOG
c) Database Configuration Design Named
The database configuration design is saved in file form, and its content is the specific value of the configuration item for a particular database.
Named of the database profile: _ _cfg.ini format named.
Among them, the database type is discomfort see Annex 2 "Database Type Short", and CFQ indicates that the file is a database configuration file.
For example: ORA_ CCNET_CFG.INI
d) Database Copy and Storage Design Name
The database replication and storage design is saved in file form, and its content is a specific detail of the replication policy between a particular database.
Database replication and storage design file named: _ _Rep.txt format named.
Among them, the database type is short. See Annex 2 "Database Type Short", REP indicates that the file is a database replication and storage file.
e) Database connection design naming
The database connection design is saved in the file, and its content is the specific details of the connection design between a specific distributed database.
Named of database connection design files: _ _DBL. SQL Format Name. Among them, the database type is short. See Annex 2 "Database Type Short", DBL indicates that the file is a database connection design file.
f) Table space, data file naming (mainly for Oracle)
Index Table Space:
Table Space Name Format: TS _i
Database file naming format: TS _i [n] .dbf
Temporary table space:
Table space Name format: TS _T
Database file naming format: TS _T [n] .dbf
Rolling the scroll:
Table space Name format: TS _R
Database file naming format: TS _R [n] .dbf
Data table space:
Table space Name format: TS _D
Database file naming format: TS _D [n] .dbf
Note: The name of the table space does not exceed 8 digits, n can be 00-99 or 0-9, determined according to the amount of system data.
6.2 Database object Name
a) table
The name of the table must begin with "TABLE abbreviation), the format is: T_ [System Identity] _ _
.
Among them, [] indicates options, increasing according to the actual situation;
The English letters associated with the mean, such as t_customers.
The data sheet is roughly divided into: business data sheet, basic coding table, auxiliary coding table, system information table, cumulative data sheet, settlement data sheet, decision data table
Basic coding table with base sign
Cumulative data sheet with count logo
System information table with info logo ...
For example: t_trade_base_trade_code, t_trade_info_help ...
b) field / domain
Name according to business requirements, no need to set a fixed prefix.
c) index
The naming format of the index established for one or more fields in the database table should begin with "idx_", the index column name is used _ separated, which is IDX_COLUMNNAME1_COLUMNNAME2_ ... where columnname1 is in the database table (first) index field The name or name is short-written; columnname2 is the name or name or name of the index field in the database table; the total length of the index name must meet the database.
Example: IDX_CERT_NUMBER (Indicates to create an index on the field CERT_NUMBER)
d) view
The naming of the view must begin with "V _" (View Abbreviation), the format is: V_ _ [System Identification] _ .
Among them, the view type refers to the "Classification Description of the Table"; [System Identity _] is optional, an increase in the situation; English letters associated with the view meaning.
Example: v_user_detail_info
e) stored procedure
The name of the stored procedure must comply with the SP_ [System ID] _ format.
Where SP is a stored procedure; [System Identification] is optional, an increase in the case; is an English letter associated with the stored procedure, for example: USP_QUERY_WRITE_TO_DISK.
Example: sp_CHECK_USER_AUTH (named in a passenger approach)
f) trigger
The naming of the trigger must comply with the TR_
_ format.
Among them, TR representation is a trigger; is an English letter associated with the trigger meaning.
Example: TR_USER_INFO_IU (inserted to the USER_INFO table, updated trigger)
g) function
The naming of the function must comply with the FN_ [System Identification] _ format.
Wherein, the FN is a function, [System Identification] is optional, depending on the situation; is an English letter associated with the function meaning.
Example: fn_create_id (named in a passenger approach)
h) Custom data type
Named format of custom data types: UD_ _
i) Default (default)
Default's name format is generally: DF_ For unbound default, you can take the system default name, see page 8.1 Instance Binding specific fields.
Example: DF_BEGIN_DATE Default Start Date '20030101'
if exists (SELECT * from sysobjects where type = 'd' and name = 'df_begin_date')
DROP DEFAULT DBO.DF_BEGIN_DATE
Go
Create Default DF_BEGIN_DATE AS '20030101'
Go
j) Check, Constraint (constraint)
The constraint is usually: CK_
_ ; Some constraints can be placed directly in the statement that generates the table.
Example: CK_FLAG See Section 8.1 Instance, Constraint Field Flag can only take characters '0' to '9':
ConsTRAINT CK_FLAG CHECK (Flag Between "
0
'
And '
9
'
)
K) Rule (rule)
The naming format of the rule is generally: rl_ For non-binding rules (constraints), see the 10-point design instance binding specific fields.
Example: rl_not_zero (define a rule that does not equal 0)
IF exists (SELECT * from sysobjects where type = 'r' and name = 'rl_not_zero')
Drop rule dbo.rl_not_zero
Go
Create rule rl_not_zero as @i <> 0
Go
l) primary key
The naming format of the primary key is the PK_
_ .
Example: PK_USER_INFO_USERID (Table USER_INFO Create a primary key with field userid)
m) foreign key
The name format of the foreign key is fk_
_ _ .
Example: fk_user_info_DEPARTMENT_DEPTID (create foreign key on the table user_info field department_ID, refer to the main table department)
N) synonym (Oracle)
The naming format of synonyms is: SY_
Example: SY_USER_INFO (public meaning of the table USER_INFO belonging to ownership)
7 script comments
7.1 Storage Process or Trigger
a) Each stored procedure or trigger must write a comment in the forefront, the comment is as follows
/ *
Writer:
Create Date:
VER:
Depiction:
REMARK:
* /
In addition, important variables declared in the process must be annotated, for example:
b) If you only have some modifications to the stored procedure or trigger, you must add the following notes:
/ * REWRITER: ADD (REWRITER): DATE: Start1:
Modify description:
* /
/ * Original code content * / (modified)
/ * REWRITER: DATE: end1: * /
/ * REWRITER: Add (REWRITER): Date: Start2: * /
New code content
/ * REWRITER: DATE: end2: * / c) If there is a large modification for stored procedures or triggers, you can increase the annotation of the modified content.
/ * Log ID:
Rewriter:
REWRITE DATE:
Depiction:
* /
7.2 Custom Function
a) Each custom function must write a comment in front of it, the comment is as follows
/ *
Function name: XXXX
Depiction: Description of this function>
PARAM (A, B)
A function or description ....
B function or description
Output: x x = 0 means ..... x = 1 means ......
Writer:
Create Date:
VER:
REMARK:
* /
In addition, the important variables declared in the function should be annotated, for example:
b) If only partially modify the function, you must add the following notes:
/ * REWRITER: ADD (REWRITER): DATE: Start1:
Modify description:
* /
/ * Original code content * / (modified)
/ * REWRITER: DATE: end1: * /
/ * REWRITER: Add (REWRITER): Date: Start2: * /
New code content
/ * REWRITER: DATE: end2: * /
c) If there is a large modification to the function, you can increase the comment for modifying the content.
/ * Log ID:
Rewriter:
REWRITE DATE:
Depiction:
* /
8 database operation principle
8.1 Establish, delete, and modify library table operations
Ø In the development environment, you can modify your own library table, delete the operation;, you need to retain the corresponding schedule statement and instructions, and build a table with the table.
8.2 Adding, deleting, modifying table data Ø In the development environment, the developer's development module is alone using the library table that can be used in the form of data;
Ø The library table associated with other modules should obtain the operation of the developer of other modules to make an operation;
Ø Information table of the system, the modification of the Dictionary table should be submitted to the administrator of the database, and the operation is performed by the administrator of the database.
9 common field name
9.1 System Information Common Field
Field Name
Field code
Oracle
SQLServer
value
Description
User ID
User_id
Char (8)
Char (8)
Used to log in to the application system, fill in the number. Generally, the primary key is to be filled with the length.
user name
User_name
VARCHAR2 (20)
VARCHAR (20)
User ID corresponding name
Group identification
GRP_ID
Char (4)
Char (4)
The group identifier to the user belongs, fill in the number. Generally, the primary key is to be filled with the length.
Group name
GRP_NAME
Varchar2 (50)
VARCHAR (50)
Group identification corresponding name
Order rights logo
GRP_AUTH_ID
Char (2)
Char (2)
From low to high, from 00 to 99. To fill the length
Order rights name
GRP_AUTH_NAME
Varchar2 (50)
VARCHAR (50)
Group permission logo corresponding name
password
Password
VARCHAR2 (30)
VARCHAR (30)
9.2 Customer personal information common field
Field Name
Field code
Oracle
SQLServer
value
Description
Customer logo
Customer_ID
char (?)
char (?)
The customer identifier used by the business system, generally the primary key, does not need to grow long types. To fill the length.
Customer Name
Customer_name
Varchar2 (50)
VARCHAR (50)
Department identification
DEPT_ID
char (?)
char (?)
Generally, the primary key is generally used. To fill the length.
Department name
DEPT_NAME
Varchar2 (50)
VARCHAR (50)
gender
SEX
char (1)
char (1)
F / m
The following is not identified, fill in the specific value directly in the field.
(Note: The above statement assumes that the main table name is DEPAERMENT. Create other foreign keys according to this statement)
/ * ================================================================================================================================================================ =============== * // * Binding rules rl_not_zero to the list of User_Vases Hight * /
