mattobob mattobob - 1 month ago 12
MySQL Question

Relationship Database, many to many

How do I represent, as a diagram, in a relationship database (eg MySQL ) the case in which:

1) a single user can be the organizer of many different events

2) an event can have only one organizer

3) and event can have many participants (which are all between the Users Table)

4) a user can participate to many event (Only if the event does not collide to another event from start to end)

here is a sketch of the DB, I have to understand this many to many relationship.enter image description here

Answer

I think you almost have it.

Now, I would suggest that Events_has_Users table be renamed to Users_Attending_Events since this would be clearer. On this table also, the primary key would be both Users_UserId and Events_EventsId, which are actually foreign keys. Being primary keys and foreign keys are automatically done in Workbench.

This don't need the Events Organizer there since this is defined in the Events Table as a foreign key (Organizer).

Here is a clearer rendition. Just fill in the other fields:

enter image description here

In table Events, users_id is the organizer (a foreign key).