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')

Answer 1

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.