Preferred database design method for assigning use

问题:

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.

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.

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.

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.

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;

回答1:

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.

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

• 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).

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.

回答2:

The groups and hats models you describe are convertible, one to the other. There's no real worry about data loss. Specifically, the "master groups" table can be produced by outer joins of the "hat person" table with the various "hat detail" tables.

If you're using a "hat" model you need to make sure that a given "hat table" accurately encapsulates the unique characteristics of that hat. There's a lot less forgiveness there than with the groups model.

You'll probably want to set up a few views for common tasks if you go this way - for example, if somebody's typing into a field for "teacher name" and you want to pop up some autocompletes, having a view which is basically

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

will help enormously.

On a tangential note, one thing I've found to be useful is to call foreign keys by the same name as the primary key in their original table. That way you can just

INNER JOIN original_table USING (primary_key) 

in your FROM instead of monkeying with WHERE or ON equivalencies.

回答3:

Are teachers the only 'person' that has a pay rate? You may be limiting your design by doing it this way. What you may want to do is have an attributes table that stores additional attributes for a 'person'. This will allow for future modifications.

回答4:

I like the Hat approach. In the past, I implemented a combination of hats and groups. Basically, there is a list of all possible actions (permissions) a user can do. I then have a table of groups. Each group can have 1 or many actions (permissions). I then assign users to groups.

This provides me with a lot of flexibility. I can get very fine grain in my permissioning. I also can change many peoples permissions quickly by just editing the group. In fact, I have the permissioning page setup to use the same permissions. This allows the end user (not me) to setup permissions for other users.

回答5:

yes, teachers are the only people who have a pay rate as such. That field should more accurately be called "classPayRate" - it's a special case for teacher employees. Non-teacher employees submit their total hours as a separate line item in our payroll system.

回答6:

I might change teachers to employees and add employee type.

However in no way shape or form would I ever store email, address, phone in the person table. These should all be separate tables of their own as people have multiple email addresses (work and home), multiple phone numbers (work, home, cell, fax) and multiple addresses (work, home1, home 2, school, etc.). I would put each in its own table and assign a type to it so you can identify which is what type of address, phone, etc.

Also for address, email, phone you might want a flag to identify which is the the main record to use for contacting first. We cal ours correspondence and it is a boolean that is kept up-to-date with a trigger as each person who has a record must have one and only one correspondence, so if it changes, the old one must automatically be reset as well as the new one go in and if it is the first record it is set automaticially and if the correspondence reciord is deleted , it will be assigned to the another one if there are remaining records.

回答7:

For security I prefer to use Access Controls Lists (ACLs). With ACL's you have Principals (users or groups of users), Resources (such as a file, record or group of records) and Actions (such as read, update, delete).

By default nobody has any privilege. To grant a permission you add an entry like Bob has Read Access to File Abc.

You should be able to find code that helps you implement something like this. In Java the JAAS supports this method.

回答8:

I'm using an ACL package for fine-grained permissions on the site - the heart of my question is more about how to store the metadata for individuals who have different roles and build a few safeguards into the system for data integrity (such that a person must have a teacher record in order to be set as the teacher of a class - the foreign key constraint references the teacher table, not the person table).

回答9:

I used Party model before. I really solves most of the shortcomings.