Database Design Standards

Preface

Database design is the core foundation of a software system. Its standardization directly affects system performance, stability, and maintainability. To ensure consistency across data structures and system architectures for all product lines, Qiantong Tech has formulated these Database Design Standards.

These standards bring together the company's experience from long-term project practice and have become the unified design standard followed by Qiantong Tech products, including qKnow Knowledge Platform, qData Data Platform, qThing IoT Platform, and qModel Model Management Platform. Through standardized database design, the team can maintain consistent structures and styles during parallel development across multiple projects, improve system performance and development efficiency, and ensure product quality and extensibility.

---

1. Module Design

1. Module division standards

Module division is a foundational part of database design. It should follow the principles of clear business logic, reasonable structure, and easy maintenance and extension, ensuring system sustainability during development, iteration, and later operations.

• Business-driven: Module division should be based on actual business requirements. Each module should correspond to a clear feature or business process, avoiding artificial splitting or arbitrary piling.
  Examples:

  • The User Management Module handles user registration, login, role assignment, and related features.
  • The Project Management Module handles project information maintenance, task assignment, and progress tracking.
  • The Report Module handles data statistics and visualization.

• High cohesion and low coupling: Coupling between modules should be minimized while ensuring high cohesion inside each module. For example, the user management module (um) should not directly depend on the table structure of the project management module (pm), but should interact through interfaces or the service layer.
  Description: If one module frequently calls another module's interfaces, the division may be problematic and responsibility boundaries should be reviewed.

• Layered design: Modules can be designed by business logic and functional layers, typically including:

  • Foundation layer: such as database interaction and common utilities;
  • Logic layer: such as business rule calculation and state machine handling;
  • Application layer: such as user interfaces, API services, and frontend integration logic.

  A layered structure helps with system decoupling, test isolation, and distributed deployment.

• Balance: Module division should be as balanced as possible. Avoid oversized modules, such as modules containing hundreds of tables, because they affect later maintenance and extension. A single module is recommended to manage no more than 30 tables; if exceeded, consider further splitting.

• Flexibility and extensibility: Module design should consider possible future extensions or changes, avoid excessive coupling, and preserve module flexibility and extensibility. For example, if the user module may need multi-tenant support in the future, reserve a tenant_id field early or use an independent schema solution.

2. Module naming standards

Good module naming is the foundation of database readability and maintainability. Module names should follow the principles of being unified, concise, and meaningful, so team members can understand them at a glance.

• Concise and meaningful: Module names should be short and clearly express the module's function. Avoid overly long or abstract names.
  Examples:

  • User Management Module → user or um
  • Project Management Module → project or pm

• Use abbreviations: Since module names are used as table name prefixes, simplify them into appropriate abbreviations and keep them within a reasonable length, usually 2-4 letters. Common examples:

  Module Name	Abbreviation	Example Table Name
  User Management Module	um	um_user
  Project Management Module	pm	pm_project
  System Configuration Module	sc	sc_config

• Avoid redundancy: Avoid redundant words such as "system" and "management" in names. Focus on reflecting the module's core function.
  Not recommended: user_manage_system
  Recommended: um_user

• Unified naming rules: All projects must adopt unified naming standards to avoid maintainability issues caused by inconsistent naming from different teams or developers. The architecture team is recommended to publish a unified module abbreviation reference table at project startup.

---

2. Database Naming Standards

1. Environment-specific naming

To ensure data isolation and deployment security across development, test, and production environments, database names must strictly distinguish environments:

• Development database: [project_code]_dev
  Examples: qData_dev, qKnow_dev

• Test database: [project_code]_test
  Examples: qData_test, qModel_test

• Production database: [project_code]_prod
  Examples: qKnow_prod, qThing_prod

⚠️ Note: Never directly use databases with _dev or _test suffixes in production environments.

2. Project code naming

• Project codes should be short, unique, and business-identifiable, preferably 2-10 characters, making it easy to manage and distinguish databases for different projects.
  • Examples: qData (Qiantong Data Platform), qKnow (Qiantong Knowledge Platform), qAuth (Unified Identity Authentication Platform)

3. Unified naming rules

