带额外条件的多对多关系数据库表最优设计方案咨询

业务问题：假设我们有若干医疗中心和在这些中心工作的医生。显然，多名医生可在同一中心工作，同时一名医生也可在多个中心任职。我们需要存储每个医疗中心的主任医生信息（每个医疗中心仅能有一名主任医生，一名医生可兼任多个中心的主任）。

问题：如何构建数据库表以满足上述业务需求？

我想到两种方案（如下所述），若您有其他方案，欢迎告知。

方案1 #

此方案将主任医生信息存储在关联表jobs中，存在两个缺点：

• jobs.is_head列大多为false，显得冗余，存储了不必要的信息。
• 需要额外添加约束，限制同一中心不能有两名主任医生。

create table doctors
(
    id   bigint  not null
        constraint doctors_pk
            primary key,
    name varchar not null
);

create table medical_centers
(
    id      bigint  not null
        constraint medical_centers_pk
            primary key,
    address varchar not null
);

create table jobs
(
    medical_center_id bigint  not null
        constraint centers_fk
            references medical_centers,
    doctor_id         bigint  not null
        constraint doctors_fk
            references doctors,
    is_head           boolean not null,
    constraint jobs_pk
        primary key (doctor_id, medical_center_id)
);

方案2 #

此方案将主任医生信息存储在medical_centers表中，同样存在两个缺点：

• 表间存在两种关系：多对多（医生与医疗中心的任职关系）和一对多（医生可兼任多中心主任），这会增加复杂度，尤其在使用ORM框架（如JPA实现）时。
• 需要额外约束，限制不能将未在该中心任职的医生设为主任。

create table doctors
(
    id   bigint  not null
        constraint doctors_pk
            primary key,
    name varchar not null
);

create table medical_centers
(
    id             bigint  not null
        constraint medical_centers_pk
            primary key,
    address        varchar not null,
    head_doctor_id bigint
        constraint head_doctor_id_fk
            references doctors
);

create table jobs
(
    medical_center_id bigint not null
        constraint centers_fk
            references medical_centers,
    doctor_id         bigint not null
        constraint doctors_fk
            references doctors,
    constraint jobs_pk
        primary key (doctor_id, medical_center_id)
);

优化方案：单独存储主任关联关系 #

针对前两种方案的缺陷，建议单独创建一张表存储医疗中心与主任医生的关联，同时保留jobs表记录医生的任职关系：

方案优势 #

• 无冗余字段，仅存储必要的主任关联信息
• 天然保证“一个医疗中心仅一名主任”的约束
• 可通过数据库约束确保主任医生必须是该中心的任职医生，无需额外业务逻辑验证
• 表关系清晰，ORM框架映射更简单

对应SQL代码 #

create table doctors
(
    id   bigint  not null
        constraint doctors_pk
            primary key,
    name varchar not null
);

create table medical_centers
(
    id      bigint  not null
        constraint medical_centers_pk
            primary key,
    address varchar not null
);

-- 记录医生与医疗中心的多对多任职关系
create table jobs
(
    medical_center_id bigint not null
        constraint centers_fk
            references medical_centers,
    doctor_id         bigint not null
        constraint doctors_fk
            references doctors,
    constraint jobs_pk
        primary key (doctor_id, medical_center_id)
);

-- 单独存储医疗中心的主任信息
create table center_heads
(
    medical_center_id bigint not null
        constraint center_heads_medical_centers_fk
            references medical_centers
            on delete cascade,
    doctor_id         bigint not null
        constraint center_heads_doctors_fk
            references doctors,
    constraint center_heads_pk
        primary key (medical_center_id), -- 主键确保一个中心仅一名主任
    -- 约束：主任必须是该中心的任职医生
    constraint center_head_must_work_here
        foreign key (medical_center_id, doctor_id) references jobs(medical_center_id, doctor_id)
);

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