分配用户角色的首选数据库设计方法?(帽子与团体) [英] Preferred database design method for assigning user roles? (Hats vs. Groups)

问题描述

我有一个中等大小的 MySQL 数据库，其中有一个主要的persons"表，其中包含与我负责维护和开发许多 Web 应用程序的剧院和剧院学校有关的每个人的基本联系信息.

I have medium sized MySQL database with a primary "persons" table which contains basic contact information about every human being connected to the theatre and theatre school for which I am responsible for maintaining and developing a number of web applications.

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

Some persons are just contacts - that is, their "persons" table record is all the information we need to store about them. Many others though have to be able to assume different roles for a variety of systems. Of these, most start out as students. Some start as employees. People who are students can become interns or performers; employees can become students; all teachers are employees and performers, etc.

本质上，它们是各种不同的帽子"，任何人都可能必须戴上这些帽子"才能访问系统的不同部分并与之交互，并在我们的公共页面上提供有关它们的信息网站.

In essence, their are a variety of different "hats" that any individual person may have to wear in order to access and interact with different parts of the system, as well as have information about them made available on public pages on our site.

我选择实现这个模型是有几个其他的表来代表这些帽子"——包含元信息的表来补充基本的人"信息，所有这些都使用人"id作为他们的主键.例如，一位教师在教师表中有一个记录，其中包含他或她的简短传记信息和工资率.所有教师也是员工(但并非所有员工都是教师)，这意味着他们在员工表中有一个记录，允许他们将工作时间提交到我们的工资系统.

My choice for implementing this model is to have several other tables which represent these "hats" - tables which contain meta-information to supplement the basic "person" info, all of which use the "persons" id as their primary key. For example, a person who is a teacher has a record in a teachers table containing his or her short biographical information and pay rate. All teachers are also employees (but not all employees are teachers), meaning they have a record in the employees table which allows them to submit their hours into our payroll system.

我的问题是，这样实现模型有什么缺点?我能想到的唯一另一种选择是用对于大多数条目来说都是空且无用的字段来扩充人员表，然后有一个繁琐的人员可以属于的组"表，然后几乎每个表都有系统有一个 person person_id 外键，然后根据业务逻辑来验证引用的 person_id 是否属于适当的组；但这很愚蠢，不是吗?

My question is, what are the drawbacks to implementing the model as such? The only other option I can think of is to inflate the persons table with fields that will be empty and useless for most entries and then have a cumbersome table of "groups" to which persons can belong, and then to have almost every table for every system have a person person_id foreign key and then depend on business logic to verify that the person_id referenced belongs to the appropriate group; But that's stupid, isn't it?

下面是一些示例表声明，希望它们可以展示我目前如何将所有这些组合在一起，并希望表明为什么我认为这是对系统必须处理的各种情况的现实进行建模的更明智的方法与.

A few example table declarations follow below, which hopefully should demonstrate how I'm currently putting all this together, and hopefully show why I think it is a more sensible way to model the reality of the various situations the systems have to deal with.

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

Any and all suggestions and comments are welcome. I appreciate your time.

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

EDIT A few respondents have mentioned using ACLs for security - I did not mention in my original question that I am in fact using a separate ACL package for fine-grained access control for actual users of the different systems. My question is more about the best practices for storing metadata about people in the database schema.

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;

推荐答案

我去年经历了类似的事情.问题是:我们是明确地还是一般地为我们的实体建模?在您的示例中，这意味着实体/表格(如教师、学生等)之间是否存在直接关系.

I went through a similar thing last year. There the question was: do we model our entities explicitly or generically? In your example, that would mean having entities/tables like teacher, student, etc with direct relationships between them or not.

最后，我们采用了通用的派对"模型.Party模型如下:

In the end we went for a generic "Party" model. The Party model is as follows:

• 政党代表一个人或组织；
• 大多数派对类型都有一个依赖表来存储额外的信息，具体取决于派对类型，例如个人、组织、公司；
• 学生或教师等都是党派角色.一个派对可以有任意数量的派对角色.例如，一个人可能既是教师又是学生；
• 诸如类之类的事情作为参与方角色关系处理.例如，教师和学生角色之间的关系表示班级关系；
• 派对角色关系可以有额外信息的子类型.模型中的师生关系是一个 Enrollment，它可能具有您正在谈论的额外属性；
• 各方之间没有直接关系.只有当事方角色相互关联；和
• 对于常见的信息分组，如果有帮助，我们会创建视图，因为 SQL 可能会有点复杂，因为关系更加间接(例如，教师和学生的派对实体之间有三个表).

• A Party represents a person or organisation;
• Most Party types had a dependent table to store extra information depending on the party type eg Person, Organization, Company;
• Things like Student or Teacher are Party Roles. A Party may have any number of Party Roles. A Person may be both a Teacher and a Student, for example;
• Things like classes are handled as Party Role Relationships. For example, a relationship between a Teacher and Student role indicates a class relationship;
• Party Role Relationships can have subtypes for extra information. A Teacher-Student Relationship in your model is an Enrolment and that could have the extra attributes you're talking about;
• Parties don't have direct relationships with each other. Only Party Roles relate to each other; and
• For common groupings of information, we created views if it helped because the SQL can be a bit convoluted as the relationships are more indirect (eg there are three tables in between the Party entities for a Teacher and Student).

这是一种非常强大的模型，在 CRM 类型系统中非常常见.该模型几乎来自 "数据模型资源手册:第 1 卷"，这是处理此类事情的绝佳资源.

It's an extremely powerful model, one that is pretty common in CRM type systems. This model pretty much came from "The Data Model Resource Book: Volume 1", which is an excellent resource for such things.

这篇关于分配用户角色的首选数据库设计方法?(帽子与团体)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！