Database question concerning relationships

[LostHiWay]

I'm kinda stumped on a question for school....here's that part I'm stuck at.

"A student club wants to have a database to manage its data on members. The club assigns members to its numerous committees. It is possible that one member can servie in more than one committe and will chair at most one commitee."

So far I've made two tables students and committees. Those two table have a many-to-relationship because a committee can have many students and a student can belong to many committees. How would I go about doing the part where a member can chair only one committee part. I'm almost positive it's a one-to-one relationship I just don't know where to put it.

 

[Barnaby W. Füi]

I believe it's a one-to-many, since a committee could have more than one chair. So each student should have a field for indicating which committee they chair.

 

[LostHiWay]

Thats what I was kinda having a problem with. My instructor really didn't make it clear how many chairs were on each committee. I guess I can do it both ways and ask him which way he meant in class then turn in the right one.

 

[Armitage]

I'd make a 3rd table. Call it committee_chairs. It has two columns ... an ID from from the student table and an ID from the committee table. To enforce the condition that each student can only chair one committee, define the student ID in the committee_table to be a unique index. If you also want to enforce only one chairman/committee add a index on the committee ID as well.

 

[Buddha Bart]

many-to-many is always a no-no

try this

 

[tkdkid]

If you only have two tables so far, you're probably doing this wrong.

Make a new table, committee_members, with student_id and committee_id as fields. That will be your one-to-many relationship, as each student can be in more than one committee.

To make a committee only have one chairman, put a field in the committees table called 'chairman_id'. This will be the student's id that chairs the committee.

Each student and committee that you create will need an id, the method for creating one varies by database, but use some kind of autoincrementing number for it.

 

[tkdkid]

Originally posted by: BingBongWongFooey
I believe it's a one-to-many, since a committee could have more than one chair. So each student should have a field for indicating which committee they chair.

I disagree with this method. A 'chaired_committee' field in the students table would be blank the majority of the time. There may be 30,000 students, but only 10 committees. It is simpler to have a 'chairman_id' field in the committees table.