Database coding specifications

Database coding specifications

V1.2

November 12, 2004

Category

File Name:
Database coding specifications

Version:
V1.2

Version Date:
2004-11-12

Author:
Nick

Date:
2004-11-12

Contents

1 objective. 2

Range 2. 3

3 glossary 3

4. design overview 3

4.1 design environment 3

4.2 design and use tools 4

4.3 Design Principles 4

4.4 design update. 4

5 General naming principles 4

6. Naming Conventions (logical objects). 5

6.1 database structure name. 5

6.2 database object naming. 6

7 script comment. 8

7.1 stored procedure or trigger. 8

7.2 user-defined functions. 9

8 database operation principles 10

8.1 create, delete, and modify database tables 10

8.2 add, delete, and modify table data. 10

9 common field naming. 10

9.1 common system information fields 10

9.2 common customer personal information fields 10

9.3 common pop-up information fields. 11

9.4 common business information fields 11

10 design instances (SQL SERVER 2000). 12

10.1 tables, primary keys, foreign keys, indexes, rules, and constraints. 12

Appendix 1: database data dictionary. 14

Appendix 2: abbreviated database type. 15

1 Purpose

This specification is formulated to unify naming rules for database design and programming rules for specific work during the design process of software development.

Range 2

This specification applies to all developers in the database design and maintenance phases of software project development.

3 Terms

Database objects: In database software development, The objects involved in the database server include objects in the physical and logical structures.

Ø physical structure object: refers to the device management elements, including the names, sizes, directory plans, computing pole names, and images of data files and transaction log files, specific configuration plans should be available. Generally, management procedures for physical equipment of database servers are planned throughout the project/product outline design phase.

Logical Structure objects: management elements of database objects, including Database Name, tablespace, table, field/field, view, index, trigger, stored procedure, function, data type, database security-related design, database configuration-related design, and database design related to other features processing.

4. design overview

4.1 design environment
A) ORACLE 9I

Database
ORACLE 9i

Operating System
Suse linux 7.1 or a later version displays the graphic operation interface; RedHat 9 or a later version

CPU
P iii, 1.7 GB or above

Memory
Above MB

Hard Disk Space
More than 5 GB of space remaining. More hard disk space is required for building a large database.

Ms SQL SERVER 2000

Database
SQL SERVER 2000 Enterprise Edition

Patch and security patch above sp3

Operating System
WINDOWS 2000 SERVER or

WINDOWS 2000 Advanced Server

Patch sp4 or above

CPU
P iii above 1 GB

Memory
M or above

Hard Disk Space
More than 1 GB of remaining space is required. More hard disk space is required for building large databases.

4.2 design and use tools
A) using PowerDesigner as a database design tool requires detailed description of the main fields.

B) Use PowerDesigner to customize the word format report and export the word document to save it as a data dictionary. For the format, see Appendix 1. (PowerDesigner v10 provides the ability to customize and export word format reports)

C) Write database creation, database object creation, and data initialization script files

4.3 Design Principles
A) use multiple data files

B) Prohibit the use of large data files. The unix system should not exceed 2 GB, and the window system should not exceed 500 MB.

C) The index must be created in the index tablespace in the oracle database.

D. allocate enough storage space when the basic information table is created and disable its automatic scaling function.

E) The text column and blob column must have an independent table. This table only has ID and blob (or large text) columns.

4.4 design update
A) during the design phase, the database administrator or a member of a specified project team performs maintenance.

B) In the running stage, maintained by the database administrator.

C) if the table structure is modified, modify the table structure in powerdesigner, re-export the Word documents, and modify the table structure in the database. The database administrator must modify the database dictionary table.

D) Use powerdesigner to generate SQL code. Do not directly connect to the database by powerdesigner.

E) You must use SQL to modify the database. do not modify the data in other ways.

F) modify the SQL statement of the database and add instructions for future reference.

5 General naming principles

The specified prefix must contain lowercase letters.

Ø name all lowercase letters

The name must contain no more than 30 letters.

