0

I am setting up a database for a university and I'm struggling to setup sensible FOREIGN KEY CONSTRAINTS (and even tables) because I have two tables in which the PRIMARY KEYS of each can relate to multiple PRIMARY KEYS of the other table and vice versa:

Concrete example:

  • I have a "users" table of all staff members where the primary ID is AUTO_INCREMENT
  • I have a "workgroups" table of all workgroups in the university where the primary ID is AUTO_INCREMENT
  • in the "users" table, there is a column called "workgroup(s)" which uses workgroups.id as a FOREIGN KEY
  • in the "workgroup" table, there is a column called "leader(s)" which uses users.id as a FOREIGN KEY

my problem is that in rare cases, a person/user can be the leader of multiple workgroups AND in even rarer cases, a workgroup can have two leaders.

I am reluctant to give the "users" table several "workgroup" columns (like "workgroup1", "workgroup2", etc) and equally reluctant to give the "workgroups" table several "leader" columns. And I believe there is no way to assign an array of user.ids to the workgroups.leader(s) column and have that be a FOREIGN KEY which actually references multiple user.ids, right? So, if users with IDs 10 and 15 are leaders of workgroup "genetics" (workgroups.id = 1), I cannot assign "workgroups.leaders @ id=1" to be [10,15] and have that actually be a FOREIGN KEY which references two user.ids correctly, right?

I also have setup FOREIGN KEY CONSTRAINTS between the two columns "workgroups.leaders" and "users.id" in both directions going "ON DELETE SET NULL" and "ON UPDATE CASCADE". The intention being that if a user who is a workgroup leader is deleted, the workgroup should have NULL leaders until manually assigned some other user as leader and secondly: If a leader's user.id or a workgroups's id changes (both of which shouldn't happen), it would be updated in the other table, respectively.

I assume that I have to setup these many-to-many relationships between "users.id" and "workgroup.leader(s)" in a third table?? So take out "leaders" column from "workgroups" table and take out "workgroup" column from "users" table? And then put them in a third table where a user can be linked to be leader of multiple workgroups AND have the FOREIGN KEYS set in this third table?

Should I do this for all user <-> workgroup relationships in a forth table? Because, aside from being leader, any user may be a MEMBER of several workgroups (without being its leader)... or could I put this all in a single third table with 4 columns like "relationship_id [INT, PRIMARY], user_id [REFERENCES users.id], workgroup_id [REFERENCES workgroups.id], is_leader [BOOL]"? Do I even need "relationship_id" here if this table is only used for joining on the two FOREIGN KEYS?

I just hope that it'll never happen that a leader of one group can be in second group without being the second group's leader...

1 Answer 1

2

I am reluctant to give the "users" table several "workgroup" columns (like "workgroup1", "workgroup2", etc) and equally reluctant to give the "workgroups" table several "leader" columns.

Good.
That would break the most basic Data Normalisation rules.

I assume that I have to setup these many-to-many relationships between "users.id" and "workgroup.leader(s)" in a third table?

Correct.

Should I do this for all user <-> workgroup relationships in a forth table?

Yep!

Something like this:

select * from users ; 
+----+--------+
| ID | Name   | . . . 
+----+--------+
|  1 | Fred   | 
|  2 | Barney | 
+----+--------+

select * from workgroups ; 
+----+----------+
| ID | Name     | . . . 
+----+----------+
| 22 | Gravel 1 |
+----+----------+

select * from workgroup_leaders ; 
+--------------+---------+
| workgroup_id | user_id |
+--------------+---------+
|           22 |       1 |
+--------------+---------+

select * from workgroup_users ; 
+--------------+---------+
| workgroup_id | user_id |
+--------------+---------+
|           22 |       2 |
+--------------+---------+
2
  • 1
    Possibly workgroup_leaders should itself be a derived table (using some form of table inheritance) from workgroup_users depending on use case. Or maybe just a flag column is_leader on workgroup_users Jan 31 at 12:59
  • Sounds good! I have now also read about the different Normal forms of a database and it seems to me it makes perfect sense to group the different entities (workgroups, users, projects, etc) into individual tables and then set the connections between the entities (which user in which groups? which user is leader for which group? what projects do groups work on? etc) in separate tables! Thanks! Feb 6 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.