Database coding specification V1.2 2004 Nian 11 Yue 12 Ri category file name: database coding standard version: V1.2 Version Date: 2004-11-12 Author: Nick Date: 2004-11-12 Contents 1 Purpose range 22. Terminology. 3 4 Design Environment. 3 4.2 Design Usage Tools. 4 4.3 Design Principles. 4 4.4 Update. 4 5 Naming Overall Principles. 4 6 Naming Specifications (Logic Objects). 5 6.1 Database Structural naming. 5 6.2 Database object Named. 6 7 Script Note. 8 7.1 Store procedure or trigger. 8 7.2 Custom function. 9 8 Database operation principles. 10 8.1 Creating, deleting, modify the library table operation. 10 8.2 Add, delete , Modify table data. 10 9 Common field names. 10 9.1 System information common field. 10 9.3 Customer Information Common fields. 10 9.4 Trending information common fields. 11 9.4 Business information common fields. 11 10 Design instances (SQL Server 2000) 12 10.1 Table, primary key, foreign key, index, rules, constraints. 12 Annex 1: Database data dictionary. 14 Annex 2: Database Type Short-write. 15 1 To unify the design of the company's software development on database design Programming specifications for specifications and specific work, which is easy to communicate and maintain, and specialize this specification. 2 Scope This specification applies to all staff of the development group, acting on database design and maintenance stages of software project development. 3 Term Ø 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 graphic operation interface; Redhat 9 or more version of CPU P iii 1.7G above memory 512m or more 50GB of hard disk space 5GB or more, if you want to establish More, large databases require more hard disk space B) MS SQL Server 2000 database SQL Server 2000 Enterprise version play sp3 or more patch and secure patch operating system Windows 2000 Server or Windows 2000 Advanced Server hits SP4 or more Patch CPU P III 1G The above memory 256m or more Hard disk space 1g The remaining space If you want to create more, large databases require more hard disk space 4.2 Design Tools A) Use PowerDesigner as a design tool for the database, requires a detailed description of 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 features the function of customizing the Word format report) C) Writing a database built database, initializing database objects, initializing data script files 4.3 design principles a) Using multi-data file B) Prohibition of excessive data files, UNIX systems More than 2GB, 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, prohibits its automatic extension function E) Big text, the blob column has a table independently, this table only ID and BLOB (or big text) column 4.4 design Update a) Maintain by a member of the database administrator or the specified 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) Use PowerDesigner to generate SQL code, prohibit database operations of PowerDesigner direct connection database E) Modifying the database To modify the data via SQL, prohibit other ways to modify the data to SQL to add an instructions After adding a statement, save the return 5 Name Overall Principles Ø Set The prefix will use lowercase letters Ø logo name Name All lowercase Ø The entire naming full length must not exceed 30 letters Ø All use letters and underscore '_', can't use Chinese and other characters, there is a special situation to use the end number number. For example: t_finace1, t_finace2 ... Ø Name name comes from the business, all using English words Ø English word too long can adopt common abbreviation, try to express the meaning of the business Ø If you need more than two English words, the name, word Between the Underline '_' connection Ø Name is all consisting of nouns, the noun is sorted by a large range to small range Ø Complete the name of a feature, such as function and process, named 6 Name Specification in the form of mobile guests ( Logic object) 6.1 Database Structure Name a) Database Naming Database Naming Requirements Use English letters associated with database meaning, and head heads with "db_", ie DB_
. For example, the china Care database is named DB_CCNET; the customer data database is named DB_CUSTOMER_INFO. b) Database log design named database logs to name
_
.log format named. among them,
For meaningful database log name. For example: DB_ccnet_logredo.log c) Database Configuration Design Named Database Configuration Design is saved in file form, and its content is the specific value of configuration items for a particular database. Named of the database profile:
_>
_cfg.ini format naming. 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 Naming Database Copy and Storage Design are saved in file form, which is the specific details 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 Named Database Connection Design is saved in file form, which is the specific details of connection design between a particular distributed database. The database connection design file is named:
_
_dbl. SQL format named. 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 Name Format: TS
_i [n] .dbf temporary table space: Table space Name format: TS
_r [n] .dbf data table space: Table space naming format: TS
_D Database File Name Format: TS
_D [n] .dbf Note: The table space name does not exceed 8 digits, n can be obtained from 00-99 or 0-9, determined according to the amount of system data. 6.2 Database Object Name A) The naming of the table must begin with "Table abbreviation), the format is: t_ [system ID] _
_
. Among them, [] represents optional, increasing according to the actual situation;
English letters associated with expressions, such as t_customers. The data sheet is roughly divided into: business data sheet, basic coded table, auxiliary coding table, system information table, cumulative data sheet, settlement data sheet, decision data table INFO logo ... for example: t_trade_base_trade_code, t_trade_info_help ... b) field / domain naming 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 to use _ separan, which is IDX_COLUMNNAME1_COLUMNNAME2_ ... where columnname1 is the database table (first) The name or name of the index field; columnName2 is the name or name or name of the database table (second) index field; the total length of the index is required to meet the database. Example: IDX_CERT_NUMBER (Index Indexing on Field Cert_Number) D) The naming of the view view must begin with "V _" (View Abbreviation), the format is: V_
_ [System logo] _
. Among them, the view type refers to the "Classification Description of the Table"; [System Identity_] is optional, which is increased according to the situation;
English letters should be associated with view meaning. Example: v_user_detail_info e) The name of the stored procedure stored procedure must comply with SP_ [System ID] _
format. Among them, the SP representation is a stored procedure; [System Identification] is optional, which is incremented by the situation;
It is an English letter associated with the meanings of stored procedures, such as USP_QUERY_WRITE_TO_DISK. Example: sp_CHECK_USER_AUTH (Name) f) The naming of the trigger trigger must comply with TR_
_
format. Among them, TR representation is a trigger;
It is an English letter associated with the meaning of the trigger. Example: TR_USER_INFO_IU (indicating that the User_info table is inserted, updated) G) The naming of the function function must meet the FN_ [System ID] _
format. Where fn representation is a function, [System Identity] is optional, depending on the situation;
It is an English letter associated with the function meaning. Example: Fn_create_id (Name Name) h) Custom Data Type Custom Data Type Name Format is: UD_
_
i) DEFAULT DEFAULT (default) Name format is generally: DF_
For unbound default, you can take the system default name, see page 8.1 Example 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 ( The naming format of constraints) is generally: CK_
_
Some constraints can be placed directly in the form of statements. Example: CK_FLAG See page 8.1 Instance, constraint field FLAG can only take characters '0' to '9': consTRAINT CK_FLAG CHECK (Flag Between '0' AND '9') The naming format of the rule is generally: RL_
For non-binding rules (constraints), see the design instance binding specific fields for the design instance. Example: RL_NOT_ZERO (Define a rule that does not equal 0) if fis = 'r' and name = 'rl_not_zero') Drop rule dbo.rl_not_zero go create rule rl_not_zero as @i <> 0 Go L) The name format of the primary key master key is pk_
_
. Example: PK_USER_INFO_USERID (Table USER_INFO Create primary key) m) M) Outer Outer Outer By Name 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) synonym naming format is: SY_
Example: SY_USER_INFO (public meaning of a table User_info belonging to ownership) 7 Script Notes 7.1 Store or trigger a) Each stored procedure or trigger must write a comment in the forefront, comment below * Writer: CREATE DATE: Ver: deMark: * / In addition, important variables declared during the process must be annotated, for example: @iactionflag int = 0 / * 0 => Checkout, 1 => getLatest, 2 => undocheckout * / b) If only The following comments must be added when the stored procedure or trigger is partially modified: / * Rewriter: add (shutriter): 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, comment below / * function name: xxxx defiction:
PARAM (A, B) A function or description .... B function or description Output: x x = 0 means ..... x = 1 indicates ... Writer: Create Date:
Ver: remark: * / In addition, important variables declared in the function should be annotated, for example: @iactionflag int = 0 / * 0 => Checkout, 1 => getLateSt, 2 => undocheckout * / b) If only the function is performed Some changes are required to add the following notes: / * Rewriter: add (shutriter): 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 Principles 8.1 Creating, Deleting, Modifying the Subscription Operation Ø In the development environment, you can modify it for your own library table; but need to retain the corresponding metering statement and instructions, and People build table time. 8.2 Add, delete, modify table data Ø In the development environment, developers develop modules to use library tables alone, free operation of data; Ø The library table associated with other modules should obtain the developers of other modules to agree Re-execution operation; Ø System information table, 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) The corresponding name group identifies the group identifier GRP_ID CHAR (4) Char (4) The group identifier 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 identifier Correspondence Name Group Prostlelation ID GRP_AUTH_ID CHAR (2) Char (2) From below to high, from 00 to 99. To fill the length group permissions name GRP_AUTH_NAME VARCHAR2 (50) VARCHAR (50) Group Property Limits Corresponds Name Password VARCHAR2 (30) VARCHAR (30) 9.2 Customer Personal Information Field Field Field Name Field Code Oracle SQLServer Value Description Customer_id Char ?) The customer ID used by the char (?) service system, generally the primary key, does not need to grow long types. To fill the length. Customer Name Customer_Name Varchar2 (50) VARCHAR (50) DISD DEPT_ID CHAR (?) Char (?) General key, do not need to grow long types. To fill the length.
Department Name DEPT_NAME VARCHAR2 (50) VARCHAR (50) Gender SEX Char (1) Char (1) F / m below F / M below, Direct Direct Date Date Date (50) VARCHAR (50) Birth Date Birthday Date SmallDatetime Height High High Number (5,2) DEC (5,2) Unit of cm body weight Weight Number (5, 2) DEC (5, 2) unit for ki national nationality varchar2 (20) VARCHAR (20) Contact phone Phone varchar2 (50) VARCHAR (50) Home Phone Home_Phone VARCHAR2 (50) VARCHAR (50) Office Phone 1 office_phone1 varchar2 (50) VARCHAR (50) Office Phone 2 office_phone2 varchar2 (50) VARCHAR (50) Area Code ZONE VARCHAR2 (20) VARCHAR ( 20) Fax Number Office_fax varchar2 (50) VARCHAR (50) Mobile Phone Mobil_Phone Varchar2 (50) VARCHAR (50) Email Varchar2 (50) VARCHAR (50) Postal Code Post_Code Varchar2 (20) VARCHAR (20) Nationality Country varchar2 ( 50) VARCHAR (50) PROVINCE VARCHAR2 (20) VARCHAR (20) VARCHAR2 (20) VARCHAR (20) Home Address Home_addr varcha2 (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) Occupation Occupation_id Char (4) Char (4) To fill long career names Occupation varchar2 (50) VARCHAR (50) Education level EDUC ATION VARCHAR2 (20) VARCHAR (20) 9.3 Top information Field field field name field code Oracle SQLServer Value Description Record Status Logo Flag Char (1) Char (1) A / D ... A: Valid; D: Delete; S: Pause ... record number id number int 9.4 service information common fields field name field Code ORACLE SQLSERVER value Description service occurs date oper_date date smalldate start date begin_date date smalldate end date end_date date smalldate operation date curr_date date smalldate operator number user_id char (4) char ( 4) That is, the number of users in system information Number (7, 2) DEC (7, 2) Dec (7, 2) Dec (10, 2) Dec (10, 2) Description Description Varchar2 (50) VARCHAR (50) 10 Design Example (SQL Server 2000) 10.1 Table, Primary Key, Foreign Key, Index, Rules, Constraint Name: T_Base_User Chinese Name: User Basic Information Form Brief Description: Store User Basic Information Name Code Data Type Key, Rules, Constraint Note User ID User_ID Char (8) PK users uniquely identify user_name varchar (20) User real name Height High Decimal (7, 2)
RL_NOT_ZERO is set to set this three fields, the original table does not.
Weight Decimal (7, 2) Start Date Begin_Date SmallDateTime DF_BEGIN_DATE Department ID Department_id Char (4) FK User Associated Department ID Reference Home Table: Department User Director 1 ID Headship1_id Char (4) FK User Job ID Reference Home Table: User Director 2ID Headship2_id Char (4) FK User Director ID Reference Home: User Title ID Title_id Char (4) FK User Title ID Reference Number: Phone Number Phone Varchar (50) User Phone Number Email Varchar (50) Bank Account Account_no varchar (50) User's salary bank account identity ID CERT_NUMBER VARCHAR (30) UNI INDEX User's Identity ID Disabled Flag Char (1) Not NULL Identify whether the user is canceled / * =========== ============================================================================================================================================================================================================= = * / / * TABLE: T_BASE_USER * / / * ======================================= ===============================================================================================00_Name Varchar (20) Null, User_Name Varchar (20) Null 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 Che CK (Flag Between '0' and '9'), consTRAINT PK_USER_BASE_USERID PK_USER_BASE_USERID PRIMARY Key (user_id)) GO / * ===================
