0

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?

Kermit
  • 33,827
  • 13
  • 85
  • 121
Karen Remick
  • 1
  • 1

2 Answers2

1

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

Kermit
  • 33,827
  • 13
  • 85
  • 121
  • I'm not sure what you are saying as roles is a field and people is a table containing personal information. The role field could hold 'Teacher' and the roleID would hold the key for the teacher table, or the role could hold 'Mentor' and the value in the roldID would be the key in the mentor table. – Karen Remick Feb 05 '13 at 00:42
  • @KarenRemick That's exactly my point. You must look at it from an object perspective. A person is an object, and that person can have a role. You don't need to have a table for mentor, teacher or administrator. – Kermit Feb 05 '13 at 00:45
  • The teacher and mentor tables contain other information. The role of the person is part of who they are. The teacher table holds info like what school they work at and their certifications, where the mentor table contains info like who their mentoring partner is and what type of contract they are working under. A person will either be a teacher or a mentor (or an administrator - which we don't have separate data for). I want the field to point to the table with the additional data. – Karen Remick Feb 05 '13 at 00:49
  • @KarenRemick This doesn't sound like it was well designed. If you can't redesign it, my suggestion at this point would be to create mapping tables (`peopleTeacher, peopleAdministrator, peopleMentor`). – Kermit Feb 05 '13 at 00:56
0

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

Peter Wooster
  • 6,009
  • 2
  • 27
  • 39
  • Unfortunately in another place I have 22 tables that could be linked, one of them with 66 fields. So my options seem to be: 1) leave in separate tables and have 22 foreign keys 2) put them all in the same table together and have ~66 nulls per record – Karen Remick Feb 05 '13 at 00:31
  • Bah! here is the whole comment: Seems like a pretty big capability being overlooked here. Unfortunately in another place I have 16 tables that could be linked, one of them with 66 fields, 15 guaranteed nulls per record seems excessive. So my options seem to be: 1) leave in separate tables and have 16 foreign keys 2) put them all in the same table together and have ~66 nulls per record 3) don't mention them as foreign keys during table setup and do the matching in the join using variables and deal with the slow processing. At least this explains why I couldn't find the syntax. – Karen Remick Feb 05 '13 at 00:40