236 lines
7.9 KiB
SQL
236 lines
7.9 KiB
SQL
-- 科技成果转化系统 数据库设计 (PostgreSQL) - 完整版
|
|
|
|
-- 1. 部门表
|
|
CREATE TABLE IF NOT EXISTS departments (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 2. 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL, -- 手机号
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role VARCHAR(20) NOT NULL CHECK (role IN ('super_admin', 'admin', 'user', 'maintainer', 'senior_user', 'intermediate_user')),
|
|
real_name VARCHAR(100),
|
|
department VARCHAR(100), -- 所属部门名称
|
|
dept_status VARCHAR(20) DEFAULT 'verified', -- 部门审核状态
|
|
token_version INTEGER DEFAULT 0, -- Token 版本号 (用于单点登录)
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 3. 成果基础信息表 (所有类型共用)
|
|
CREATE TABLE IF NOT EXISTS achievements (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
type VARCHAR(50) NOT NULL, -- 'paper', 'project', 'award', 'standard', 'monograph', 'report', 'plan', 'patent', 'transformation', 'software'
|
|
name TEXT NOT NULL, -- 成果名称
|
|
main_contributors TEXT, -- 主要完成人 (字符串格式，兼容旧数据)
|
|
all_contributors TEXT, -- 完成人 (字符串格式，兼容旧数据)
|
|
contributor_phones TEXT, -- 完成人手机号 (字符串格式)
|
|
contributors JSONB DEFAULT '[]', -- 结构化完成人列表 [{name, phone, isMain}]
|
|
assigned_departments TEXT[] DEFAULT '{}', -- 归属部门数组 (自动计算)
|
|
achievement_date DATE, -- 时间
|
|
remarks TEXT, -- 备注
|
|
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
|
|
audit_comment TEXT, -- 审核意见
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 4. 论文详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_paper (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
paper_type TEXT,
|
|
first_unit TEXT,
|
|
journal_name TEXT,
|
|
publish_date DATE,
|
|
doi VARCHAR(100)
|
|
);
|
|
|
|
-- 5. 项目详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_project (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
project_category TEXT,
|
|
source TEXT
|
|
);
|
|
|
|
-- 6. 奖励详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_award (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
award_type TEXT,
|
|
award_level TEXT,
|
|
award_unit TEXT
|
|
);
|
|
|
|
-- 7. 标准详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_standard (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
standard_type TEXT,
|
|
standard_no VARCHAR(100) NOT NULL,
|
|
implement_date DATE
|
|
);
|
|
|
|
-- 8. 专著详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_monograph (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
publisher TEXT,
|
|
isbn VARCHAR(50)
|
|
);
|
|
|
|
-- 9. 技术报告详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_report (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
report_type TEXT,
|
|
recipient TEXT,
|
|
approver_id INTEGER REFERENCES users(id), -- 签批院领导ID
|
|
approver_name VARCHAR(100) -- 签批院领导姓名 (冗余存储，方便显示)
|
|
);
|
|
|
|
-- 10. 规划详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_plan (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 11. 专利详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_patent (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
patent_no VARCHAR(100),
|
|
patent_type TEXT,
|
|
assignee TEXT
|
|
);
|
|
|
|
-- 12. 成果转化详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_transformation (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
trans_method TEXT,
|
|
trans_amount NUMERIC(15, 2),
|
|
project_category TEXT
|
|
);
|
|
|
|
-- 13. 软著详情表
|
|
CREATE TABLE IF NOT EXISTS achievement_software (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
reg_no VARCHAR(100),
|
|
acquisition_method TEXT,
|
|
scope TEXT,
|
|
owner_unit TEXT
|
|
);
|
|
|
|
-- 14. 附件表
|
|
CREATE TABLE IF NOT EXISTS achievement_attachments (
|
|
id SERIAL PRIMARY KEY,
|
|
achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
|
|
file_name TEXT NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 15. 字典表 (由超级管理员管理)
|
|
|
|
-- 单位名称表
|
|
CREATE TABLE IF NOT EXISTS dict_organizations (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 奖励种类表
|
|
CREATE TABLE IF NOT EXISTS dict_award_types (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 奖励等级表
|
|
CREATE TABLE IF NOT EXISTS dict_award_levels (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 论文种类表
|
|
CREATE TABLE IF NOT EXISTS dict_paper_types (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 标准种类表
|
|
CREATE TABLE IF NOT EXISTS dict_standard_types (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 项目分类表
|
|
CREATE TABLE IF NOT EXISTS dict_project_categories (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 领导层部门表 (用于筛选签批领导)
|
|
CREATE TABLE IF NOT EXISTS dict_leader_departments (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 16. 审计日志表
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id),
|
|
username VARCHAR(50),
|
|
real_name VARCHAR(50),
|
|
ip_address VARCHAR(50),
|
|
method VARCHAR(10),
|
|
url TEXT,
|
|
description VARCHAR(255),
|
|
status INTEGER,
|
|
duration INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 17. 系统公告表
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id SERIAL PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
publisher_id INTEGER REFERENCES users(id),
|
|
publisher_name VARCHAR(100),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
is_top BOOLEAN DEFAULT FALSE,
|
|
status VARCHAR(20) DEFAULT 'published' -- published, draft, archived
|
|
);
|
|
|
|
-- 18. 系统公告附件表
|
|
CREATE TABLE IF NOT EXISTS notification_attachments (
|
|
id SERIAL PRIMARY KEY,
|
|
notification_id INTEGER REFERENCES notifications(id) ON DELETE CASCADE,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_path VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 索引优化
|
|
CREATE INDEX IF NOT EXISTS idx_achievements_user ON achievements(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_achievements_type ON achievements(type);
|
|
CREATE INDEX IF NOT EXISTS idx_achievements_status ON achievements(status);
|
|
CREATE INDEX IF NOT EXISTS idx_achievements_assigned_depts ON achievements USING GIN (assigned_departments);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_username ON audit_logs(username);
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at DESC);
|