All characters must contain letters and underscores '_'. Chinese characters and other characters are not allowed. In special cases, end numbers are allowed. Example: t_finace1, t_finace2...

Ø the name is from the business and all English words are used.

Ø if the English word is too long, General abbreviations can be used to express the meaning of the business as much as possible.

If you need more than two English words as the logo name, use the underscore '_' to connect words.

All the names are composed of nouns, And the names are sorted from a wide range to a small range.

Ø name a function, such as a function or process, in the form of a Dynamic Object

6. Naming Convention (logical object)

6.1 database structure naming
A) database naming

The name of a database must contain English letters associated with the meaning of the database and start with "DB,

Db _.

For example, the name of the china care Database is db_ccnet;

The customer data database is named db_Customer_Info.

B) Database Log design name

Database logs are named in the format of _. log. Here, it is a meaningful Database Log name.

Example: db_ccnet_logredo.log

C) Database Configuration Design name

The database configuration design scheme is saved in the form of files. Its content is the specific value of the configuration items of a specific database.

The database configuration file is named in the format of _ cfg. ini.

For a brief description of the Database Type, see Appendix 2 database type abbreviation. cfq indicates that the file is a database configuration file.

Example: ORA _ ccnet_cfg.ini

D) database replication and storage design naming

The database replication and storage design scheme is saved in the form of files. Its content is specific details about the replication policies between specific databases.

The database copy and storage design file is named in the format of __rep.txt.

For a brief description of the Database Type, see Appendix 2 database type abbreviation. rep indicates that the file is a copy and storage file of the database.

E) database connection design name

The database connection design scheme is saved in the form of files. Its content is specific details about the connection design between specific distributed databases.

The database connection design file is named in the format of __dbl. SQL. For a brief description of the Database Type, see Appendix 2 database type shorthand. dbl indicates that this file is a database connection design file.

F) Names of tablespaces and data files (mainly for Oracle)

Index tablespace:

Table space naming format: ts_ I

Database File naming format: ts_ I [N]. DBF

Temporary tablespace:

Table space naming format: ts_t

Database File naming format: ts_t [N]. DBF

Roll back the tablespace:

Table space naming format: ts_r

Database File naming format: ts_r [N]. DBF

Data Table space:

Table space naming format: ts_d

Database File naming format: ts_d [N]. DBF

Note: The tablespace name cannot exceed 8 bits. N can be-99 or 0-9, which is determined based on the system data volume.

6.2 database object naming
A) Table

The table name must start with "T _" (Table abbreviation). Format: T _ [System ID] _.

[] Indicates the option, which is added based on the actual situation. The English letters associated with the table meaning are required, such as t_customers.

Data Tables are roughly divided into business data tables, basic encoding tables, auxiliary encoding tables, system information tables, accumulative data tables, settlement data tables, and decision data tables;

Base flag for the basic encoding table

Count flag for accumulative Data Tables

The system information table is marked with info...

Example: t_trade_base_trade_code, t_trade_info_help...

B) field/Field

Naming based on business requirements without a fixed prefix.

C) Index

The naming format of indexes created for one or more fields in the database table should start with "idx _". The index column names are separated by _, that is, idx_columnname1_columnname2 _...

ColumnName1 is the name or short name of the index field in the database table (first); ColumnName2 is the name or short name of the index field in the database table (second; the total length of the index name must comply with database requirements.

For example, idx_cert_number (indicates creating an index on the cert_number field)

D) view

The View name must start with "v _" (View abbreviation). Format: v _ [System ID] _.

For more information about view types, see "Table classification description". [system identifier _] is optional and increases based on the situation. It should be associated with English letters of view meaning.

Example: v_user_detail_info

E) Stored Procedure

The name of the stored procedure must comply with the sp _ [system identifier] _ format.

Sp indicates a stored procedure. The [System ID] is optional and increases based on the situation. It is an English letter associated with the meaning of the stored procedure, for example, USP_Query_Write_to_Disk.

For example, sp_check_user_auth (named as a dynamic object)

F) Trigger

