'Best way to model identifiable has many?
I’m trying to figure out the best way to model a relationship. I have three tables: Units, UnitCrews, and Users.
Conceptually a Unit has a Crew consisting of 2+ Users in various positions; one Incharge, one Attendant, and zero or more Observers.
So a Unit should have one UnitCrew, and a UnitCrew should have many Users, but I need to know the position of each user.
If a User belongs to a UnitCrew, the fk would be on UnitCrews, but that would just add a User_Id column with no way to identify the position.
Ideally the UnitCrews table would have columns for Incharge, Attendant, and some way to identify 0+ Observers, but I’m not sure how that would work.
My initial thought is to create an additional table called CrewAssignments as a sort of join table, with a column Type whose value would be either Incharge, Attendant, or Observer. That would allow a UnitCrew to have many CrewAssignments, and a User to have one CrewAssignment.
Is the CrewAssignments table the best way to model this, or is there a better option?
Edit
Adding some clarification...
A User belongs to one UnitCrew, and a UnitCrew has many Users.
To better describe what I'm trying to achieve, here's the scenario I'm trying to model:
Each Unit is staffed with a Crew. A Crew needs an Incharge, an Attendant, and can have zero or more Observers. Each position in the Crew (Incharge, Attendant, Observer) is a Crew Member. So a Crew Member is part of a (belongs to one) Crew as it's Incharge, Attendant, or Observer; and a Crew has many Crew Members.
In that scenario Unit = Units, Crew = UnitCrews, and Crew Member = Users
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
