Database Design Specification V2.0

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 systemsProgramThe system provides a uniform overall description 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, secondary encoding table, system information table, accumulative data table, settlement data table, and decision data table.
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. Such as user information, permissions, 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, accumulative sales, accumulative 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, and monthly settlement of accounts receivable.
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, check and recalculate the current inventory, current deposit, and 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 remittance statistics, and warehouse receiving statistics.

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 data table (this period occurs ). 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 initializes the inventory quantity (accumulative data table) and the inventory quantity (deposit data table) at the end of last month;
. When a warehouse receiving service occurs, the warehouse receiving module (Business Processing) enters and saves the warehouse receiving ticket to the warehouse receiving Bill (business data table), and accumulates the warehouse receiving count to the inventory count (accumulative data table) medium;
. 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 (the number of deposit data tables), the number of occurrences this month (business data tables) check whether the current inventory quantity (accumulative data table) meets the requirements. If not, a prompt is displayed, manual or automatic correction is allowed (current inventory = last month's inventory + current month's warehouse receiving-current month's warehouse picking );
. At the beginning of each month, end the month of the previous month. The monthly settlement module calculates the inventory quantity (accumulative data table) at the end of the last month based on the inventory quantity (deposit data table) at the beginning of the last month and the number of occurrences (business data table) at the last month ). Formula: Last month inventory = last month's first inventory + last month's warehouse receiving-last month's warehouse picking;
. After the completion of each month, the inventory business monthly statistics module (statistical processing) collects the number of warehouse receiving and warehouse picking items in the previous month, which facilitates query and generation of 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;
Auxiliary encoding table: T_A _ [<System ID>] _ <Table ID>. For example, the contract type t_a_conttype or T_A _ contract type;
. 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 _ inventory month is closed;
. Decision data table: t_w _ <System ID >_< table ID>. For example, 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 that both t_s_sh_user and t_s_user comply with the rules.

4. database structure principles

It defines some applicable principles other than the paradigm followed by the database design, and rationally divides tables, adds state and control fields based on the database design paradigm.

4.1 auxiliary encoding table

In order to make the auxiliary encoding table play the expected performance, and it is not difficult to manage too many auxiliary encoding tables, the use of the auxiliary 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, merge the secondary encoding tables that are not allowed to be added into a "common" secondary 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 guaranteed by the "Domain" specified by the column.
4. In addition to the encoding and name columns, the "common" Auxiliary encoding table also has an ID column used to identify the pre-merger code table. This ID column + encoding column serves as the primary key of the table.
5. for an "independent" Auxiliary encoding table, you can only add new encoding and change the name, and cannot change the meaning of an encoding. For a "universal" Auxiliary encoding table, in principle, you are not allowed to modify the name, or you are only allowed to modify the name.

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 as the primary key and invisible to users. External Encoding (unique encoding)

The rules are customized and visible to the user. The help code is scaled down in pinyin, which is easy to input and not unique. The list is selected when the duplicate code is used. The abbreviation is used for list display and report to shorten the line 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 one table and store other information 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 distinguish the document type, followed by a numerical sequence to represent the serial 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 a new row or modify an existing row to set the tag. Clear the tag 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 procedures 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 statuses such as creation, review, accounting, and reddening. The record status is used to record statuses 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>