The trigger name must comply with tr __Format.

Tr indicates a trigger;It is an English letter associated with the trigger meaning.

Example: tr_user_info_iu (a trigger for inserting and updating the user_info table)

G) Function

The function name must comply with the fn _ [system identifier] _ format.

Fn indicates a function. The [system identifier] is optional and depends on the situation. It is an English letter associated with the function meaning.

Example: fn_create_id (named as a dynamic object)

H) Custom Data Type

The name format of the custom data type is: UD __

I) default (default)

The default naming format is generally: DF _ For non-bound default names that can be used by the system by default, see binding specific fields to instances at point 8.1.

For example, df_begin_date indicates the default start date '20140901'

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 '20140901'

Go

J) Check and constraint (constraints)

The naming format of the constraint is generally: CK __ ; Some constraints can be directly placed in the statements that generate the table.

For example, for ck_flag, see the instance at. The constraint field flag can only take the 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), you can use the default name of the system. For details, see design instance binding specific fields.

Example: rl_not_zero (defines a rule not equal to 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 primary key name format is pK __.

For example, pk_user_info_userid (Table user_info creates a primary key with the field userid)

M) foreign key

The name format of the foreign key is FK ___.

Example: fk_user_info_department_deptid (create a foreign key in the department_id field of user_info, refer to Department of the master table)

N) Synonyms (Oracle)

Synonym naming format: Sy _

Example: sy_user_info (Public synonym for user_info table with ownership of Ben)

7. Script Annotation

7.1 stored procedure or trigger
A) each stored procedure or trigger must be annotated at the very beginning. The annotations are as follows:

/*

Writer:

Create Date:

Ver:

Depiction:

Remark:

*/

In addition, important variables declared during the process must be annotated, for example:

@ Iactionflag Int = 0/* 0 => checkout, 1 => getlatest, 2 => undocheckout */

B) Add the following notes if you only partially modify the stored procedure or trigger:

/* Rewriter: add (rewriter): Date: start1:

Description:

*/

/* Original code content */(when modified)

/* Rewriter: date: end1 :*/

/* Rewriter: add (rewriter): date: start2 :*/

New Code content

/* Rewriter: date: end2 :*/

C) if there is a large modification to the stored procedure or trigger, you can add a comment on the modification content.

/* Log Id:

Rewriter:

Rewrite date:

Depiction:

*/

7.2 User-Defined Functions
A) each user-defined function must be annotated before it. The comments are as follows:

/*

Function name: xxxx

Depiction:

Param (a, B)

A function or description ....

B function or description

Output: x = 0 indicates... x = 1 indicates ......

Writer:

Create date:

Ver:

Remark:

*/

In addition, important variables declared in functions must be annotated, for example:

@ IActionFlag int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */

B) Add the following notes if you only want to partially modify the function:

/* Rewriter: add (rewriter): date: start1:

Description:

*/

/* Original code content */(when modified)

/* Rewriter: date: end1 :*/

/* Rewriter: add (rewriter): date: start2 :*/

New Code content

/* Rewriter: date: end2 :*/

C) if there is a major modification to the function, you can add comments to the modification content.

/* Log id:

Rewriter:

Rewrite date:

Depiction:

*/

8 database operation principles

8.1 create, delete, and modify database tables
Ø in the development environment, you can modify or delete your own database and tables at will. However, you must retain the corresponding table creation statements and instructions, which are consistent with the time when the table creator created the table.

8.2 add, delete, and modify table data
Ø in the development environment, the database tables used by the development module by the developer are free to operate the table data;

Ø the database tables associated with other modules should be approved by the developers of other modules before performing the operation;

Ø modification of system information tables and dictionary tables should be requested by the database administrator to perform operations.

9. Name common fields

9.1 common system information fields
Field name
Field code
ORACLE
SQLSERVER
Value
Description

User ID
User_id
Char (8)
Char (8)

Used to log on to the application system. Enter a number. It is generally used as the primary key, and the length must be filled.

User Name
User_name
Varchar2 (20)
Varchar (20)