• Database naming formats must be unified across all environments so operations staff can quickly identify environment types.
• Enforce naming standards in database creation scripts and CI/CD configuration files.

---

3. Table Design

1. Table naming standards

• Lowercase letters and underscores: All table names must use lowercase letters, with words separated by underscores.
  ✅ Correct examples: user_info, order_details, product_category
  ❌ Incorrect examples: UserInfo, ORDERDETAILS, user-info

💡 If a customer has special requirements, such as uppercase preference in Oracle environments, modeling tools such as PDManer can be used to uniformly convert case during DDL export, but logical design should still use lowercase.

• Concise and accurate: Table names should be short and accurately reflect the data entity they store.
  Examples:

  • um_user: main user table in the user management module
  • pm_project: main project table in the project management module

• Avoid generalized business terms: Do not use vague terms such as "data table", "record table", or "information table". Focus on the entity itself, such as user instead of user_data_table.

• Avoid special characters and numbers: Table names must not contain spaces, Chinese characters, -, #, @, or other special characters, and should not start or end with pure numbers.

• Prefix rule: All business tables must use the owning module abbreviation as the prefix.
  Examples:

  • um_user_role_rel (user-role relationship table)
  • om_order_history (order history table)
  • sm_system_config (system configuration table)

2. Naming identifiers for special table types

To improve database readability, tables with specific purposes should use unified suffix identifiers:

Table Type	Naming Suffix	Example	Description
Relationship table	_rel	um_user_role_rel	Many-to-many association table
Log table	_log	sm_operation_log	Operation logs, error logs, and similar data
History table	_history	om_order_history	Records data change history
Configuration table	_config	sm_app_config	Stores system-level or application-level configuration parameters

📌 Recommendation: History tables and log tables should be archived regularly to avoid primary database bloat.

---

4. Field Design

1. Logical field naming standards (technical naming)

• Short and clear: Field names should reflect their business meaning, such as user_id and order_amount. Avoid excessive abbreviations such as uid and amt.

• Lowercase letters and underscores: Field names should uniformly use lowercase letters plus underscores.
  ✅ Examples: create_time, request_ip, del_flag

• Avoid reserved words: SQL reserved words must not be used as field names, such as order, group, desc, and user. If they must be used, backticks are required, but this is not recommended.

• Unified naming rules: Fields with the same semantic meaning should use the same name across different tables.
  Example: the "creator" field in all tables should be named create_by, not creator or created_by_user.

2. Field name standards (business naming / Chinese comments)

Note: "field name" here refers to the logical field name displayed in database modeling tools such as PDManer or PowerDesigner, not the physical column name.

• Chinese naming: In data model documents or ER diagrams, fields should use Chinese descriptions so business staff can understand them.
  Examples:

  • Physical column name: user_id → logical name: User ID
  • Physical column name: create_time → logical name: Creation time

• Concise and clear: Avoid piling up technical terms. For example, "user unique identifier" should be simplified to "user ID".

• Avoid ambiguity: A term such as "time" should be made explicit as "creation time" or "update time"; do not write only "time".

3. Field comment standards

High-quality field comments are key to database self-documentation:

• Concise and clear: A comment should describe the field purpose in one sentence.
  Example: the comment for user_status is: "User status: 0-disabled, 1-enabled"

• Business-driven: Comments should be written from the user or business perspective, not the technical implementation perspective.
  ✅ Good comment: "Whether to receive marketing SMS messages (0-no, 1-yes)"
  ❌ Poor comment: "Boolean flag for SMS subscription"

• Comment content should include:

  • Field business meaning
  • Enumeration value description, if any
  • Whether NULL is allowed
  • Whether it is a primary key or foreign key
  • Default value, if any

• Unified style: Use the following template when possible:

  "[Business meaning]. Values: [enum description]. [Other constraints]"
  Example:
  "Order payment status. Values: 0-unpaid, 1-paid, 2-refunded. Not null."

• Example SQL:

CREATE TABLE um_user (
    id BIGINT PRIMARY KEY COMMENT 'Primary key ID. Not null, uniquely identifies the user record.',
    username VARCHAR(50) NOT NULL COMMENT 'Username. Unique and not null.',
    password VARCHAR(100) NOT NULL COMMENT 'Login password. Stored encrypted and not null.',
    status TINYINT DEFAULT 1 COMMENT 'User status. Values: 0=disabled, 1=enabled. Not null, default 1.',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time. Defaults to current time.',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record last modification time. Auto-updated.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User information table. Stores basic system user information.';

4. Field data type standards

• Choose data types reasonably:

  Business Scenario	Recommended Type	Example
  User ID, order ID	BIGINT	Auto-increment primary key or Snowflake ID
  Username, email	VARCHAR(50~255)	Estimate based on actual length
  Password	VARCHAR(100)	Store encrypted string
  Amount	DECIMAL(18,2)	Avoid FLOAT/DOUBLE
  Creation time	DATETIME	Accurate to seconds
  Whether deleted	TINYINT(1)	0-no, 1-yes
  Long text, such as description	TEXT	Not recommended to exceed 64KB

• Preset type configuration: Common field templates such as "standard primary key" and "standard timestamp" should be predefined in database modeling tools for direct reuse during design.

• Consider extensibility: For example, a phone number currently has 11 digits, but may support international numbers later. Use VARCHAR(20) instead of CHAR(11).

• Avoid over-design: Do not reserve oversized fields for scenarios that "might be needed in the future". For example, user nicknames usually do not exceed 30 characters and do not need VARCHAR(255).

---

5. Field Ordering Principles

Reasonable field ordering helps improve readability and query efficiency.

1. Regular field ordering (recommended order)

1. Primary key fields (such as id)
2. Foreign key fields (such as user_id, project_id)
3. Core business fields (such as username, order_no, amount)
4. Status/category fields (such as status, type, gender)
5. Metadata fields (such as create_by, create_time, update_time, del_flag)

Example: field order of the um_user table

id,
tenant_id,          -- Multi-tenant scenario
username,
email,
phone,
status,             -- 0-disabled 1-enabled
gender,             -- Gender: 0-unknown 1-male 2-female
create_by,
create_time,
update_time,
del_flag

2. Type field ordering

• Category fields first: Fields such as gender, user_type, and order_source should be placed earlier to make WHERE filtering easier.
• Enumeration fields first: Frequently used enumeration fields, such as status fields, should be placed after business fields and before time fields.

---

6. Field Grouping Principles

1. Group related fields

• Place logically related fields together:

  • User basic information: username, email, phone, avatar
  • Audit fields: create_by, create_time, update_by, update_time
  • Soft delete fields: del_flag, delete_time

• Group fields with similar data types:

  • All time fields together (create_time, update_time, login_time)
  • All amount fields together (order_amount, discount, pay_amount)

2. Prioritize frequently queried fields

• Put frequently queried fields earlier: Fields such as user_id, order_status, and create_time, which are often used in WHERE or JOIN conditions, should be placed toward the front of the table.
• Make index fields explicit: Although physical field position does not affect index performance, placing indexed fields earlier helps developers quickly identify key fields.

---

Appendix: Common Field Names and Standards

No.	Field Meaning	Field Name	Remarks	Type	Length
1	Category	category	Field used to represent a category or classification.	VARCHAR	128
2	Type	type	Field used to represent a type, often used for status types.	VARCHAR	10
3	Description	description	Field used to briefly describe a data entity. varchar(512) is used in many SQL locations.	VARCHAR	512
4	Introduction	introduction	Field used to provide a detailed introduction or explanation of a data entity.	TEXT	—
5	Code	code	Unique identifying code, such as product code or user code.	VARCHAR	128
6	Level	level	Used to represent level, permission level, and similar concepts.	INT	—
7	Content	content	Field used to store main content.	TEXT	—
8	Sort value	order_num	Sorting field, usually used for sorting or priority control.	INT	—
9	Status	status	Status field, such as enabled/disabled or active/frozen.	TINYINT	4
10	Audit status	audit_status	Audit status, such as pending review, approved, or rejected.	TINYINT	4
11	Auditor	auditor_id	Auditor, usually the auditor ID or username.	BIGINT	—
12	Audit time	audit_time	Indicates the audit time, usually used to record the exact audit time point.	DATETIME	—
13	Name	name	Field used to represent a name, such as user name or project name.	VARCHAR	128
14	Phone number	phone	Phone number field.	VARCHAR	20
15	Email	email	Email field.	VARCHAR	100
16	Address	address	Address field.	VARCHAR	255
17	Gender	gender	Gender field. Values: 0=unknown, 1=male, 2=female. Not null, default 0.	TINYINT	4
18	Age	age	Age field.	INT	—
19	Birthday	birthday	Date of birth field.	DATE	—
20	Avatar	avatar	Avatar field.	VARCHAR	256
21	Creator	create_by	Records the creator's name.	VARCHAR	32
22	Creator ID	creator_id	Records the creator's ID.	BIGINT	—
23	Creation time	create_time	Records creation time.	DATETIME	—
24	Updater	update_by	Records the name of the last updater.	VARCHAR	32
25	Updater ID	updater_id	Records the ID of the last updater.	BIGINT	—
26	Update time	update_time	Records the last update time.	DATETIME	—
27	Deleter	delete_by	Soft delete field, indicating the name of the person who deleted the record.	VARCHAR	32
28	Deleter ID	deleter_id	Soft delete field, indicating the ID of the person who deleted the record.	BIGINT	—
29	Delete time	delete_time	Soft delete field, indicating the record deletion time.	DATETIME	—
30	Whether deleted	del_flag	Soft delete flag (0: not deleted, 1: deleted).	TINYINT	1
31	Whether valid	valid_flag	Indicates whether it is valid (0: invalid, 1: valid).	TINYINT	1
32	Whether locked	lock_flag	Indicates whether it is locked (0: not locked, 1: locked).	TINYINT	1
33	Whether enabled	enable_flag	0 means disabled, 1 means enabled.	TINYINT	1
34	Whether required	require_flag	Indicates whether the field is required (0: not required, 1: required).	TINYINT	1
35	Remark	remark	Used to supplement explanations or remark information.	VARCHAR	512
36	Parent ID	parent_id	Parent ID of the record, used to identify parent nodes in tree structures or hierarchies.	BIGINT	—
37	Child ID	child_id	Used to identify child nodes in tree structures or hierarchies.	BIGINT	—
38	Path	path	Used to store path information, such as file paths or URL paths.	VARCHAR	256
39	Related ID	related_id	ID associated with another entity, such as the user ID associated with an order.	BIGINT	—
40	Action type	action_type	Operation type, such as add, delete, or update.	TINYINT	4
41	Permission	permission	Permission field.	VARCHAR	100
42	Permission group	role_group	Permission group field, such as role assignment.	VARCHAR	100
43	File name	file_name	Name of the file.	VARCHAR	128
44	File size	file_size	Size of the file.	BIGINT	—
45	File path	file_path	File storage path.	VARCHAR	256
46	File type	file_type	File type, such as pdf, jpg, or png.	VARCHAR	32
47	Expiry time	expiry_date	Time field indicating whether certain data has expired.	DATETIME	—
48	Timestamp	timestamp	Used to record a timestamp.	DATETIME	—
49	Request source	source	Request source field.	TINYINT	4
50	Request parameters	params	Request parameter field.	TEXT	—
51	Request IP	request_ip	Request source IP address.	VARCHAR	45
52	IP address	ip_address	Field for storing an IP address.	VARCHAR	45
53	Version number	version	Data version field, recording the version number.	INT	—
54	Whether default	default_flag	Whether it is the default value (0: no, 1: yes).	TINYINT	1
55	Trigger time	trigger_time	Time when an event is triggered.	DATETIME	—
56	Response time	response_time	Time when a response event occurs.	DATETIME	—
57	Visit count	visit_count	Records the number of visits.	INT	—
58	Verification info	verification_info	Used to store verification information, such as verification codes.	VARCHAR	100
59	Unique identifier	uuid	Unique identifier, commonly used in distributed systems.	VARCHAR	36
60	Serial number	serial_no	Unique number commonly used for items, orders, products, and similar entities.	VARCHAR	64
61	Tag	tag	Records tag information for projects, users, products, and similar entities.	VARCHAR	128

---

Tip

Follow the standards to move steadily and go farther.