Preferred database design method for assigning user roles? (Hats vs. Groups)

Question

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;

Answer 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.

Answer 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.