2

I am creating a web interface for upcoming events in a community. The users can create an event, join an event, and also leave an event. The interface will list the upcoming events with the following fields: Event name, event datatime, event location, event participants.

My question is how should I store the users for each event? I am using Mysql. Since users can create a new event, and they can also join or leave an event by others, is it better for me to create 2 link tables? One to store the creation, the other to store the Join/leave?

I hope this question is specific enough, and any suggestions are gratefully received.

  • 1
    Look up "Open Source class/event scheduling software" or similar. A class in school can be seen as a future event. This has been done many times - you can avoid reinventing the wheel - and profit from the efforts of others :-) – Vérace Mar 12 '16 at 16:36
  • If you want to "roll your own" - Event and UserEvent tables. Event would have e.g. EventID, Name, Description, Date, Category, OwnerID (FK to User table)... UserEvent could have UserID (FK to User table), EventID (FK to Event table). – seventyeightist Mar 12 '16 at 17:54
1

An "Event" has a Creator ("users can create an Event"), Members (those who have "Joined" and not "Left" the event) and a Location.

You should have the entity Event with EventID, Creator, other fields such as Location, description, category, date created (optionally) Represented as an Event table with EventID, CreatorID (foreign key to Users table), maybe other fields as needed.

Then have a table EventUser or similar, which is a "bridge" table between Event and User. Field names EventID, UserID which keeps track of users registered to the particular event.

User table is self explanatory with UserID plus whatever other fields are needed for the user.

Do you need to keep track of the "history" of created/deleted event, joined/left event? Then add appropriate tables accordingly and ensure the event is updated accordingly. e.g. add an entry to a table saying that a User was added or deleted to the particular event. You could do this via the business logic in the application itself, or perhaps with an update/insert trigger.

Presumably only the Creator of an event can change details such as the date and location, so you will need to take that into account in the way your application updates these.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.