Database Schema
Relevant source files
The following files were used as context for generating this wiki page:
- project-datamodel/zheng.sql
- project-datamodel/update_20170328_1_to_20170405_1.sql
- project-datamodel/README.md
- zheng-cms/zheng-cms-rpc-service/src/main/java/com/zheng/cms/dao/mapper/CmsArticleMapper.xml
- zheng-upms/zheng-upms-rpc-service/src/main/java/com/zheng/upms/dao/mapper/UpmsSystemMapper.xml
- zheng-cms/zheng-cms-dao/src/main/java/com/zheng/cms/dao/Generator.java
- zheng-upms/zheng-upms-dao/src/main/java/com/zheng/upms/dao/Generator.java
The zheng project utilizes a MySQL database to persist data for its various microservices. The schema is logically partitioned based on functionality, with distinct sets of tables for User Permission Management (UPMS), Content Management (CMS), User Center (UCENTER), and Payment services (PAY). This modular design aligns with the microservice architecture of the application.
The database model is maintained using PowerDesigner, with the primary SQL script being zheng.sql. This file contains the full schema definition, including table structures, primary keys, foreign keys, and initial seed data. Update scripts are generated to manage schema evolution between versions, as seen in update_20170328_1_to_20170405_1.sql.
Sources: project-datamodel/zheng.sql, project-datamodel/README.md:5-12, project-datamodel/update_20170328_1_to_20170405_1.sql
Core Modules
The database is organized into four main modules, each prefixed to identify its domain:
upms_: User Permission Management Systemcms_: Content Management Systemucenter_: User Centerpay_: Payment System
Sources: zheng-cms/zheng-cms-dao/src/main/java/com/zheng/cms/dao/Generator.java:15, zheng-upms/zheng-upms-dao/src/main/java/com/zheng/upms/dao/Generator.java:15, project-datamodel/zheng.sql
UPMS (User Permission Management System)
The UPMS module implements a Role-Based Access Control (RBAC) model. It manages systems, users, roles, permissions, and organizational structures.
Sources: project-datamodel/zheng.sql:1011-1376
UPMS ER Diagram
This diagram illustrates the relationships between the main tables in the UPMS module.
Key Tables
upms_user
Stores administrative user accounts for the management backend.
| Column | Type | Description |
|---|---|---|
user_id | int(10) unsigned | Primary Key, User ID |
username | varchar(20) | Account name |
password | varchar(32) | MD5 hash of password + salt |
salt | varchar(32) | Salt for password hashing |
realname | varchar(20) | User's real name |
locked | tinyint(4) | Status (0: normal, 1: locked) |
ctime | bigint(20) | Creation time |
Sources: project-datamodel/zheng.sql:1270-1283
upms_permission
Defines the permissions available within the systems. Permissions can be menus, buttons, or other actions.
| Column | Type | Description |
|---|---|---|
permission_id | int(10) unsigned | Primary Key, Permission ID |
system_id | int(10) unsigned | Foreign key to upms_system |
pid | int(10) | Parent permission ID for hierarchical structure |
name | varchar(20) | Permission name |
type | tinyint(4) | Type (1: directory, 2: menu, 3: button) |
permission_value | varchar(50) | Permission string value (e.g., upms:user:read) |
uri | varchar(100) | Path associated with the permission |
status | tinyint(4) | Status (0: disabled, 1: normal) |
Sources: project-datamodel/zheng.sql:1080-1094
CMS (Content Management System)
The CMS module manages all content-related entities, including articles, categories, tags, comments, and static pages.
Sources: project-datamodel/zheng.sql:15-464, update_20170328_1_to_20170405_1.sql:26-177
CMS ER Diagram
This diagram shows the core entities of the CMS and their relationships.
Sources: project-datamodel/zheng.sql:15-464
Key Tables
cms_article
This is the central table for storing articles.
| Column | Type | Description |
|---|---|---|
article_id | int(10) unsigned | Primary Key, Article ID |
topic_id | int(11) | Foreign key to cms_topic |
title | varchar(200) | Article title |
content | mediumtext | The main content of the article |
user_id | int(10) unsigned | ID of the user who published the article |
status | tinyint(4) | Status (-1: Not approved, 0: Unaudited, 1: Approved) |
system_id | int(11) | The system this article belongs to (e.g., news, blog) |
ctime | bigint(20) unsigned | Creation timestamp |
Sources: project-datamodel/zheng.sql:17-36, zheng-cms/zheng-cms-rpc-service/src/main/java/com/zheng/cms/dao/mapper/CmsArticleMapper.xml:4-21
cms_category
Stores hierarchical categories for organizing articles.
| Column | Type | Description |
|---|---|---|
category_id | int(10) unsigned | Primary Key, Category ID |
pid | int(10) unsigned | Parent category ID for tree structure |
level | tinyint(4) | The depth level in the hierarchy |
name | varchar(20) | Category name |
alias | varchar(20) | URL-friendly alias |
system_id | int(11) | The system this category belongs to |
Sources: project-datamodel/zheng.sql:168-184
UCENTER (User Center)
The UCENTER module is responsible for managing end-user data, including profiles, authentication details, and logs. It is distinct from the upms_user table, which is for administrative users.
Sources: project-datamodel/zheng.sql:810-937
UCENTER ER Diagram
This diagram shows the user-centric tables and their relationships, including OAuth integration.
Sources: project-datamodel/zheng.sql:810-937
Key Tables
ucenter_user
Stores basic information for end-users of the public-facing applications.
| Column | Type | Description |
|---|---|---|
user_id | int(10) unsigned | Primary Key, User ID |
password | varchar(32) | MD5 hash of password + salt |
salt | varchar(32) | Salt for password hashing |
nickname | varchar(20) | User's nickname |
sex | tinyint(4) | Gender (0: Unknown, 1: Male, 2: Female) |
avatar | varchar(100) | URL to user's avatar image |
create_time | timestamp | Registration timestamp |
Sources: project-datamodel/zheng.sql:829-842
ucenter_user_oauth
Links user accounts to third-party authentication providers like WeChat, QQ, etc.
| Column | Type | Description |
|---|---|---|
user_oauth_id | int(10) unsigned | Primary Key |
user_id | int(10) unsigned | Foreign key to ucenter_user |
oauth_id | int(10) unsigned | Foreign key to ucenter_oauth (e.g., QQ, WeChat) |
open_id | varbinary(50) | The unique ID from the third-party provider |
status | tinyint(4) unsigned | Binding status (0: Unbound, 1: Bound) |
Sources: project-datamodel/zheng.sql:920-929
PAY (Payment System)
The PAY module contains tables for managing payment-related data, including merchants, vendors, and transaction orders.
Sources: project-datamodel/zheng.sql:466-670
PAY ER Diagram
This diagram outlines the structure for handling payment orders and configurations.
Sources: project-datamodel/zheng.sql:466-670
Key Tables
pay_in_order
Represents an incoming payment order.
| Column | Type | Description |
|---|---|---|
pay_in_order_id | int(10) unsigned | Primary Key |
pay_vendor_id | int(10) | Foreign key to pay_vendor (e.g., Alipay, WeChat Pay) |
pay_mch_id | int(10) | Foreign key to pay_mch (the merchant) |
amount | decimal(10,0) | Order amount |
status | tinyint(4) | Order status |
ctime | bigint(20) unsigned | Creation time |
Sources: project-datamodel/zheng.sql:468-478
pay_mch
Stores information about merchants using the payment system.
| Column | Type | Description |
|---|---|---|
pay_mch_id | int(10) | Primary Key |
mch_id | varchar(20) | Merchant ID |
name | varchar(20) | Merchant name |
reqKey | varchar(50) | Request key for signing/encryption |
resKey | varchar(50) | Response key for signing/encryption |
Sources: project-datamodel/zheng.sql:500-507
Summary
The zheng database schema is well-structured and modular, reflecting the microservice architecture of the application. Each service domain (UPMS, CMS, UCENTER, PAY) has its own set of tables, clearly delineated by prefixes. Relationships are established through foreign keys, and the schema is consistently defined across the SQL scripts. The use of tools like PowerDesigner and MyBatis Generator indicates a systematic approach to database management and code generation.
Sources: project-datamodel/zheng.sql, project-datamodel/README.md, zheng-cms/zheng-cms-dao/src/main/java/com/zheng/cms/dao/Generator.java