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

Question

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

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

本质上，他们是各种不同的“帽子”，任何个人都可能必须戴上这些帽子才能访问系统的不同部分并与之交互，并获取有关他们的信息可在我们网站的公共页面上找到。

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

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

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

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

编辑 一些受访者提到使用 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;

Answer 1

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

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

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

这是一种极其强大的模型，在 CRM 类型的系统中非常常见。 该模型几乎来自“数据模型资源书籍：第 1 卷”，这是此类事情的绝佳资源。

Answer 2

您描述的组和帽子模型是可以相互转换的。 无需真正担心数据丢失。 具体来说，“主组”表可以通过“帽子人”表与各种“帽子详细信息”表的外连接来产生。

如果您使用“帽子”模型，则需要确保给定的“帽子表”准确地封装了该帽子的独特特征。 与团体模型相比，这里的宽容度要低得多。

如果您这样做，您可能会想要为常见任务设置一些视图 - 例如，如果有人在“教师姓名”字段中输入内容，而您想要弹出一些自动完成功能，那么有一个视图基本上

SELECT firstName, lastName 
FROM persons 
INNER JOIN teachers ON persons.id = teachers.person_id 

会帮助很大。

顺便说一句，我发现有用的一件事是使用与原始表中主键相同的名称来调用外键。 这样你就可以只

INNER JOIN original_table USING (primary_key) 

在你的 FROM 中而不用胡闹于 WHERE 或 ON 等价物。

Answer 3

教师是唯一有工资的“人”吗？ 这样做可能会限制您的设计。 您可能想要做的是拥有一个属性表来存储“人”的附加属性。 这将允许将来进行修改。

Answer 4

我喜欢帽子方法。 过去，我实现了帽子和组的组合。 基本上，有一个用户可以执行的所有可能操作（权限）的列表。 然后我有一个组表。 每个组可以有 1 个或多个操作（权限）。 然后我将用户分配到组。

这为我提供了很大的灵活性。 我可以获得非常细粒度的许可。 我还可以通过编辑组来快速更改许多人的权限。 事实上，我有权限页面设置来使用相同的权限。 这允许最终用户（不是我）为其他用户设置权限。

Answer 5

是的，教师是唯一享有此类工资的人。 该字段应该更准确地称为“classPayRate” - 这是教师员工的特殊情况。 非教师员工将其总工作时间作为单独的行项目提交到我们的工资系统中。

Answer 6

我可能会将教师更改为员工并添加员工类型。

然而，我绝不会以任何形式或形式将电子邮件、地址、电话存储在人员表中。 这些都应该是自己的单独表格，因为人们有多个电子邮件地址（工作和家庭）、多个电话号码（工作、家庭、手机、传真）和多个地址（工作、家庭 1、家庭 2、学校等）。 我会将每个数据放在自己的表中并为其分配一个类型，以便您可以识别地址、电话等的类型。

此外，对于地址、电子邮件、电话，您可能需要一个标志来识别哪个是主要记录用于首先联系。 我们调用我们的通信，它是一个布尔值，通过触发器保持最新，因为每个拥有记录的人都必须有一个且只有一个通信，所以如果它发生变化，旧的必须自动重置以及新的进去，如果是第一条记录，则自动设置，如果删除了对应的记录，如果还有剩余记录，则将其分配给另一条记录。

Answer 7

为了安全起见，我更喜欢使用访问控制列表（ACL）。 通过 ACL，您可以拥有主体（用户或用户组）、资源（例如文件、记录或记录组）和操作（例如读取、更新、删除）。

默认情况下，没有人拥有任何特权。 要授予权限，您可以添加一个条目，例如 Bob 对文件 Abc 具有读取访问权限。

您应该能够找到帮助您实现类似功能的代码。 在 Java 中，JAAS 支持这种方法。

Answer 8

我正在使用 ACL 包来实现网站上的细粒度权限 - 我的问题的核心更多的是如何为具有不同角色的个人存储元数据，并在系统中构建一些保护措施以确保数据完整性（例如人必须有教师记录才能被设置为班级的教师 - 外键约束引用教师表，而不是人员表）。

Answer 9

我之前用过Party模型。 我确实解决了大部分缺点。