MySQL关系数据库设计

Question

I have seen a question on this forum that I can relate with, but I can't apply the answers to my question.

Here it goes:

1. I have a memberlist table (id, name, number) I'll just make the columns short.
2. Next, I have an events table (id, eventName, description)

Now, 1. each member in the memberlist can join events as many as he wants. 2. each events in the events table can have members without limits (okay, say 1k members, like that or whatever).

What I have now is an event table that has a column named: "joiners" which will contain the id of a certain joiner/member. But I believe I'm wrong because how can a certain event handles many joiner's id?

Answer 1

I would rename memberlist into members to make your table naming more consistent. Or events into eventlist. Which ever you like more.

Then you want to define a many to many relation between members and events. This is done through an intermediate table which will reference both:

create table eventmembers (
    id int unsigned not null primary_key auto_increment,
    member_id int unsigned not null references members(id),
    event_id int unsigned not null references events(id)
)

I'm assuming that on your memebers and events you already have id fields which are set to be primary keys.

If you want to get all events attended by a specific user you can then do

select events.*
from events
left join eventmembers
  on events.id = eventmembers.event_id
where
    member_id = ?

and get all the members in an event:

select members.*
from members
left join eventmembers
  on members.id = eventmembers.member_id
where
    event_id = ?