title: Preferred database design method for assigning user roles? (Hats vs. Groups)
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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 <code>person_id</code> 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?</p>
  
  <p>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.</p>
  
  <p>Any and all suggestions and comments are welcome. I appreciate your time.</p>
  
  <p><strong>EDIT</strong> 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. </p>
  
  <pre><code>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;
  </code></pre>