用于分配用户角色的首选数据库设计方法？ （帽子与团体）

我有一个中等规模的 MySQL 数据库，其中有一个主要的“人员”表，其中包含与剧院和戏剧学校相关的每个人的基本联系信息，我负责维护和开发许多 Web 应用程序。

有些人只是联系人 - 也就是说，他们的“人”表记录是我们需要存储的有关他们的所有信息。但许多其他人必须能够为各种系统承担不同的角色。其中，大多数人都是从学生开始的。有些人从雇员开始。学生可以成为实习生或表演者；员工可以成为学生；所有教师都是雇员和表演者等。

本质上，它们是任何个人都可能必须佩戴的各种不同的“帽子”，以便访问系统的不同部分并与之交互，以及在我们网站的公共页面上提供有关它们的信息。

我实现此模型的选择是使用几个其他表来表示这些“帽子”，这些表包含元信息以补充基本的“人员”信息，所有这些表都使用“人员”id 作为主键。例如，一名教师在教师表中有一条记录，其中包含他或她的简短简历信息和工资率。所有教师也是雇员（但并非所有雇员都是教师），这意味着他们在雇员表中有一条记录，允许他们将工作时间提交到我们的工资系统中。

我的问题是，实施该模型有哪些缺点？我能想到的唯一的其他选择是用对大多数条目来说都是空且无用的字段来膨胀人员表，然后有一个人员可以所属的繁琐的“组”表，然后为每个人提供几乎每个表系统有一个人person_id外键，然后依赖业务逻辑来验证引用的 person_id 是否属于适当的组；但这很愚蠢，不是吗？

下面是一些示例表声明，希望能够演示我当前如何将所有这些组合在一起，并希望说明为什么我认为这是对系统必须处理的各种情况的现实进行建模的更明智的方法。

欢迎任何建议和意见。我很感激你的时间。

EDIT一些受访者提到使用 ACL 来确保安全 - 我在最初的问题中没有提到我实际上正在使用单独的 ACL 包来为不同系统的实际用户进行细粒度的访问控制。我的问题更多是关于在数据库模式中存储有关人员的元数据的最佳实践。

CREATE TABLE persons (
    `id`            int(11) NOT NULL auto_increment,
    `firstName`     varchar(50) NOT NULL,
    `middleName`    varchar(50) NOT NULL default '',
    `lastName`      varchar(75) NOT NULL,
    `email`         varchar(100) NOT NULL default '',
    `address`       varchar(255) NOT NULL default '',
    `address2`      varchar(255) NOT NULL default '',
    `city`          varchar(75) NOT NULL default '',
    `state`         varchar(75) NOT NULL default '',
    `zip`           varchar(10) NOT NULL default '',
    `country`       varchar(75) NOT NULL default '',
    `phone`         varchar(30) NOT NULL default '',
    `phone2`        varchar(30) NOT NULL default '',
    `notes`         text NOT NULL default '',
    `birthdate`     date NOT NULL default '0000-00-00',
    `created`       datetime NOT NULL default '0000-00-00 00:00',
    `updated`       timestamp NOT NULL,
    PRIMARY KEY (`id`),
    KEY `lastName` (`lastName`),
    KEY `email` (`email`)
) ENGINE=InnoDB;

CREATE TABLE teachers (
    `person_id`     int(11) NOT NULL,
    `bio`           text NOT NULL default '',
    `image`         varchar(150) NOT NULL default '',
    `payRate`       float(5,2) NOT NULL,
    `active`        boolean NOT NULL default 0,
    PRIMARY KEY (`person_id`),
    FOREIGN KEY(`person_id`) REFERENCES `persons` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE classes (
    `id`            int(11) NOT NULL auto_increment,
    `teacher_id`    int(11) default NULL,
    `classstatus_id` int(11) NOT NULL default 0,
    `description`   text NOT NULL default '',
    `capacity`      tinyint NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`teacher_id`) REFERENCES `teachers` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(`classstatus_id`) REFERENCES `classstatuses` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY (`teacher_id`,`level_id`),
    KEY (`teacher_id`,`classstatus_id`)
) ENGINE=InnoDB;

CREATE TABLE students (
    `person_id`     int(11) NOT NULL,
    `image`         varchar(150) NOT NULL default '',
    `note`          varchar(255) NOT NULL default '',
    PRIMARY KEY (`person_id`),
    FOREIGN KEY(`person_id`) REFERENCES `persons` (`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE enrollment (
    `id`                int(11) NOT NULL auto_increment,
    `class_id`          int(11) NOT NULL,
    `student_id`        int(11) NOT NULL,
    `enrollmenttype_id` int(11) NOT NULL,
    `created`           datetime NOT NULL default '0000-00-00 00:00',
    `modified`          timestamp NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`class_id`) REFERENCES `classes` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(`student_id`) REFERENCES `students` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(`enrollmenttype_id`) REFERENCES `enrollmenttypes` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

去年我也经历过类似的事情。问题是：我们是明确地还是通用地对我们的实体进行建模？在您的示例中，这意味着实体/表（例如教师、学生等）之间是否有直接关系。

最后我们选择了通用的“派对”模型。政党模型如下：

• A Party代表个人或组织；
• 大多数参与方类型都有一个依赖表来存储取决于参与方类型的额外信息，例如个人、组织、公司；
• 像学生或老师这样的东西是派对角色。一方可以具有任意数量的一方角色。例如，一个人可以既是教师又是学生；
• 像类这样的事情被处理为各方角色关系。例如，教师和学生角色之间的关系表示班级关系；
• 参与方角色关系可以有子类型以获取额外信息。您的模型中的师生关系是一个注册，并且可能具有您正在谈论的额外属性；
• 各方之间没有直接关系。只有参与方角色相互关联；和
• 对于常见的信息分组，我们创建了视图（如果有帮助的话），因为由于关系更加间接，SQL 可能会有点复杂（例如，教师和学生的当事人实体之间存在三个表）。

这是一种极其强大的模型，在 CRM 类型的系统中非常常见。这个模型几乎来自《数据模型资源书：第 1 卷》 https://rads.stackoverflow.com/amzn/click/com/0471380237，这是此类事情的绝佳资源。