跳转至

制药企业实验室样品管理系统数据库设计#

项目概述#

本文档为制药企业实验室样品管理系统提供完整的MySQL数据库设计方案，去掉外键约束和触发器，以提高性能和灵活性。

一、数据库创建#

-- 创建数据库
CREATE DATABASE sample_management_system 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE sample_management_system;

-- 设置时区
SET time_zone = '+08:00';

二、用户与权限管理#

2.1 用户表#

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    employee_id VARCHAR(20) UNIQUE,
    phone VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED,
    updated_by BIGINT UNSIGNED,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_department (department),
    INDEX idx_employee_id (employee_id),
    INDEX idx_is_active (is_active),
    INDEX idx_created_by (created_by),
    INDEX idx_updated_by (updated_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2.2 角色表#

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role_name (role_name),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2.3 用户角色关联表#

CREATE TABLE user_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by BIGINT UNSIGNED,
    UNIQUE KEY uk_user_role (user_id, role_id),
    INDEX idx_user_id (user_id),
    INDEX idx_role_id (role_id),
    INDEX idx_assigned_by (assigned_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2.4 权限表#

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    module VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_permission_name (permission_name),
    INDEX idx_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2.5 角色权限关联表#

CREATE TABLE role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by BIGINT UNSIGNED,
    UNIQUE KEY uk_role_permission (role_id, permission_id),
    INDEX idx_role_id (role_id),
    INDEX idx_permission_id (permission_id),
    INDEX idx_assigned_by (assigned_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

三、样品方案管理#

3.1 产品类型表#

CREATE TABLE product_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type_code VARCHAR(20) UNIQUE NOT NULL,
    type_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type_code (type_code),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.2 样品方案表#

CREATE TABLE sample_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_code VARCHAR(50) UNIQUE NOT NULL,
    plan_name VARCHAR(200) NOT NULL,
    product_type_id BIGINT UNSIGNED,
    sampling_config JSON NOT NULL COMMENT '取样配置JSON',
    label_template JSON NOT NULL COMMENT '标签模板JSON',
    request_template JSON NOT NULL COMMENT '请验单模板JSON',
    is_active TINYINT(1) DEFAULT 1,
    version INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED,
    updated_by BIGINT UNSIGNED,
    INDEX idx_plan_code (plan_code),
    INDEX idx_product_type_id (product_type_id),
    INDEX idx_is_active (is_active),
    INDEX idx_created_by (created_by),
    INDEX idx_updated_by (updated_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.3 方案版本历史表#

CREATE TABLE sample_plan_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_id BIGINT UNSIGNED NOT NULL,
    version INT NOT NULL,
    sampling_config JSON NOT NULL,
    label_template JSON NOT NULL,
    request_template JSON NOT NULL,
    change_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED,
    INDEX idx_plan_id_version (plan_id, version),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

四、请验管理#

4.1 请验单表#

CREATE TABLE sample_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_code VARCHAR(50) UNIQUE NOT NULL COMMENT '请验单号',
    batch_number VARCHAR(50) NOT NULL COMMENT '批次号',
    product_code VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    specification VARCHAR(200),
    manufacturer VARCHAR(200),
    supplier VARCHAR(200),
    production_date DATE,
    expiry_date DATE,
    quantity DECIMAL(10,3),
    unit VARCHAR(20),
    purpose TEXT NOT NULL COMMENT '检验目的',
    test_items JSON COMMENT '检验项目JSON',
    requester_id BIGINT UNSIGNED,
    department VARCHAR(50) NOT NULL,
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('PENDING', 'APPROVED', 'REJECTED', 'CANCELLED') DEFAULT 'PENDING',
    approval_date TIMESTAMP NULL,
    approved_by BIGINT UNSIGNED,
    approval_comment TEXT,
    plan_id BIGINT UNSIGNED,
    parent_request_id BIGINT UNSIGNED COMMENT '关联前序请验',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED,
    updated_by BIGINT UNSIGNED,
    INDEX idx_request_code (request_code),
    INDEX idx_batch_number (batch_number),
    INDEX idx_product_code (product_code),
    INDEX idx_status (status),
    INDEX idx_request_date (request_date),
    INDEX idx_requester_id (requester_id),
    INDEX idx_department (department),
    INDEX idx_plan_id (plan_id),
    INDEX idx_parent_request_id (parent_request_id),
    INDEX idx_created_by (created_by),
    INDEX idx_updated_by (updated_by),
    INDEX idx_approved_by (approved_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4.2 请验附件表#

CREATE TABLE request_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(200) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(50),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    uploaded_by BIGINT UNSIGNED,
    INDEX idx_request_id (request_id),
    INDEX idx_upload_date (upload_date),
    INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

五、取样管理#

5.1 取样任务表#

CREATE TABLE sampling_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_code VARCHAR(50) UNIQUE NOT NULL,
    request_id BIGINT UNSIGNED NOT NULL,
    collector_id BIGINT UNSIGNED,
    assigned_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    due_date TIMESTAMP NULL,
    status ENUM('ASSIGNED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') DEFAULT 'ASSIGNED',
    priority ENUM('LOW', 'NORMAL', 'HIGH', 'URGENT') DEFAULT 'NORMAL',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED,
    INDEX idx_task_code (task_code),
    INDEX idx_request_id (request_id),
    INDEX idx_collector_id (collector_id),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

5.2 取样记录表#

CREATE TABLE sample_collections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    collection_code VARCHAR(50) UNIQUE NOT NULL,
    request_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED,
    collector_id BIGINT UNSIGNED,
    collection_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity DECIMAL(10,3) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    container_type VARCHAR(100),
    container_quantity INT,
    container_identifier VARCHAR(100),
    sampling_location VARCHAR(200),
    environmental_conditions JSON COMMENT '环境条件JSON',
    status ENUM('COLLECTED', 'PENDING_RECEIPT', 'RECEIVED') DEFAULT 'COLLECTED',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_collection_code (collection_code),
    INDEX idx_request_id (request_id),
    INDEX idx_task_id (task_id),
    INDEX idx_collector_id (collector_id),
    INDEX idx_collection_date (collection_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

5.3 取样附件表#

CREATE TABLE collection_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    collection_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(200) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(50),
    attachment_type ENUM('PHOTO', 'DOCUMENT', 'VIDEO') DEFAULT 'DOCUMENT',
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    uploaded_by BIGINT UNSIGNED,
    INDEX idx_collection_id (collection_id),
    INDEX idx_attachment_type (attachment_type),
    INDEX idx_upload_date (upload_date),
    INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

六、样品接收#

6.1 样品接收表#

CREATE TABLE sample_receipts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    receipt_code VARCHAR(50) UNIQUE NOT NULL,
    collection_id BIGINT UNSIGNED NOT NULL,
    receiver_id BIGINT UNSIGNED,
    receipt_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    storage_location VARCHAR(200),
    storage_area VARCHAR(100),
    shelf_position VARCHAR(100),
    container_info JSON COMMENT '容器信息JSON',
    receipt_conditions JSON COMMENT '接收条件JSON',
    status ENUM('RECEIVED', 'STORED', 'DISPOSED') DEFAULT 'RECEIVED',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_receipt_code (receipt_code),
    INDEX idx_collection_id (collection_id),
    INDEX idx_receiver_id (receiver_id),
    INDEX idx_receipt_date (receipt_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

6.2 接收附件表#

CREATE TABLE receipt_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    receipt_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(200) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(50),
    attachment_type ENUM('PHOTO', 'DOCUMENT', 'VIDEO') DEFAULT 'DOCUMENT',
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    uploaded_by BIGINT UNSIGNED,
    INDEX idx_receipt_id (receipt_id),
    INDEX idx_attachment_type (attachment_type),
    INDEX idx_upload_date (upload_date),
    INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

七、样品库存管理#

7.1 样品库存表#

CREATE TABLE sample_inventory (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inventory_code VARCHAR(50) UNIQUE NOT NULL,
    receipt_id BIGINT UNSIGNED NOT NULL,
    sample_name VARCHAR(200) NOT NULL,
    batch_number VARCHAR(50) NOT NULL,
    manufacturer VARCHAR(200),
    supplier VARCHAR(200),
    production_date DATE,
    expiry_date DATE,
    storage_condition VARCHAR(200),
    storage_location VARCHAR(200),
    current_quantity DECIMAL(10,3) NOT NULL,
    original_quantity DECIMAL(10,3) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    status ENUM('AVAILABLE', 'IN_USE', 'DISPOSED', 'EXPIRED') DEFAULT 'AVAILABLE',
    quality_status ENUM('GOOD', 'SUSPECT', 'REJECTED') DEFAULT 'GOOD',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_inventory_code (inventory_code),
    INDEX idx_batch_number (batch_number),
    INDEX idx_sample_name (sample_name),
    INDEX idx_status (status),
    INDEX idx_expiry_date (expiry_date),
    INDEX idx_storage_location (storage_location),
    INDEX idx_quality_status (quality_status),
    INDEX idx_receipt_id (receipt_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

7.2 样品领用表#

CREATE TABLE sample_issues (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    issue_code VARCHAR(50) UNIQUE NOT NULL,
    inventory_id BIGINT UNSIGNED NOT NULL,
    requester_id BIGINT UNSIGNED,
    issuer_id BIGINT UNSIGNED,
    issue_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity DECIMAL(10,3) NOT NULL,
    purpose TEXT NOT NULL,
    expected_return_date DATE,
    actual_return_date DATE,
    status ENUM('ISSUED', 'RETURNED', 'OVERDUE') DEFAULT 'ISSUED',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_issue_code (issue_code),
    INDEX idx_inventory_id (inventory_id),
    INDEX idx_requester_id (requester_id),
    INDEX idx_issuer_id (issuer_id),
    INDEX idx_issue_date (issue_date),
    INDEX idx_status (status),
    INDEX idx_expected_return_date (expected_return_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

7.3 样品归还表#

CREATE TABLE sample_returns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    return_code VARCHAR(50) UNIQUE NOT NULL,
    issue_id BIGINT UNSIGNED NOT NULL,
    returner_id BIGINT UNSIGNED,
    receiver_id BIGINT UNSIGNED,
    return_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    returned_quantity DECIMAL(10,3) NOT NULL,
    condition_status ENUM('GOOD', 'DAMAGED', 'CONTAMINATED') DEFAULT 'GOOD',
    return_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_return_code (return_code),
    INDEX idx_issue_id (issue_id),
    INDEX idx_returner_id (returner_id),
    INDEX idx_receiver_id (receiver_id),
    INDEX idx_return_date (return_date),
    INDEX idx_condition_status (condition_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

7.4 样品销毁表#

CREATE TABLE sample_disposals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    disposal_code VARCHAR(50) UNIQUE NOT NULL,
    inventory_id BIGINT UNSIGNED NOT NULL,
    disposal_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    disposal_quantity DECIMAL(10,3) NOT NULL,
    disposal_method VARCHAR(100) NOT NULL,
    disposal_reason TEXT NOT NULL,
    approver_id BIGINT UNSIGNED,
    executor_id BIGINT UNSIGNED,
    status ENUM('PENDING', 'APPROVED', 'COMPLETED') DEFAULT 'PENDING',
    approval_date TIMESTAMP NULL,
    completion_date TIMESTAMP NULL,
    disposal_certificate_path VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED,
    INDEX idx_disposal_code (disposal_code),
    INDEX idx_inventory_id (inventory_id),
    INDEX idx_status (status),
    INDEX idx_disposal_date (disposal_date),
    INDEX idx_approver_id (approver_id),
    INDEX idx_executor_id (executor_id),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

八、电子签名和审计#

8.1 电子签名表#

CREATE TABLE electronic_signatures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    signature_code VARCHAR(50) UNIQUE NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    operation_type ENUM('CREATE', 'UPDATE', 'DELETE', 'APPROVE') NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    record_id BIGINT UNSIGNED NOT NULL,
    signature_data JSON NOT NULL COMMENT '签名数据JSON',
    reason TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    signature_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_signature_code (signature_code),
    INDEX idx_user_id (user_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_table_name (table_name),
    INDEX idx_signature_date (signature_date),
    INDEX idx_record_id (record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

8.2 系统配置表#

CREATE TABLE system_configurations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    config_type ENUM('STRING', 'NUMBER', 'BOOLEAN', 'JSON') DEFAULT 'STRING',
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by BIGINT UNSIGNED,
    INDEX idx_config_key (config_key),
    INDEX idx_is_active (is_active),
    INDEX idx_updated_by (updated_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

8.3 批次编号规则表#

CREATE TABLE batch_number_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    rule_pattern VARCHAR(200) NOT NULL COMMENT '规则模式，如: B{YYYY}{MM}{NNNN}',
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    current_sequence INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rule_name (rule_name),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

九、审计日志表#

9.1 审计日志表（按时间分区）#

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_id VARCHAR(50) UNIQUE NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    record_id BIGINT UNSIGNED NOT NULL,
    operation ENUM('CREATE', 'UPDATE', 'DELETE') NOT NULL,
    operator_id BIGINT UNSIGNED,
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    old_data JSON,
    new_data JSON,
    changed_fields JSON COMMENT '变更字段JSON',
    session_id VARCHAR(100),
    request_id VARCHAR(100),
    INDEX idx_log_id (log_id),
    INDEX idx_table_name (table_name),
    INDEX idx_record_id (record_id),
    INDEX idx_operation (operation),
    INDEX idx_operator_id (operator_id),
    INDEX idx_operation_time (operation_time),
    INDEX idx_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (YEAR(operation_time) * 100 + MONTH(operation_time)) (
    PARTITION p202412 VALUES LESS THAN (202501),
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p202505 VALUES LESS THAN (202506),
    PARTITION p202506 VALUES LESS THAN (202507),
    PARTITION p202507 VALUES LESS THAN (202508),
    PARTITION p202508 VALUES LESS THAN (202509),
    PARTITION p202509 VALUES LESS THAN (202510),
    PARTITION p202510 VALUES LESS THAN (202511),
    PARTITION p202511 VALUES LESS THAN (202512),
    PARTITION p202512 VALUES LESS THAN (202601),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

9.2 系统访问日志表#

CREATE TABLE access_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_id VARCHAR(50) UNIQUE NOT NULL,
    user_id BIGINT UNSIGNED,
    username VARCHAR(50),
    session_id VARCHAR(100),
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    login_method ENUM('PASSWORD', 'SSO', 'TOKEN') DEFAULT 'PASSWORD',
    status ENUM('SUCCESS', 'FAILED') DEFAULT 'SUCCESS',
    failure_reason TEXT,
    INDEX idx_log_id (log_id),
    INDEX idx_user_id (user_id),
    INDEX idx_access_time (access_time),
    INDEX idx_session_id (session_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (YEAR(access_time) * 100 + MONTH(access_time)) (
    PARTITION p202412 VALUES LESS THAN (202501),
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

9.3 系统通知表#

CREATE TABLE system_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    notification_type VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    related_id BIGINT UNSIGNED,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notification_type (notification_type),
    INDEX idx_related_id (related_id),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

十、视图设计#

10.1 样品全生命周期视图#

CREATE VIEW sample_lifecycle_view AS
SELECT 
    sr.id as request_id,
    sr.request_code,
    sr.batch_number,
    sr.product_name,
    sr.status as request_status,
    sr.request_date,
    u1.full_name as requester,
    sc.id as collection_id,
    sc.collection_code,
    sc.collection_date,
    u2.full_name as collector,
    sc.status as collection_status,
    srec.id as receipt_id,
    srec.receipt_code,
    srec.receipt_date,
    u3.full_name as receiver,
    srec.status as receipt_status,
    si.id as inventory_id,
    si.inventory_code,
    si.current_quantity,
    si.status as inventory_status
FROM sample_requests sr
LEFT JOIN users u1 ON sr.requester_id = u1.id
LEFT JOIN sample_collections sc ON sr.id = sc.request_id
LEFT JOIN users u2 ON sc.collector_id = u2.id
LEFT JOIN sample_receipts srec ON sc.id = srec.collection_id
LEFT JOIN users u3 ON srec.receiver_id = u3.id
LEFT JOIN sample_inventory si ON srec.id = si.receipt_id;

10.2 库存统计视图#

CREATE VIEW inventory_statistics_view AS
SELECT 
    sample_name,
    batch_number,
    manufacturer,
    storage_location,
    SUM(current_quantity) as total_quantity,
    COUNT(*) as location_count,
    MIN(expiry_date) as earliest_expiry,
    MAX(expiry_date) as latest_expiry
FROM sample_inventory
WHERE status = 'AVAILABLE'
GROUP BY sample_name, batch_number, manufacturer, storage_location;

10.3 待处理任务视图#

CREATE VIEW pending_tasks_view AS
SELECT 
    st.id as task_id,
    st.task_code,
    st.status as task_status,
    st.due_date,
    st.priority,
    sr.request_code,
    sr.batch_number,
    sr.product_name,
    u.full_name as collector,
    DATEDIFF(st.due_date, NOW()) as days_remaining
FROM sampling_tasks st
JOIN sample_requests sr ON st.request_id = sr.id
LEFT JOIN users u ON st.collector_id = u.id
WHERE st.status IN ('ASSIGNED', 'IN_PROGRESS')
AND st.due_date >= NOW();

10.4 过期样品视图#

CREATE VIEW expired_samples_view AS
SELECT 
    si.id,
    si.inventory_code,
    si.sample_name,
    si.batch_number,
    si.current_quantity,
    si.unit,
    si.expiry_date,
    DATEDIFF(si.expiry_date, CURDATE()) as days_expired,
    si.storage_location
FROM sample_inventory si
WHERE si.expiry_date < CURDATE()
AND si.status = 'AVAILABLE';

10.5 用户权限视图#

CREATE VIEW user_permissions_view AS
SELECT 
    u.id as user_id,
    u.username,
    u.full_name,
    u.department,
    r.role_name,
    p.permission_name,
    p.module
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.is_active = 1 AND r.is_active = 1;

十一、存储过程#

11.1 更新审计日志的存储过程#

DELIMITER //
CREATE PROCEDURE InsertAuditLog(
    IN p_table_name VARCHAR(50),
    IN p_record_id BIGINT UNSIGNED,
    IN p_operation ENUM('CREATE', 'UPDATE', 'DELETE'),
    IN p_operator_id BIGINT UNSIGNED,
    IN p_old_data JSON,
    IN p_new_data JSON,
    IN p_changed_fields JSON,
    IN p_session_id VARCHAR(100),
    IN p_request_id VARCHAR(100)
)
BEGIN
    DECLARE v_log_id VARCHAR(50);
    SET v_log_id = CONCAT('AUDIT_', DATE_FORMAT(NOW(), '%Y%m%d'), '_', LPAD((SELECT COUNT(*) + 1 FROM audit_logs WHERE DATE(operation_time) = CURDATE()), 3, '0'));

    INSERT INTO audit_logs (
        log_id, table_name, record_id, operation, operator_id, 
        operation_time, ip_address, user_agent, old_data, new_data, 
        changed_fields, session_id, request_id
    ) VALUES (
        v_log_id, p_table_name, p_record_id, p_operation, p_operator_id,
        NOW(), @current_ip, @current_user_agent, p_old_data, p_new_data,
        p_changed_fields, p_session_id, p_request_id
    );
END //
DELIMITER ;

11.2 自动生成批次编号的存储过程#

DELIMITER //
CREATE PROCEDURE GenerateBatchNumber(
    IN p_rule_name VARCHAR(100),
    OUT p_batch_number VARCHAR(50)
)
BEGIN
    DECLARE v_pattern VARCHAR(200);
    DECLARE v_current_seq INT;
    DECLARE v_batch_number VARCHAR(50);

    -- 获取规则模式
    SELECT rule_pattern, current_sequence 
    INTO v_pattern, v_current_seq
    FROM batch_number_rules 
    WHERE rule_name = p_rule_name AND is_active = 1;

    IF v_pattern IS NOT NULL THEN
        -- 生成批次号
        SET v_batch_number = v_pattern;
        SET v_batch_number = REPLACE(v_batch_number, '{YYYY}', DATE_FORMAT(NOW(), '%Y'));
        SET v_batch_number = REPLACE(v_batch_number, '{MM}', DATE_FORMAT(NOW(), '%m'));
        SET v_batch_number = REPLACE(v_batch_number, '{DD}', DATE_FORMAT(NOW(), '%d'));
        SET v_batch_number = REPLACE(v_batch_number, '{NNNN}', LPAD(v_current_seq, 4, '0'));

        -- 更新序列号
        UPDATE batch_number_rules 
        SET current_sequence = current_sequence + 1 
        WHERE rule_name = p_rule_name;

        SET p_batch_number = v_batch_number;
    ELSE
        SET p_batch_number = NULL;
    END IF;
END //
DELIMITER ;

十二、初始数据#

12.1 插入基础角色#

INSERT INTO roles (role_name, description) VALUES
('ADMIN', '系统管理员'),
('QA_MANAGER', '质量保证经理'),
('LAB_MANAGER', '实验室经理'),
('SAMPLER', '取样员'),
('RECEIVER', '接收员'),
('INVENTORY_MANAGER', '库存管理员');

12.2 插入基础权限#

INSERT INTO permissions (permission_name, description, module) VALUES
-- 样品方案管理
('PLAN_CREATE', '创建样品方案', 'SAMPLE_PLAN'),
('PLAN_UPDATE', '修改样品方案', 'SAMPLE_PLAN'),
('PLAN_DELETE', '删除样品方案', 'SAMPLE_PLAN'),
('PLAN_VIEW', '查看样品方案', 'SAMPLE_PLAN'),

-- 请验管理
('REQUEST_CREATE', '创建请验单', 'SAMPLE_REQUEST'),
('REQUEST_UPDATE', '修改请验单', 'SAMPLE_REQUEST'),
('REQUEST_APPROVE', '审批请验单', 'SAMPLE_REQUEST'),
('REQUEST_VIEW', '查看请验单', 'SAMPLE_REQUEST'),

-- 取样管理
('COLLECTION_CREATE', '创建取样记录', 'SAMPLE_COLLECTION'),
('COLLECTION_UPDATE', '修改取样记录', 'SAMPLE_COLLECTION'),
('COLLECTION_VIEW', '查看取样记录', 'SAMPLE_COLLECTION'),

-- 库存管理
('INVENTORY_VIEW', '查看库存', 'SAMPLE_INVENTORY'),
('INVENTORY_ISSUE', '样品领用', 'SAMPLE_INVENTORY'),
('INVENTORY_RETURN', '样品归还', 'SAMPLE_INVENTORY'),
('INVENTORY_DISPOSE', '样品销毁', 'SAMPLE_INVENTORY');

12.3 插入批次编号规则#

INSERT INTO batch_number_rules (rule_name, rule_pattern, description) VALUES
('DEFAULT_BATCH', 'B{YYYY}{MM}{NNNN}', '默认批次编号规则：B+年+月+流水号'),
('VALIDATION_BATCH', 'V{YYYY}{MM}{NNNN}', '验证样品批次编号规则'),
('PILOT_BATCH', 'P{YYYY}{MM}{NNNN}', '小试样品批次编号规则');

12.4 插入系统配置#

INSERT INTO system_configurations (config_key, config_value, config_type, description) VALUES
('SYSTEM_NAME', '实验室样品管理系统', 'STRING', '系统名称'),
('COMPANY_NAME', '紫龙制药', 'STRING', '公司名称'),
('AUDIT_RETENTION_DAYS', '2555', 'NUMBER', '审计日志保留天数（7年）'),
('EXPIRY_WARNING_DAYS', '30', 'NUMBER', '过期预警天数'),
('LOW_STOCK_THRESHOLD', '0.1', 'NUMBER', '库存不足阈值（10%）'),
('MAX_FILE_SIZE_MB', '50', 'NUMBER', '最大文件上传大小（MB）'),
('ALLOWED_FILE_TYPES', '["jpg","jpeg","png","pdf","doc","docx","xls","xlsx"]', 'JSON', '允许上传的文件类型');

十三、优势说明#

去掉外键约束和触发器后的优势：

13.1 性能提升#

  • 减少外键和触发器检查开销
  • 提高INSERT/UPDATE/DELETE操作速度
  • 减少锁竞争

13.2 灵活性增强#

  • 可以独立删除记录
  • 支持数据迁移和清理
  • 便于分库分表

13.3 维护简化#

  • 减少外键和触发器依赖关系
  • 降低数据一致性维护复杂度
  • 便于数据库重构

13.4 扩展性更好#

  • 支持分布式部署
  • 便于水平扩展
  • 减少跨表操作限制

13.5 应用层控制#

  • 在应用层实现数据完整性检查
  • 更灵活的业务逻辑控制
  • 便于实现软删除等特性

十四、注意事项#

  1. 数据完整性：需要在应用层实现数据完整性检查
  2. 事务管理：确保跨表操作的事务一致性
  3. 索引优化：根据查询模式优化索引设计
  4. 分区维护：定期维护审计日志分区
  5. 备份策略：制定完整的数据备份和恢复策略

通过合理的索引设计和应用层约束，可以在保持性能的同时确保数据一致性。