User ID

Group ID
Grp_id
Char (4)
Char (4)

ID of the group to which the user belongs. Enter a number. It is generally used as the primary key, and the length must be filled.

Group Name
Grp_name
Varchar2 (50)
Varchar (50)

Group ID name

Group permission ID
Grp_auth_id
Char (2)
Char (2)

From low to high, from 00 to 99. Length to be filled

Group permission name
Grp_auth_name
Varchar2 (50)
Varchar (50)

Name of the Group permission ID

Password
Password
Varchar2 (30)
Varchar (30)

9.2 common customer personal information fields
Field name
Field code
ORACLE
SQLSERVER
Value
Description

Customer ID
Customer_id
Char (?)
Char (?)

The customer ID used by the business system. Generally, the primary key does not need to be a variable length type. Length to be filled.

CUSTOMER NAME
Customer_name
Varchar2 (50)
Varchar (50)

Department ID
Dept_id
Char (?)
Char (?)

Generally, the primary key does not need to be of variable length type. Length to be filled.

Department name
Dept_name
Varchar2 (50)
Varchar (50)

Gender
Sex
Char (1)
Char (1)
F/M
If the following is not identified, enter the specific value in the field

Marital Status
Marriagestatus
Varchar2 (50)
Varchar (50)

Date of birth
Birthday
Date
Smalldatetime

Height
Hight
Number (5, 2)
Dec (5, 2)

Unit: centimeter

Weight
Weight
Number (5, 2)
Dec (5, 2)

Unit: kg

Ethnicity
Nationality
Varchar2 (20)
Varchar (20)

Contact number
Phone
Varchar2 (50)
Varchar (50)

Home phone number
Home_phone
Varchar2 (50)
Varchar (50)

Office phone number 1
Office_phone1
Varchar2 (50)
Varchar (50)

Office phone number 2
Office_phone2
Varchar2 (50)
Varchar (50)

Area code
Zone
Varchar2 (20)
Varchar (20)

Fax number
Office_fax
Varchar2 (50)
Varchar (50)

Mobile phone number
Pai_phone
Varchar2 (50)
Varchar (50)

Email Box
Email
Varchar2 (50)
Varchar (50)

Zip code
Post_code
Varchar2 (20)
Varchar (20)

Nationality
Country
Varchar2 (50)
Varchar (50)

Province of residence
Province
Varchar2 (20)
Varchar (20)

City of residence
City
Varchar2 (20)
Varchar (20)

Home address
Home_addr
Varchar2 (80)
Varchar (80)

Office location
Office_addr
Varchar2 (80)
Varchar (80)

Document number
Cert_number
Varchar2 (50)
Varchar (50)

Document Name
Cert_name
Varchar2 (20)
Varchar (20)

Career ID
Occupation_id
Char (4)
Char (4)

Length to be filled

Occupation name
Occupation
Varchar2 (50)
Varchar (50)

Educational Level
Education
Varchar2 (20)
Varchar (20)

9.3 common pop-up information fields
Field name
Field code
ORACLE
SQLSERVER
Value
Description

Record status ID
Flag
Char (1)
Char (1)
A/D...
A: valid; D: Delete; S: suspend...

Record No.
Id
Number
Int

9.4 common business information fields
Field name
Field code
ORACLE
SQLSERVER
Value
Description

Business occurrence date
Oper_date
Date
Smalldate

Start date
Begin_date
Date
Smalldate

End Date
End_date
Date
Smalldate

Operation Date
Curr_date
Date
Smalldate

Operator ID
User_id
Char (4)
Char (4)

The User ID in the system information.

Amount

Number (7,2)
Dec (7, 2)

Balance
Balance
Number (10, 2)
Dec (10, 2)

Description
Description
Varchar2 (50)
Varchar (50)

10 design instances (SQL SERVER 2000)

10.1 tables, primary keys, foreign keys, indexes, rules, and constraints
Name: t_base_user

Chinese name: User basic info table

Simple Description: stores basic user information.

Name
Code
Data Type
Keys, rules, and constraints
Note

