Database Design Specifications)

Title: Database Design Specifications

Version: V2.0

Revision document history:
--- Date -- | --- version -- | -- Description ----
How to get started with V1.0
2003.6.17 V2.0 refresh the 1.0 content

Database Design Specifications
1 Purpose

Standardized database design.

2 Overview

This article discusses the standardized ideas and naming rules of database design from the aspects of database design principles and design documents.

3. Database Application Structure

Based on the analysis of general business systems, the database and program systems are described in a unified manner to display

The relationship between tables and program modules.

3.1 classification of data tables and program modules

Data Tables and program modules are classified as follows based on "Processing Features:

Data Table Category: business data table, basic encoding table, auxiliary encoding table, system information table, accumulative data table, and knot

Calculate data tables and decision data tables.
Program module classification: initialization, business processing, integrity detection and correction, settlement processing, and statistical processing.

3.1.1 Data Table Category Description

Business data table: records the process and results of a business. Such as contracts, warehouse picking tickets, application forms, and creden.
Basic Encoding table: describes the basic information and encoding of a business entity. Such as products, customers, suppliers, and employees.
Auxiliary encoding table: List Value of the description attribute. Such as contract type, title, nationality, and payment method.
System Info table: stores parameters related to system operations and business control. For example, user information, permissions, and user configuration

Information and cost accounting methods.
Cumulative data table: stores the current value and cumulative value of the business. Such as current inventory, current deposit, cumulative sales, and fatigue

Expenditure and accounts receivable.
Settlement data table: the number of balances at the end of each period. Such as inventory at the end of the month, bank deposits at the end of the month, accounts receivable month

.
Decision data table: stores the statistical values generated during each period. Such as monthly sales statistics, monthly remittance statistics, and warehouse receiving and picking

Statistics.

3.1.2 program module category description

Initialization: Initialize the system data before the system runs. For example, inventory initialization.
Business Processing: Control and result recording of business processes. For example, contract entry, expense approval, and warehouse receiving and picking.
Integrity detection and correction: checks the accumulated data tables and automatically fixes them. For example, for the current inventory, current deposit,

Check and recalculate cumulative sales.
Settlement processing: calculates and records the number of balances at the end of each period. Inventory monthly settlement and accounts receivable monthly settlement.
Statistical Processing: calculates and records the statistics generated during each period. For example, monthly sales statistics, monthly payments statistics

Warehouse receiving and receiving.

3.2 Relationship between data tables

Business Data Table <--> Primary-foreign key relationship of the Basic Encoding table. For example, contract Table <--> customer code table;
Business Data Table <--> the primary-foreign key relationship of the secondary encoding table. For example, the contract form <--> payment method;
Business data table, accumulative data table, and settlement data table: accumulative data table = settlement data table (last period) + business count

Data Table (occurred in this period ). For example, the current inventory = the number of inventories at the end of last month + (number of warehouse receiving this month-Number of warehouse picking this month );
Decision data table <--> the data in the decision data table of the business data table is exported (Statistics) from the business data table;

3.3 relationship between data tables and program modules

An example (Warehouse Management) is used to describe the relationship between data tables and program modules:
Before the system is used, the initialization module enters

