Forum Discussion
struggling to grasp the concept...
You need at least one more table. A committee has multiple people and a person can work in more than one committee. So you need a junction table (people-committee) with (so far) two foreign keys (no foreign key to people in the committee table).
A person has a role in a committee. Someone else can have the same role in an other committee. A person can have (different) roles in the committees he is in. Bottom line is: role is an attribute (fk) in people-committee (no fk to role in committee). I assume a person has only ONE role in a committees.
- also, is there a way that i can have the 'isActive' field changed from yes to no when a ceaseDate is added to the persons role. e.g. when a committee member resigns i would enter the date of that as their ceaseDate which would mean that their position was now available for someone else to perform. i was trying to use the isActive field to show only those roles that no one is currently performing on the form.
- Why do you need a committee table when there is only one committee?
In addition to the tables for people and roles, you need a table to link them. So there are two foreign keys in it.
An isActive field is not necessary. If the ceaseDate is filled in, then the person is not active, otherwise he is. So that is deducible and you don't store it.thanks.
i have the committee table to record who is in what position (and keep a historical record of previous position holders).
i have the people table and the role table and i connect them via the committee table (that has the person id and the role id in it, along with the date they start in the role and the date they finish)
isActive is gone. 🙂
this is what i am working with:
Your database looks alight. Your committee table is the junction table i referred to.
I am not sure about the isCommittee field. If (s)he connected to a role and is still active it's a True if no it is a False. So........