基于MariaDB的多模块通用基础信息表关联设计咨询

嘿，这个场景在数据库设计里太常见了——多个实体共享一堆通用属性，想统一管理又不知道怎么关联对吧？我给你几个经过项目验证的方案，你可以结合自己的业务需求挑：

方案一：共享元数据表 + 多态关联（最推荐的集中式方案） #

核心思路是把所有实体的通用属性抽出来放到一张单独的表，然后每个业务表通过外键关联到这张表，同时用一个字段标记所属的实体类型。

示例MariaDB建表语句 #

-- 通用元数据表：存放所有实体的基础属性
CREATE TABLE entity_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creator_id INT NOT NULL,
    current_owner_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE COMMENT '是否移入回收站',
    entity_type VARCHAR(50) NOT NULL COMMENT '标记所属实体：user/file/notification/employee'
);

-- Users表：只存用户专属属性，关联元数据表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metadata_id INT UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    -- 其他用户专属字段...
    FOREIGN KEY (metadata_id) REFERENCES entity_metadata(id)
);

-- Files表：同理
CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metadata_id INT UNIQUE NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    -- 其他文件专属字段...
    FOREIGN KEY (metadata_id) REFERENCES entity_metadata(id)
);

-- Employees表（注意：非所有员工都是用户）
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metadata_id INT UNIQUE NOT NULL,
    employee_no VARCHAR(20) NOT NULL UNIQUE,
    department VARCHAR(100),
    -- 其他员工专属字段...
    FOREIGN KEY (metadata_id) REFERENCES entity_metadata(id)
);

关键优化：解决创建者/所有者的多态问题 #

你提到Employees不一定是Users，那creator_id和current_owner_id可能指向用户或员工。这时候建议先建一张统一的人员表，避免多态外键的麻烦：

-- 统一人员表：涵盖所有系统内的人员（用户+员工）
CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    is_system_user BOOLEAN DEFAULT FALSE COMMENT '标记是否是系统注册用户',
    -- 其他通用人员字段：手机号、部门等
);

-- 调整entity_metadata的外键
ALTER TABLE entity_metadata 
ADD FOREIGN KEY (creator_id) REFERENCES people(id),
ADD FOREIGN KEY (current_owner_id) REFERENCES people(id);

-- 调整Users表关联people
ALTER TABLE users
ADD FOREIGN KEY (id) REFERENCES people(id);

方案优缺点 #

• ✅ 通用属性集中管理，修改时只需要操作一张表，维护成本低
• ✅ 避免字段冗余，数据一致性有保障
• ❌ 查询实体时需要关联元数据表，小幅度增加查询复杂度（可以通过视图简化）

方案二：数据库表继承（适合查询性能优先的场景） #

MariaDB没有PostgreSQL那样的原生表继承，但可以通过触发器+外键模拟实现：把通用属性放到基础表，业务表复制这些字段并关联基础表，用触发器同步数据。

示例建表语句 #

-- 基础实体表：存放通用属性
CREATE TABLE base_entity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creator_id INT NOT NULL,
    current_owner_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- Users表：包含通用属性+专属属性
CREATE TABLE users (
    id INT PRIMARY KEY,
    creator_id INT NOT NULL,
    current_owner_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    FOREIGN KEY (id) REFERENCES base_entity(id)
);

-- 触发器：插入用户时自动同步到base_entity
DELIMITER //
CREATE TRIGGER sync_users_to_base AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO base_entity (id, creator_id, current_owner_id, created_at, is_deleted)
    VALUES (NEW.id, NEW.creator_id, NEW.current_owner_id, NEW.created_at, NEW.is_deleted);
END //
DELIMITER ;

方案优缺点 #

• ✅ 查询单个实体时不需要关联，性能更好
• ❌ 通用属性修改时需要更新所有业务表，维护成本高
• ❌ 触发器增加了数据库复杂度，排查问题难度提升

方案三：应用层封装通用属性（适合快速开发的场景） #

如果不想在数据库层面做复杂关联，可以在应用代码里封装一个通用的实体基类，让所有业务实体继承这个基类，自动携带通用字段。

比如在Python Django里用抽象模型：

from django.db import models

class BaseEntity(models.Model):
    creator = models.ForeignKey('people.People', on_delete=models.CASCADE)
    current_owner = models.ForeignKey('people.People', on_delete=models.SET_NULL, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    is_deleted = models.BooleanField(default=False)

    class Meta:
        abstract = True  # 抽象模型，不会生成数据库表

class User(BaseEntity):
    username = models.CharField(max_length=50, unique=True)
    email = models.EmailField(unique=True)

方案优缺点 #

• ✅ 数据库结构简单，查询无需关联
• ✅ 应用层统一管理通用属性，修改方便
• ❌ 数据库层面没有集中约束，需要依赖应用层保证数据一致性

最终建议 #

如果你的业务需要频繁修改通用属性、追求数据一致性，优先选方案一；如果查询性能要求极高，且通用属性很少变动，可以考虑方案二；如果是快速迭代的项目，用方案三能节省数据库设计时间。

内容的提问来源于stack exchange，提问作者mrzewnicki