数据库设计
数据库概述
本资产管理系统采用关系型数据库设计，基于PostgreSQL 13+构建，支持MySQL 8.0+和MariaDB 10.6+兼容。数据库设计遵循第三范式，确保数据一致性和完整性，支持高并发访问和复杂查询。
数据库关系图
1. 核心实体关系图
erDiagram
Asset ||--o{ Image : "has many"
Asset ||--o{ Video : "has many"
Asset ||--o{ Attachment : "has many"
Asset }o--|| Category : "belongs to"
Asset }o--|| Organization : "belongs to"
Asset }o--o{ Tag : "has many"
Asset {
int id PK
string name
string spec
string model
int value
int depreciation
string vendor
datetime purchased_at
int status
string code
string coords
string address
int organization_id FK
int category_id FK
}
Category {
int id PK
string name
string label
string code
int tid
}
Organization {
int id PK
string name
int parent_id FK
}
Image {
int id PK
int asset_id FK
string image
string title
int file_size
int width
int height
}
Video {
int id PK
int asset_id FK
string video
string title
string video_format
int duration
int width
int height
}
Attachment {
int id PK
int asset_id FK
string attachment
string title
string file_type
int file_size
}
Tag {
int id PK
string name
string label
string color
}
2. 业务流程关系图
erDiagram
Asset ||--o{ Lease : "has many"
Asset ||--o{ Borrow : "has many"
Asset ||--o{ Transfer : "has many"
Asset ||--o{ MaintenanceApplication : "has many"
Asset ||--o{ MaintenancePlan : "has many"
Asset ||--o{ MaintenanceRecord : "has many"
Asset ||--o{ DisposalApplication : "has many"
Organization ||--o{ Transfer : "from/to"
Asset {
int id PK
string name
int status
int organization_id FK
}
Lease {
int id PK
int asset_id FK
string lessee_name
string lessee_company
datetime lease_start_date
datetime lease_end_date
int status
}
Borrow {
int id PK
int asset_id FK
string borrower_name
string borrower_department
datetime borrow_start_date
datetime borrow_end_date
int status
}
Transfer {
int id PK
int asset_id FK
int from_organization_id FK
int to_organization_id FK
text transfer_reason
int status
}
MaintenanceApplication {
int id PK
int asset_id FK
string applicant_name
string maintenance_type
int priority
text description
int status
}
MaintenancePlan {
int id PK
int asset_id FK
string plan_name
string plan_type
string frequency
datetime next_maintenance_date
int status
}
MaintenanceRecord {
int id PK
int asset_id FK
int maintenance_application_id FK
int maintenance_plan_id FK
datetime maintenance_date
string maintenance_type
text maintenance_result
}
DisposalApplication {
int id PK
int asset_id FK
string application_number
string applicant_name
text disposal_reason
string disposal_method
int status
}
3. 维护管理关系图
erDiagram
MaintenanceApplication ||--o{ MaintenanceRecord : "generates"
MaintenancePlan ||--o{ MaintenanceRecord : "generates"
Asset ||--o{ MaintenanceApplication : "has many"
Asset ||--o{ MaintenancePlan : "has many"
Asset ||--o{ MaintenanceRecord : "has many"
MaintenanceApplication {
int id PK
int asset_id FK
string applicant_name
string applicant_department
string maintenance_type
int priority
text description
datetime expected_date
int maintenance_cost
int status
}
MaintenancePlan {
int id PK
int asset_id FK
string plan_name
string plan_type
string frequency
datetime next_maintenance_date
datetime last_maintenance_date
text maintenance_items
string responsible_person
int status
}
MaintenanceRecord {
int id PK
int asset_id FK
int maintenance_application_id FK
int maintenance_plan_id FK
datetime maintenance_date
string maintenance_type
string maintenance_person
text maintenance_items
text maintenance_result
int maintenance_cost
}
4. 采购管理关系图
erDiagram
PurchaseContract ||--o{ PurchaseAcceptance : "has many"
User ||--o{ PurchasePlan : "creates"
User ||--o{ PurchaseContract : "creates"
PurchasePlan {
int id PK
string plan_name
int plan_year
int plan_quarter
int total_budget
int used_budget
int status
int created_by FK
}
PurchaseContract {
int id PK
string contract_number
string contract_name
string supplier_name
string supplier_contact
int contract_amount
datetime contract_date
datetime delivery_date
text payment_terms
int status
int created_by FK
}
PurchaseAcceptance {
int id PK
int contract_id FK
string acceptance_number
datetime acceptance_date
string acceptance_person
text acceptance_result
int status
}
User {
int id PK
string username
string email
string first_name
string last_name
}
5. 处置管理关系图
erDiagram
DisposalApplication ||--o{ DisposalAppraisal : "has many"
Asset ||--o{ DisposalApplication : "has many"
DisposalApplication {
int id PK
int asset_id FK
string application_number
string applicant_name
string applicant_department
text disposal_reason
string disposal_method
datetime disposal_date
int disposal_value
int status
}
DisposalAppraisal {
int id PK
int application_id FK
string appraiser_name
datetime appraisal_date
int appraisal_value
string appraisal_method
text appraisal_result
int status
}
6. 用户权限关系图
erDiagram
User ||--o{ UserRole : "has many"
User ||--o{ AuditLog : "creates"
Role ||--o{ UserRole : "assigned to"
Role ||--o{ RolePermission : "has many"
Permission ||--o{ RolePermission : "granted to"
Organization ||--o{ User : "contains"
User {
int id PK
string username
string email
string first_name
string last_name
string phone
int organization_id FK
boolean is_active
}
Role {
int id PK
string name
string label
text description
boolean is_system
}
Permission {
int id PK
string name
string label
text description
string resource
string action
}
UserRole {
int user_id FK
int role_id FK
}
RolePermission {
int role_id FK
int permission_id FK
}
AuditLog {
int id PK
int user_id FK
string entity_type
int entity_id
string action
json old_values
json new_values
}
7. 组织架构关系图
erDiagram
Organization ||--o{ Organization : "parent-child"
Organization ||--o{ Asset : "owns"
Organization ||--o{ User : "contains"
Organization ||--o{ Transfer : "from/to"
Organization {
int id PK
string name
int parent_id FK
datetime created_at
}
Asset {
int id PK
string name
string code
int status
int organization_id FK
}
User {
int id PK
string username
string email
string first_name
string last_name
int organization_id FK
}
Transfer {
int id PK
int asset_id FK
int from_organization_id FK
int to_organization_id FK
text transfer_reason
int status
}
核心表结构
1. 资产表 (asset)
CREATE TABLE asset (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL COMMENT '资产名称',
spec VARCHAR(255) COMMENT '规格型号',
model VARCHAR(255) COMMENT '型号',
value INTEGER COMMENT '资产价值（分）',
depreciation INTEGER COMMENT '累计折旧（分）',
vendor VARCHAR(255) COMMENT '供应商',
purchased_at TIMESTAMP COMMENT '采购日期',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
status SMALLINT DEFAULT 0 COMMENT '状态：0新增，10闲置，20在用，30报废，40维修',
code VARCHAR(48) COMMENT '资产编码',
coords VARCHAR(255) COMMENT '坐标信息',
address VARCHAR(255) COMMENT '地址',
cert VARCHAR(255) COMMENT '证书编号',
url VARCHAR(255) COMMENT '相关链接',
note TEXT COMMENT '备注',
organization_id INTEGER NOT NULL COMMENT '所属组织ID',
category_id INTEGER NOT NULL COMMENT '分类ID',
INDEX idx_asset_organization (organization_id),
INDEX idx_asset_category (category_id),
INDEX idx_asset_status (status),
INDEX idx_asset_code (code),
INDEX idx_asset_created_at (created_at),
FOREIGN KEY (organization_id) REFERENCES organization(id),
FOREIGN KEY (category_id) REFERENCES category(id)
);
2. 分类表 (category)
CREATE TABLE category (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL COMMENT '分类名称',
label VARCHAR(255) NOT NULL COMMENT '分类标签',
code VARCHAR(6) UNIQUE NOT NULL COMMENT '分类编码',
tid SMALLINT DEFAULT 1 COMMENT '类型ID：1有形资产，2无形资产',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_category_tid (tid),
INDEX idx_category_code (code),
CONSTRAINT chk_category_code CHECK (code ~ '^[A-Z]+$')
);
3. 组织表 (organization)
CREATE TABLE organization (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL COMMENT '组织名称',
parent_id INTEGER COMMENT '父组织ID',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_organization_parent (parent_id),
FOREIGN KEY (parent_id) REFERENCES organization(id)
);
4. 用户表 (user)
CREATE TABLE user (
id SERIAL PRIMARY KEY,
username VARCHAR(180) UNIQUE NOT NULL COMMENT '用户名',
email VARCHAR(255) UNIQUE NOT NULL COMMENT '邮箱',
password VARCHAR(255) NOT NULL COMMENT '密码哈希',
first_name VARCHAR(255) COMMENT '名字',
last_name VARCHAR(255) COMMENT '姓氏',
phone VARCHAR(20) COMMENT '电话',
organization_id INTEGER COMMENT '所属组织ID',
is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
last_login_at TIMESTAMP COMMENT '最后登录时间',
INDEX idx_user_organization (organization_id),
INDEX idx_user_email (email),
INDEX idx_user_active (is_active),
FOREIGN KEY (organization_id) REFERENCES organization(id)
);
5. 标签表 (tag)
CREATE TABLE tag (
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL COMMENT '标签名称',
label VARCHAR(255) NOT NULL COMMENT '标签显示名称',
color VARCHAR(7) DEFAULT '#007bff' COMMENT '标签颜色',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_tag_name (name)
);
多媒体表结构
6. 图片表 (image)
CREATE TABLE image (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
image VARCHAR(255) NOT NULL COMMENT '图片文件名',
title VARCHAR(255) COMMENT '图片标题',
description TEXT COMMENT '图片描述',
file_size INTEGER COMMENT '文件大小（字节）',
width INTEGER COMMENT '图片宽度',
height INTEGER COMMENT '图片高度',
mime_type VARCHAR(100) COMMENT 'MIME类型',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_image_asset (asset_id),
INDEX idx_image_created_at (created_at),
FOREIGN KEY (asset_id) REFERENCES asset(id) ON DELETE CASCADE
);
7. 视频表 (video)
CREATE TABLE video (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
video VARCHAR(255) NOT NULL COMMENT '视频文件名',
title VARCHAR(255) COMMENT '视频标题',
description TEXT COMMENT '视频描述',
video_format VARCHAR(50) COMMENT '视频格式',
file_size INTEGER COMMENT '文件大小（字节）',
duration INTEGER COMMENT '视频时长（秒）',
width INTEGER COMMENT '视频宽度',
height INTEGER COMMENT '视频高度',
thumbnail VARCHAR(255) COMMENT '缩略图文件名',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_video_asset (asset_id),
INDEX idx_video_format (video_format),
INDEX idx_video_created_at (created_at),
FOREIGN KEY (asset_id) REFERENCES asset(id) ON DELETE CASCADE
);
8. 附件表 (attachment)
CREATE TABLE attachment (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
attachment VARCHAR(255) NOT NULL COMMENT '附件文件名',
title VARCHAR(255) COMMENT '附件标题',
description TEXT COMMENT '附件描述',
file_type VARCHAR(100) COMMENT '文件类型',
file_size INTEGER COMMENT '文件大小（字节）',
mime_type VARCHAR(100) COMMENT 'MIME类型',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_attachment_asset (asset_id),
INDEX idx_attachment_file_type (file_type),
INDEX idx_attachment_created_at (created_at),
FOREIGN KEY (asset_id) REFERENCES asset(id) ON DELETE CASCADE
);
业务流程表结构
9. 租赁表 (lease)
CREATE TABLE lease (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
lessee_name VARCHAR(255) NOT NULL COMMENT '承租人姓名',
lessee_company VARCHAR(255) COMMENT '承租人公司',
lessee_contact VARCHAR(255) COMMENT '联系人',
lessee_phone VARCHAR(255) COMMENT '联系电话',
lessee_email VARCHAR(255) COMMENT '联系邮箱',
lessee_address VARCHAR(255) COMMENT '联系地址',
lease_start_date TIMESTAMP NOT NULL COMMENT '租赁开始日期',
lease_end_date TIMESTAMP NOT NULL COMMENT '租赁结束日期',
actual_return_date TIMESTAMP COMMENT '实际归还日期',
monthly_rent INTEGER COMMENT '月租金（分）',
security_deposit INTEGER COMMENT '押金（分）',
lease_terms TEXT COMMENT '租赁条款',
purpose TEXT COMMENT '租赁用途',
notes TEXT COMMENT '备注',
location VARCHAR(255) COMMENT '租赁位置',
status SMALLINT DEFAULT 0 COMMENT '状态：0待审批，10已批准，20租赁中，30已到期，40已归还，50已取消',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_lease_asset (asset_id),
INDEX idx_lease_status (status),
INDEX idx_lease_dates (lease_start_date, lease_end_date),
FOREIGN KEY (asset_id) REFERENCES asset(id)
);
10. 借用表 (borrow)
CREATE TABLE borrow (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
borrower_name VARCHAR(255) NOT NULL COMMENT '借用人姓名',
borrower_department VARCHAR(255) COMMENT '借用人部门',
borrower_contact VARCHAR(255) COMMENT '联系方式',
borrow_start_date TIMESTAMP NOT NULL COMMENT '借用开始日期',
borrow_end_date TIMESTAMP NOT NULL COMMENT '借用结束日期',
actual_return_date TIMESTAMP COMMENT '实际归还日期',
purpose TEXT COMMENT '借用用途',
notes TEXT COMMENT '备注',
status SMALLINT DEFAULT 0 COMMENT '状态：0待审批，10已批准，20借用中，30已归还，40已取消',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_borrow_asset (asset_id),
INDEX idx_borrow_status (status),
INDEX idx_borrow_dates (borrow_start_date, borrow_end_date),
FOREIGN KEY (asset_id) REFERENCES asset(id)
);
11. 调拨表 (transfer)
CREATE TABLE transfer (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
from_organization_id INTEGER COMMENT '调出组织ID',
to_organization_id INTEGER NOT NULL COMMENT '调入组织ID',
transfer_reason TEXT COMMENT '调拨原因',
transfer_date TIMESTAMP NOT NULL COMMENT '调拨日期',
actual_transfer_date TIMESTAMP COMMENT '实际调拨日期',
notes TEXT COMMENT '备注',
status SMALLINT DEFAULT 0 COMMENT '状态：0待审批，10已批准，20调拨中，30已完成，40已取消',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_transfer_asset (asset_id),
INDEX idx_transfer_from_org (from_organization_id),
INDEX idx_transfer_to_org (to_organization_id),
INDEX idx_transfer_status (status),
FOREIGN KEY (asset_id) REFERENCES asset(id),
FOREIGN KEY (from_organization_id) REFERENCES organization(id),
FOREIGN KEY (to_organization_id) REFERENCES organization(id)
);
维护管理表结构
12. 维护申请表 (maintenance_application)
CREATE TABLE maintenance_application (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
applicant_name VARCHAR(255) NOT NULL COMMENT '申请人姓名',
applicant_department VARCHAR(255) COMMENT '申请人部门',
applicant_contact VARCHAR(255) COMMENT '联系方式',
maintenance_type VARCHAR(100) COMMENT '维护类型',
priority SMALLINT DEFAULT 1 COMMENT '优先级：1低，2中，3高，4紧急',
description TEXT NOT NULL COMMENT '问题描述',
expected_date TIMESTAMP COMMENT '期望维护日期',
actual_date TIMESTAMP COMMENT '实际维护日期',
maintenance_cost INTEGER COMMENT '维护费用（分）',
status SMALLINT DEFAULT 0 COMMENT '状态：0待审批，10已批准，20进行中，30已完成，40已取消',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_maintenance_asset (asset_id),
INDEX idx_maintenance_status (status),
INDEX idx_maintenance_priority (priority),
FOREIGN KEY (asset_id) REFERENCES asset(id)
);
13. 维护计划表 (maintenance_plan)
CREATE TABLE maintenance_plan (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
plan_name VARCHAR(255) NOT NULL COMMENT '计划名称',
plan_type VARCHAR(100) COMMENT '计划类型',
frequency VARCHAR(50) COMMENT '维护频率',
next_maintenance_date TIMESTAMP COMMENT '下次维护日期',
last_maintenance_date TIMESTAMP COMMENT '上次维护日期',
maintenance_items TEXT COMMENT '维护项目',
responsible_person VARCHAR(255) COMMENT '负责人',
status SMALLINT DEFAULT 1 COMMENT '状态：0停用，1启用',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_plan_asset (asset_id),
INDEX idx_plan_status (status),
INDEX idx_plan_next_date (next_maintenance_date),
FOREIGN KEY (asset_id) REFERENCES asset(id)
);
14. 维护记录表 (maintenance_record)
CREATE TABLE maintenance_record (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
maintenance_application_id INTEGER COMMENT '维护申请ID',
maintenance_plan_id INTEGER COMMENT '维护计划ID',
maintenance_date TIMESTAMP NOT NULL COMMENT '维护日期',
maintenance_type VARCHAR(100) COMMENT '维护类型',
maintenance_person VARCHAR(255) COMMENT '维护人员',
maintenance_items TEXT COMMENT '维护项目',
maintenance_result TEXT COMMENT '维护结果',
maintenance_cost INTEGER COMMENT '维护费用（分）',
notes TEXT COMMENT '备注',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_record_asset (asset_id),
INDEX idx_record_application (maintenance_application_id),
INDEX idx_record_plan (maintenance_plan_id),
INDEX idx_record_date (maintenance_date),
FOREIGN KEY (asset_id) REFERENCES asset(id),
FOREIGN KEY (maintenance_application_id) REFERENCES maintenance_application(id),
FOREIGN KEY (maintenance_plan_id) REFERENCES maintenance_plan(id)
);
采购管理表结构
15. 采购计划表 (purchase_plan)
CREATE TABLE purchase_plan (
id SERIAL PRIMARY KEY,
plan_name VARCHAR(255) NOT NULL COMMENT '计划名称',
plan_year INTEGER NOT NULL COMMENT '计划年度',
plan_quarter INTEGER COMMENT '计划季度',
total_budget INTEGER COMMENT '总预算（分）',
used_budget INTEGER DEFAULT 0 COMMENT '已用预算（分）',
status SMALLINT DEFAULT 0 COMMENT '状态：0草稿，1待审批，2已批准，3执行中，4已完成',
created_by INTEGER COMMENT '创建人ID',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_plan_year (plan_year),
INDEX idx_plan_status (status),
INDEX idx_plan_created_by (created_by),
FOREIGN KEY (created_by) REFERENCES user(id)
);
16. 采购合同表 (purchase_contract)
CREATE TABLE purchase_contract (
id SERIAL PRIMARY KEY,
contract_number VARCHAR(255) UNIQUE NOT NULL COMMENT '合同编号',
contract_name VARCHAR(255) NOT NULL COMMENT '合同名称',
supplier_name VARCHAR(255) NOT NULL COMMENT '供应商名称',
supplier_contact VARCHAR(255) COMMENT '供应商联系方式',
contract_amount INTEGER NOT NULL COMMENT '合同金额（分）',
contract_date TIMESTAMP NOT NULL COMMENT '合同日期',
delivery_date TIMESTAMP COMMENT '交付日期',
payment_terms TEXT COMMENT '付款条款',
contract_terms TEXT COMMENT '合同条款',
status SMALLINT DEFAULT 0 COMMENT '状态：0草稿，1待审批，2已批准，3执行中，4已完成，5已取消',
created_by INTEGER COMMENT '创建人ID',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_contract_number (contract_number),
INDEX idx_contract_status (status),
INDEX idx_contract_supplier (supplier_name),
INDEX idx_contract_date (contract_date),
FOREIGN KEY (created_by) REFERENCES user(id)
);
17. 采购验收表 (purchase_acceptance)
CREATE TABLE purchase_acceptance (
id SERIAL PRIMARY KEY,
contract_id INTEGER NOT NULL COMMENT '合同ID',
acceptance_number VARCHAR(255) UNIQUE NOT NULL COMMENT '验收编号',
acceptance_date TIMESTAMP NOT NULL COMMENT '验收日期',
acceptance_person VARCHAR(255) COMMENT '验收人员',
acceptance_result TEXT COMMENT '验收结果',
acceptance_notes TEXT COMMENT '验收备注',
status SMALLINT DEFAULT 0 COMMENT '状态：0待验收，1验收通过，2验收不通过',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_acceptance_contract (contract_id),
INDEX idx_acceptance_status (status),
INDEX idx_acceptance_date (acceptance_date),
FOREIGN KEY (contract_id) REFERENCES purchase_contract(id)
);
处置管理表结构
18. 处置申请表 (disposal_application)
CREATE TABLE disposal_application (
id SERIAL PRIMARY KEY,
asset_id INTEGER NOT NULL COMMENT '资产ID',
application_number VARCHAR(255) UNIQUE NOT NULL COMMENT '申请编号',
applicant_name VARCHAR(255) NOT NULL COMMENT '申请人姓名',
applicant_department VARCHAR(255) COMMENT '申请人部门',
applicant_contact VARCHAR(255) COMMENT '联系方式',
disposal_reason TEXT NOT NULL COMMENT '处置原因',
disposal_method VARCHAR(100) COMMENT '处置方式',
disposal_date TIMESTAMP COMMENT '处置日期',
disposal_value INTEGER COMMENT '处置价值（分）',
status SMALLINT DEFAULT 0 COMMENT '状态：0待审批，10已批准，20处置中，30已完成，40已取消',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_disposal_asset (asset_id),
INDEX idx_disposal_status (status),
INDEX idx_disposal_date (disposal_date),
FOREIGN KEY (asset_id) REFERENCES asset(id)
);
19. 处置评估表 (disposal_appraisal)
CREATE TABLE disposal_appraisal (
id SERIAL PRIMARY KEY,
application_id INTEGER NOT NULL COMMENT '申请ID',
appraiser_name VARCHAR(255) NOT NULL COMMENT '评估人姓名',
appraisal_date TIMESTAMP NOT NULL COMMENT '评估日期',
appraisal_value INTEGER COMMENT '评估价值（分）',
appraisal_method VARCHAR(100) COMMENT '评估方法',
appraisal_result TEXT COMMENT '评估结果',
appraisal_notes TEXT COMMENT '评估备注',
status SMALLINT DEFAULT 0 COMMENT '状态：0待评估，1评估完成，2评估不通过',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_appraisal_application (application_id),
INDEX idx_appraisal_status (status),
INDEX idx_appraisal_date (appraisal_date),
FOREIGN KEY (application_id) REFERENCES disposal_application(id)
);
关联表结构
20. 资产标签关联表 (asset_tag)
CREATE TABLE asset_tag (
asset_id INTEGER NOT NULL COMMENT '资产ID',
tag_id INTEGER NOT NULL COMMENT '标签ID',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (asset_id, tag_id),
INDEX idx_asset_tag_asset (asset_id),
INDEX idx_asset_tag_tag (tag_id),
FOREIGN KEY (asset_id) REFERENCES asset(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE
);
系统管理表结构
21. 角色表 (role)
CREATE TABLE role (
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL COMMENT '角色名称',
label VARCHAR(255) NOT NULL COMMENT '角色显示名称',
description TEXT COMMENT '角色描述',
is_system BOOLEAN DEFAULT FALSE COMMENT '是否系统角色',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_role_name (name),
INDEX idx_role_system (is_system)
);
22. 权限表 (permission)
CREATE TABLE permission (
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL COMMENT '权限名称',
label VARCHAR(255) NOT NULL COMMENT '权限显示名称',
description TEXT COMMENT '权限描述',
resource VARCHAR(255) COMMENT '资源名称',
action VARCHAR(255) COMMENT '操作名称',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_permission_name (name),
INDEX idx_permission_resource (resource)
);
23. 用户角色关联表 (user_role)
CREATE TABLE user_role (
user_id INTEGER NOT NULL COMMENT '用户ID',
role_id INTEGER NOT NULL COMMENT '角色ID',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (user_id, role_id),
INDEX idx_user_role_user (user_id),
INDEX idx_user_role_role (role_id),
FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE
);
24. 角色权限关联表 (role_permission)
CREATE TABLE role_permission (
role_id INTEGER NOT NULL COMMENT '角色ID',
permission_id INTEGER NOT NULL COMMENT '权限ID',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (role_id, permission_id),
INDEX idx_role_permission_role (role_id),
INDEX idx_role_permission_permission (permission_id),
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
FOREIGN KEY (permission_id) REFERENCES permission(id) ON DELETE CASCADE
);
审计日志表结构
25. 审计日志表 (audit_log)
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
user_id INTEGER COMMENT '用户ID',
entity_type VARCHAR(255) NOT NULL COMMENT '实体类型',
entity_id INTEGER NOT NULL COMMENT '实体ID',
action VARCHAR(50) NOT NULL COMMENT '操作类型',
old_values JSON COMMENT '旧值',
new_values JSON COMMENT '新值',
ip_address VARCHAR(45) COMMENT 'IP地址',
user_agent TEXT COMMENT '用户代理',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_audit_user (user_id),
INDEX idx_audit_entity (entity_type, entity_id),
INDEX idx_audit_action (action),
INDEX idx_audit_created_at (created_at),
FOREIGN KEY (user_id) REFERENCES user(id)
);
数据库索引策略
主要索引
- 主键索引: 所有表的主键自动创建
- 外键索引: 所有外键字段创建索引
- 状态索引: 业务状态字段创建索引
- 时间索引: 创建时间、更新时间字段创建索引
- 业务索引: 编码、名称等业务字段创建索引
复合索引
- 查询优化: 根据常用查询组合创建复合索引
- 排序优化: 支持排序的字段组合创建索引
- 过滤优化: 多条件查询的字段组合创建索引
数据完整性约束
外键约束
- 级联删除: 子表记录随父表删除
- 级联更新: 父表主键更新时子表同步更新
- 限制删除: 有子表记录时禁止删除父表记录
检查约束
- 数据范围: 数值字段的范围检查
- 格式验证: 字符串字段的格式检查
- 枚举值: 状态字段的枚举值检查
唯一约束
- 业务唯一: 编码、名称等业务字段唯一性
- 系统唯一: 用户名、邮箱等系统字段唯一性
总结
本数据库设计采用关系型数据库设计原则，通过合理的表结构设计、索引策略、约束机制，确保了数据的一致性、完整性和查询性能。支持资产全生命周期管理、多媒体档案管理、业务流程管理等核心功能，为系统的高效运行提供了坚实的数据基础。