MySQL foreign key having multiple (conditional) po

Question

My idea is to use two entries (one containing the referenced table name and one containing the key in that table) in one table to reference one of several other tables.

The relevant parts of the table:

CREATE TABLE people 
  ( 
     peopleid SMALLINT UNSIGNED auto_increment, 
     name     VARCHAR(40) NOT NULL, 
     prevname VARCHAR(40), 
     role     ENUM('Teacher', 'Mentor', 'Administrator'), 
     roleid   SMALLINT UNSIGNED 
  ) 

Note:Teacher and Mentor are tables. If the person is an administrator, RoleID would be null.

I want the RoleID to be the foreign key referencing what ever table is referenced in the Role field. How do I do that?

Answer 1

SQL isn't designed to do that. You will need separate foreign keys if you want to link to separate tables.

Answer 2

Your best bet is to have a separate table for roles and people. This will provide a normalized and relational model.