Database design specification V2.0

Database design specification 1 purpose

Standardized database design.

2 outline

Designing Documents from the Design Principle of Database Documents to Database Design Specific Thoughts and Name Rules.

3 Database Application Structure According to the analysis of the general business system, unify the database and program system overall description, display the database

The table between the tables and the relationship between the program module.

3.1 Classification of data sheets and program modules

According to the "Processing Features", the data sheets and program modules are classified as follows:

Data Sheet Category: Business Data Sheet, Basic Coded Table, Auxiliary Coding Table, System Information Table, Cumulative Data Sheet, Settlement Data Sheet, Decision Data Sheet. Program module classification: initialization, business processing, integrity detection and correction, settlement processing, statistical processing. 3.1.1 Data Table Category Description Business Data Sheet: Record the processes and results of the business. Such as, contract, warehouse, application form, voucher. Basic Coding Table: Describe basic information and encoding of business entities. Such as products, customers, suppliers, employees. Auxiliary Code: Describe the list of properties. For example, the contract type, title, nation, payment method. System Information Table: Store parameters related to system operations, business control. For example, user information, permissions, user configuration information, cost accounting mode. Cumulative Data Sheet: Store the current value of the business and the accumulated value. For example, current stock, current deposit, accumulated sales, accumulated spending, accounts receivable. Settlement Data Table: Store the number of monoliths at the end of each period. Such as, month-end stocks, month-end bank deposits, accounts receivable. Decision Data Sheet: Statistics that occur during each period of time. Such as, month sales statistics, monthly fund statistics, and enterprising statistics.

3.1.2 Program module Classification Description

Initialization: The system is initialized to the system before the system runs. For example, inventory initialization. Business handling: Control and results record of business processes. For example, the contract is entry, the cost is approved, and the entry is library. Integrity Detection and Correction: Check and automatically correct the accumulated data sheet. Such as check and recalculation of current inventories, current deposits, cumulative sales. Settlement processing: calculate and record the number of monoliths at the end of each period. Inventory month, payment accounts. Statistical processing: calculate and record the statistics that occur during each period. For example, statistics month sales, statistical monthly return, statistical access library.

3.2 Relationship between data tables

Business Data Table <-> Basic Coded Table Lord - Foreign Key Relationship. Such as, contract <-> customer coding table; business data table <-> Auxiliary coding table master - foreign key relationship. Such as, contract table <-> payment method; business data sheet, cumulative data sheet, settlement data table: Cumulative data table = settlement data table (last end) service data table (in this period). Such as current stock = number of stocks at the end of last month (number of reservoirs this month - this month); Decision Data Sheet <-> Business Data Sheet Data Sheet data is derived from the data sheet (statistics) ;

