Database-design dilemma

Question

I have a question about my current project. It's an online registry system for a certain organization.

I have this problem on how I can determine the attendance of members in a certain 'conference'.

I have a table in my database named 'members' which contains all information about their registration and a 'conference' table too. There are conferences every year and members register to it in able to be a participant in that conference. I need to have a view function for the 'admin' to see who attended the the previous conference.

The idea I have already is that I will add 'first_conference' column in the 'members' table and set a value like '1' if that member registered or attended the 'first_conference' so that if admin chooses to view the attendance in the 1st conference, members with '1' as value of the 'first_conference' column will be showed in the page.

The thing is it is yearly and I'm finding something like a better algo for this. because if I go with my idea, I'll be like adding new column every year to the table 'members' e.g. ('second_conference', 'third_conference', ... , 'nth_conference')

trrrrrrm · Accepted Answer · 2013-09-29 06:00:25Z

2

this relation is called many-to-many that means for each member there will be multiple conferences and for each conference there will be multiple members.

you should break this relationship by using a third table lets call it conf_members and its columns is something like id, member_id, conference_id, attended then whenever you have a conference you will create a new row in conference table and for example the new conference id is 10 and then you send invitation to members and for each new invitation you add a new row to our new table for example:

id, member_id, conference_id, attended
1 , 2        , 10           , 0
1 , 5        , 10           , 0
1 , 4        , 10           , 0
1 , 3        , 10           , 0

once the member attend you change the attended from 0 to 1 if you are inviting someone who is not yet a member, you should add a record for that one in your members table and then add a reference to that member to this table.

so now you don't need to touch your database anymore, and you can easily generate your view by using join between your members , conferences, conf_member tables.

answered Sep 29, 2013 at 6:00

trrrrrrm

11.9k25 gold badges87 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Lee Torriz Over a year ago

oh i haven't considered that. well, i understand your solution and i think you're suggesting that i use 'foreign key' right? like in the new table you're suggesting, 'conf_members' have columns 'member_id' referenced from 'members' table and 'conference_id' referenced from 'conferences' table. is that how you're suggesting me to do it? thanks. :)

trrrrrrm Over a year ago

Ya forgot to mention that member_id, conference_id should be foreign keys

Collectives™ on Stack Overflow

Database-design dilemma

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related