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, the database and program system are unified, and the database is displayed.
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 table classification: business data sheet, basic coding table, auxiliary code table, system information table, cumulative data sheet, knot
Counting data sheets, decision data tables.
Program module classification: initialization, business processing, integrity detection and correction, settlement processing, statistical processing.
3.1.1 Data State Classification Description
Business Data Sheet: Record the process 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, users
Set information, cost accounting mode.
Cumulative Data Sheet: Store the current value of the business and the accumulated value. Such as, current stock, current deposit, accumulated sales, tired
Expenditure, accounts receivable.
Settlement Data Table: Store the number of monoliths at the end of each period. Such as, month-end stock, month-end bank deposit, accounts receivable
.
Decision Data Sheet: Statistics that occur during each period of time. Such as, month sales statistics, monthly fund statistics, access library
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 the current stock, current deposit,
Cumulative sales check and recalculation.
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,
The deposit is based.
3.2 Relationship between data tables
Business Data Table <-> Basic Coded Table Lord - Foreign Key Relationship. Such as, the contract table <-> customer coding table;
Business Data Sheet <-> Auxiliary Code Table Main - Foreign Key Relationship. Such as the contract <-> Payment method;
Business data sheet, cumulative data sheet, settlement data table: cumulative data sheet = settlement data table (last at the end) business number
According to the table (the occurrence of this issue). Such as current stock = number of stocks at the end of last month (number of reservoirs this month - number of warehouses this month);
Decision Data Sheet <-> The data of the business data sheet decision data table is derived from the data sheet (statistics);
3.3 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 the system use, the initialization module is in stock (cumulative data sheet) and the number of inventories (deposit data sheet)
Row initialization;
When there is a warehouse service, the library module (business processing) will be subjected to the warehouse and saved to the library single file (
Business Data Sheets), at the same time, the number of warents is accumulated to the inventive number (cumulative data sheet);
Regular or irregular, inventory counting module (check integrity detection and correction) according to the number of inventories (survive)
Data table), this month has occurred (Business Data Sheet) Check if the current inventory number (cumulative data sheet) is in line with it, does not match
Then give a tip, can be corrected manually or automatically (the current stock number = number of inventories in the last month number of reservoirs this month - the number of outline this month);
Each month, the monthly process is performed last month. Men's module (settlement process) According to the count number of inventories (deposit data)
Table), the number of years (business data sheet) is calculated from the last month (accumulated data sheet). The formula is: last month
Save count = number of inventories in the last month number of logs in the last month - the number of warehouses last month;
After each month, the inventory business month statistics module (statistical processing) statistics on various stock products in the previous month and
The number of warents is easy to query and generate reports, as well as data foundations for decision support.
3.4 Treatment of Data Sheets when named
Business Data Sheet: T_D_ _
. Such as the contract table T_D_sh_contract
Or t_d_sh_ contract;
Basic Code Table: T_B_ [ ]_
. Such as customer coding table t_b_customer or t_b_
Household;
Auxiliary Code: T_A_ [] _
. Such as contract categories 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 Sheet: T_T_ _
. Such as current inventory table T_T_SO_STOCK or T_T_SO_
in stock;
. Settlement data table: t_c_ _
. Such as inventory month table T_C_SO_STOCKMONTH or
T_C_SO_ inventory month;
Decision data table: t_w_ _
. Monthly sales statistics table T_W_SH_SELLMONTH or
T_W_SH_ month sales statistics;
Note: The content in [] is optional. Such as "T_S _ [] _
" means T_S_SH_USER and
T_S_User is all in line with rules.
4 database structure principle
Specify some applicable principles outside the paradigms followed by the database design, based on the database design paradigm
, Reasonably divide the table, add state, and control fields.
4.1 Auxiliary Code Table
In order to enable the auxiliary coding table to play an expected performance, it is not difficult to manage due to excessive auxiliary coding tables.
The use of the code table is as follows:
1. When the encoding of a helper coding table allows the user to add, it should be designed as "independent" data sheet; otherwise,
Allows the user to add each auxiliary coding table that is encoded 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. The "general" secondary coding table is not constrained between the main tables, and the data integrity of the main list is noted.
"Domain" to ensure.
4. "GM" Auxiliary Coding Extuncture is except the encoding and name, and there is a labeled column that identifies the previous
Code table, the identity column encoding column as the primary key of the table.
5. For "independent" auxiliary coding table, users can only add new coding and change the name, and cannot change one
The meaning representative of the encoding; for "universal" auxiliary coding table, it is not allowed to modify, or only limited
Allow the modification name.
4.2 Basic Code Table
1. Basic coding tables can have the following identity columns: internal coding, external coding, help, abbreviation, full name. Internal code
(Unique Code) As the primary key, the program is automatically generated, 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-described identity column and the common information can be stored in a table.
Its 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 Director Number" in the table of records, starting from two characters to distinguish the document type, then one number
The word sequence represents the serial number. 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. Insert a new line or modify
This tag is set when there is a 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 corresponding programs to maintain the identity of the "redundancy" column.
In order to avoid objections.
8. Set the "Procedure Status" column and "Record Status" column. Process status column is used to record such as creation, review, accounting
, Red-red and other states; record status is used to record, such as effective, 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_
Arbitrary Combination of _ (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"