3.3 The relationship between data sheets and program modules The relationship between the data sheet and the program module is described by an example (warehouse management):. Before using the system, the initialization module pairs (cumulative data sheet) and the number of inventories last month ( The docking data table is initialized; when there is a library service, the warehousing module (business processing) will be incorporated and saved in the warehousing document (business data sheet), and the retention number is accumulated In the stock count (cumulative data sheet);. Regular or irregular, inventory count account module (check integrity detection and correction), according to the number of inventories (deposited data sheet), this month, this month (business data sheet) Check if the current inventory number (cumulative data sheet) is in line with, it is not conform to correctly, can be made manually or automatically (current inventory number = number of stocks at the end of last month number of reservoirs in this month - the number of outlines this month); Each month, the monthly process is performed last month. Month Module (Settlement Processing) Based on the number of inventories (dictation data table) at the beginning of last month (business data table), the number of inventories (cumulative data sheet) is calculated on the last month. The formula is: the number of inventories in the last month = number of inventories in the last month number of wares in the last month - the number of wares in the last month;. After each month, the inventory business month statistics module (statistical processing) statistics last month's various stock products The warehousing and the number of warents, facilitates query and generating reports, as well as the data foundation for decision support. 3.4 Treatment of Data Sheets when named

Business Data Sheet: T_D_ _

. If the contract table T_D_sh_contract or t_d_sh_ contract;. Basic Code Table: T_B_ [] _ . Such as customer code table T_b_b_b_b_b_b_b_b_b_b_b_b_b_b_b_B; Auxiliary Code Table: T_A_ [] _ . Such as contract category T_A_CONTTYPE or T_A_ contract category; system information table: t_s _ [] _ . Such as user table T_S_USER or T_S_ users;. Cumulative Data Table: T_T_ _ . Such as current inventory table T_T_SO_STOCK or T_T_SO_

Stock; settlement data table: t_c_ _

. Such as the inventory month table T_C_SO_STOCKMONTH or T_C_SO_ inventory monthly knot; decision data table: T_W_ _ . As a monthly sales statistics table T_W_sh_sellmonth or t_w_sh_ month sales statistics; Note: [] The content represents optional. Such as "T_S _ [] _ " means T_S_SH_USER and T_S_USER are all in line with rules.

4 Database Structure Principles Specify some applicable principles outside the paradigm followed by the database design, based on the database design paradigm, rationally divide the table, add state, and control fields.

4.1 Auxiliary Code Table

In order to enable the auxiliary coding table to play the expected performance, it is not difficult to manage due to excessive auxiliary coding tables, so the use of the auxiliary coding table is as follows:

1. When the encoding of a helper coding table allows the user to add, it should be designed to be "independent" data sheet; otherwise, each auxiliary coding table that is not allowed to add encoded is incorporated into a "universal" auxiliary coding table. 2. "Independence" Auxiliary Coding Table and the column of the primary table use the master-external constraint to ensure column data integrity. 3. "General" auxiliary coding table is not a constraint relationship between the main tables, and the data integrity of the main table is guaranteed by the "domain" of the column. 4. "Gener" secondary encoding ELC, there is an identity column, which is used to identify each code table before the merge, the identity column encoding column as the primary key of the table. 5. For "independent" secondary coding table, users can only add new coding and change the name, and cannot change the meaning representative of the encoding; for the "universal" secondary coding table, the user is not allowed to modify, or only Allow the name of the modification. 4.2 Basic Code Table 1. The basic coding table can have the following identity columns: internal coding, external coding, mobilization, abbreviation, full name. Internal code (unique code) is automatically generated by the primary key, the user is not visible; external coding (unique encoding) is pressed by the user

The rules are defined by themselves; the user can be seen; the help code is pushing, which is convenient to enter, not unique, and the list is selected by the list; referred to as list display and report to shorten the line width. The above columns can be deduced as the case and habits when implementing. 2. When the column of the code table is more and more, the above identity column and the commonly used information can be stored in a table, and other information is stored separately.

4.3 Business Data Sheet

1. The 'entry people' and 'entry date' columns are set, automatically recorded by the system. 2. Set "Automatic Direct Number" in the table of the document, starting with the two characters to distinguish the document, and then represent the serial number with a digital sequence. The 'Automatic Direct No.' is automatically generated by the system, as the primary key of the primary table, and the user is not allowed to modify. When there is a corresponding paper document, set "document" to record the document number of the paper document. 3. The order of entering the line is provided in the schedule. 4. Set the "Archive Tag" column to extract the update tag when data to the decision database. Set the tag when you insert a new row or modify the already row; the data is removed after the data is extracted. 5. For columns used to query the filter criteria, it cannot be empty to avoid "loss". 6. For numerical columns, it cannot be empty, "0" as the default. 7. For the necessary "redundant" columns, such as the customer name, there should be the corresponding program to maintain the identity of the "redundant" columns to avoid objections. 8. Set the "Procedure Status" column and "Record Status" column. Process Status Columns are used to record, such as creation, review, accounting, red-red, etc., recorded status is used to record, such as valid, delete, etc. 5 database naming principle

5.1 table name

Business Data Sheet: T_D_ _

. Basic Code Table: T_B_ [ ]_ . Auxiliary Code: T_A_ [] _ . System Information Table: T_S_ [] _ . Cumulative Data Sheet: T_T_ _ . . Settlement data table: t_c_ _ . Decision data table: t_w_ _ .

5.2 view

V_ _ [] _ . See "Classification of Table" in the view type.

5.3 Storage Process

P_ [] _

5.4 function

F _ [] _ 5.5 Trigger

Tr_

_ (after) Ti_ _ (INSTEAD)

5.6 Custom data type

UD_ _

5.7 Default

DF_

5.8 Rule

Ru_

5.9 primary key

PK_

_

5.10 foreign key

FK_

_ _

Attachment:

In order to describe the first part, please download the "Data Table Classification Description Map"

Visio format http://218.242.185.84/bbs/Update/20036/202218279cbs.vsd

Picture format http://218.242.185.84/bbs/Update/20036/202220359cbs.jpg

转载请注明原文地址:https://www.9cbs.com/read-120129.html

---

9cbs

New Post(0)