User ID
User_id
Char (8)
PK
Unique User ID

User Name
User_name
Varchar (20)

Real User Name

Height
Hight
Decimal (7, 2)
Rl_not_zero
Set the three fields for the example. The original table does not.

Weight
Weight
Decimal (7, 2)

Start date
Begin_date
Smalldatetime
Df_begin_date

Department ID
Department_id
Char (4)
FK
Department ID of the user

Reference master table: Department

User title 1id
Headship1_id
Char (4)
FK
The role ID of the user.

Refer to the master table:

User title 2id
Headship2_id
Char (4)
FK
The role ID of the user.

Refer to the master table:

User title ID
Title_id
Char (4)
FK
User title ID

Refer to the master table:

Phone number
Phone
Varchar (50)

User's phone number

Email
Varchar (50)

Bank account
Account_no
Varchar (50)

User's wage bank account

ID
Cert_number
Varchar (30)
UNI INDEX
User ID

Disable
Flag
Char (1)
NOT NULL
Identify whether a user is logged out

/* ===================================================== ======================================= */

/* Table: t_base_user */

/* ===================================================== ======================================= */

Create table t_base_user (

User_id char (8) not null,

User_name varchar (20) null,

Hight decimal (7,2) null,

Weight decimal (7,2) null,

Begin_date smalldatetime null,

Department_id char (4) null,

Headship1_id char (4) null,

Headship2_id char (4) null,

Title_id char (4) null,

Phone varchar (50) null,

Email varchar (50) null,

Account_no varchar (50) null,

Cert_number varchar (30) null,

Flag char (1) null,

Constraint ck_flag CHECK (flag between '0' and '9 '),

Constraint PK_USER_BASE_USERID primary key (user_id)

)

Go

/* ===================================================== ======================================= */

/* Index: idx_cert_number */

/* ===================================================== ======================================= */

Create unique index idx_cert_number on user_base (cert_number)

Go

/* ===================================================== ======================================= */

/* Foreign Key: fk_user_base_department_depid */

/* ===================================================== ======================================= */

Alter table user_base

Add constraint FK_USER_BASE_DEPARTMENT_DEPID foreign key (department_id)

References department (department_id)

Go

...

(Note: The preceding statement assumes that the master table is named depaerment. Follow this statement to create other foreign keys)

/* ===================================================== ======================================= */

/* Bind rule rl_not_zero to column hight in Table user_vase */

/* ===================================================== ======================================= */

Exec sp_bindrule 'dbo. rl_not_zero ', 'user _ base. Ht HT'

/* ===================================================== ======================================= */

/* Bind the default df_begin_date to the begin_date column in The user_base table */

/* ===================================================== ======================================= */

Exec sp_bindefault 'dbo. df_begin_date ', 'user _ base. begin_date'

Appendix 1: Database Data Dictionary

Name: t_base_user

Chinese name: User basic info table

Simple Description: stores basic user information.

Name
Code
Data Type
Foreign key

(Foreign key)
Force

(Constraint)
Primary (primary key)
Note

User ID
User_id
Char (8)

TRUE
TRUE
Unique User ID

User Name
User_name
Varchar (20)

Real User Name

Department id
Department_id
Char (4)
TRUE

Department id of the user

Reference master table: department

User title 1id
Headship1_id
Char (4)
TRUE

The role id of the user.

Refer to the master table:

User title 2id
Headship2_id
Char (4)
TRUE

The role id of the user.

Refer to the master table:

User title id
Title_id
Char (4)
TRUE

User title id

Refer to the master table:

Phone number
Phone
Varchar (50)

User's phone number

Email
Varchar (50)

Bank account
Account_no
Varchar (50)

User's wage bank account

ID
Cert_number
Varchar (30)

User ID

Disable
Flag
Char (1)

TRUE

Identify whether a user is logged out

Primary Key: PK_BASE_USER_USERID

Foreign key: FK_BASE_USER_DEPARTMENT_DEPID ,...

Appendix 2: abbreviated Database Type