DB Design - participants in event

Question

I have the following Problem and i dont know how to solve it properly yet. In my MYSQL Database I have a table for events with a ID, name, etc. But also I want to store a list of participants. But the problem is that this list is of variable length. So what is the best way to store this list ? If you want you can compare this to Facebook Events where they have informations about the event itself and then tables for "going", "maybe", "no", "invited". How are those tables handled? Is there a specific table for each event? Because that would cause tons of tables or is there another trick?

I know this question should be a very simple one but I am kind of new to databases. Best Regards

Answer 1

you may have 4 tables:

Events:(EventID,Name,.....)

Members:(MemberID,....)

Participations(EventID,MemberID,ParticipationTypeID)

ParticipationTypes(ParticipationTypeID,ParticipationTypeName)

the later table strore values such as "going",.."invited",...

field marked bold are primary keys