Database Design Specifications (naming Rules)

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 info 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;. 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>

 

Iv. Database naming rules
1. database tables
According to the sub-system/module to which the table belongs, the naming method is as follows:
Database Table name = subsystem/module abbreviation + Table meaning
The first letter of the sub-system/module is capitalized, and the first letter of each word in the table meaning is capitalized.
Subsystems/modules and their abbreviations should be described in the agreed section of the Database Design Manual.
For example, if the code table uses the "code" as the table prefix, code_area indicates the region code table.
2. Table fields:
In the conceptual model, each database defines a unique abbreviation for each table, with a length of 4 ~ 8 characters,
Field name = TABLE abbreviation + "_" + field meaning.
For example, if the prefix of a region code table field is "area", its field name is:
Area_id: Primary Key of the region code ID
Area_name: Region code name
Database table names and abbreviations should be described in the database design manual.
3. Index
Index name = idx + "_" + Table abbreviation + related fields/index meaning
4. Association
Association refers to the foreign key relationship between database tables.
Association name = RL + "_" + master table abbreviation + slave table abbreviation
5. Stored Procedure:
Stored Procedure name = proc + "_" + stored procedure meaning