Row initialization;
When a warehouse receiving service occurs, the warehouse receiving module (Business Processing) enters the warehouse receiving ticket and saves it to the warehouse receiving ticket closing account (

Business Data Tables), and add the number of incoming data to the inventory (accumulative data tables;
. Regular or irregular, the inventory quantity accounting module (check integrity detection and correction) is based on the inventory quantity at the end of last month

Check whether the current inventory quantity (accumulative data table) is consistent with and does not match

The prompt is displayed, which can be manually or automatically corrected (current inventory count = last month's inventory count + number of warehouse receiving this month-Number of warehouse picking this month ).

);
. At the beginning of each month, end the month of the previous month. The monthly settlement module (settlement processing) is based on the number of stocks in the beginning of last month (settlement data

Table), the number of occurrences of the previous month (business data table) is calculated from the last month's inventory (accumulative data table ). Formula: Last month Database

Inventory = inventory at the beginning of the previous month + warehouse receiving from the previous month-Number of warehouse picking from the previous month;
After the completion of each month, the inventory business monthly statistics module (statistical processing) collects statistics on the receipt and

The number of warehouse picking, which is easy to query and generate reports and serves as the data basis for decision-making support.

3.4 considerations for data table classification during data table naming

. Business data table: t_d _ <System ID >_< table ID>. For example, the sales system's contract Table t_d_SH_Contract

Or t_d_SH _ contract;
. Basic Encoding table: t_ B _ [<System ID>] _ <Table ID>. For example, the customer code table t_ B _Customer or t_ B _ customer

User;
Auxiliary encoding table: t_a _ [<System ID>] _ <Table ID>. For example, the contract type t_a_ContType or t_a _ contract

Category;
. System information table: t_s _ [<System ID>] _ <Table ID>. For example, user table t_s_User or t_s _ user;
. Accumulative data table: t_t _ <System ID >_< table ID>. For example, the current inventory table t_t_SO_Stock or t_t_SO _

Inventory;
. Settlement data table: t_c _ <System ID >_< table ID>. For example, the inventory month table t_c_SO_StockMonth or

T_c_SO _ monthly inventory close;
. Decision data table: t_w _ <System ID >_< table ID>. For example, the monthly sales statistical table t_w_SH_SellMonth or

T_w_SH _ monthly sales statistics;

Note: The content in [] is optional. For example, "t_s _ [<System ID>] _ <Table ID>" indicates t_s_SH_User and

T_s_User is compliant with the rules.

4. database structure principles

It defines some applicable principles other than the paradigm followed by the database design, and on the basis of the database design paradigm

, Rationally divide tables, add status and control fields.

4.1 auxiliary encoding table

In order to make the auxiliary encoding table play the expected efficiency, and it is not difficult to manage too many auxiliary encoding tables

The usage of the encoding table is as follows:

1. When the code of an auxiliary encoding table allows users to add it, it should be set to an "independent" data table; otherwise, no

The user is allowed to add the encoded auxiliary encoding tables into a "common" Auxiliary encoding table.
2. The "independent" secondary encoding table and the columns in the primary table use the primary-outer constraints to ensure the integrity of the column data.
3. There is no constraint between the "General" Auxiliary encoding table and each master table. The data integrity of the master table column is described

"Domain" to guarantee.
4. In addition to the encoding and name columns, the "General" Auxiliary encoding table also has an ID column to identify

Table, which is used as the primary key of the table.
5. For the "independent" Auxiliary encoding table, you can only add new encoding and change the name, and cannot change one.

The meanings of codes. In principle, users are not allowed to modify the "general" Auxiliary encoding table

The name can be modified.

4.2 Basic Encoding table

1. The basic encoding table can have the following identification columns: Internal encoding, External Encoding, mnemonic code, abbreviation, and full name. Internal Encoding

(Unique encoding) is automatically generated by a program as the primary key and invisible to users. External Encoding (unique encoding) is based on

The rules are customized and visible to users. The help code is scaled down in pinyin, which is easy to input and not unique. The list selects duplicate codes.

Used for list display and report to shorten the row width. The preceding columns can be deleted based on actual conditions and habits.
2. When there are many columns and there are too many rows in the code table, you can store the above identification columns and common information in a table

Its information is stored in another table.

4.3 business data table

1. The "entry person" and "entry date" columns are set and automatically recorded by the system.
2. Set the "Automatic document number" in the document record table, which starts with two characters to differentiate the document type, followed by one

The sequence number. The 'automatic document number' is automatically generated by the system. It is used as the primary key of the master table and cannot be modified by users.

When there is a corresponding paper document, set the "document number" to record the document number of the paper document.
3. There is a row sequence number in the detail table, and the order of row entry is automatically recorded.
4. Set the archive mark column to update the mark when data is extracted to the decision database. Insert new line or modify

Set this flag when the row already exists. Clear the mark after data extraction.
5. The column used to query filtering conditions cannot be blank to avoid "loss" of the row ".
6. The value column cannot be blank. "0" is the default value.
7. For necessary "redundancy" columns, such as the customer name, the corresponding program should maintain the same identity of each "redundancy" column.

To avoid any objection.
8. Set the "Process status" column and "record status" column. The process Status column is used to record records such as creation, review, and accounting.

Status, such as red. The record status is used to record the status such as valid and deleted.

5 database naming principles

5.1 table name

. Business data table: t_d _ <System ID >_< table ID>.
. Basic Encoding table: t_ B _ [<System ID>] _ <Table ID>.
Auxiliary encoding table: t_a _ [<System ID>] _ <Table ID>.
. System information table: t_s _ [<System ID>] _ <Table ID>.
. Accumulative data table: t_t _ <System ID >_< table ID>.
. Settlement data table: t_c _ <System ID >_< table ID>.
. Decision data table: t_w _ <System ID >_< table ID>.

5.2 View

V _ <view type >_[ <System ID>] _ <view ID>. For view types, see Table classification.

5.3 stored procedures

P _ [<System ID>] _ <Stored Procedure ID>

5.4 Functions

F _ [<System ID>] _ <function ID>

5.5 trigger

Tr _ <Table Name >_< I, u, d any combination> (after)
Any combination of ti _ <Table Name >_< I, u, and d> (instead)

5.6 Custom Data Types

Ud _ <custom data type identifier >_< data type>

5.7 Default

Df _ <Default ID>

5.8 Rule

Ru _ <Rule ID>

5.9 primary key

Pk _ <Table Name >_< primary key ID>

5.10 foreign key

Fk _ <Table Name >_< main table name >_< foreign key ID>