How to design this simple database?

Question

I have 2 tables - one storing user information (id, username, password) and the second one storing information about events (id, name, description, date, username(represents the user who created the event)). I would like to implement 'favourite events' functionality. This would allow the user to store his favourite events and later display them in a list. I am not sure how to implement this in terms of design. I need a simple solution. Something like storing the IDs of favourite events in a field in the user table. I am using mysql and php. Can anyone point me to the right direction?

Answer 1

You want to have a table linking the foreign keys from the user and event tables.

Users Table:

id, username, password

Events Table:

id, name, description, date, username

Favorites Table:

id, user_id, event_id

This way you can easily access the list of favorite events.

SELECT events.name, events.description, events.date
FROM events, users, favorites
WHERE favorites.user_id = users.id
AND favorites.event_id = events.id

Answer 2

What you need is the most classic and basic many-to-many relationship.

You'll need extra table (let's say: user_event_ref) that will store user and event ids.

User:
  id
  name

Event:
  id
  name

UserEventRef:
  user_id
  event_id

In usereventref each column is a Foreign Key, and both columns are parts of Primary Key.

Answer 3

There's always the option to add a tiny-int field to the Events table flagging an event as a favorite. This doesn't violate normalization in that whether or not an even is a favorite has no effect on the other events. It has the added benefit of automatically deleting the event from favorites if the event is deleted.

If a sorting scheme is needed for the favorites you can still modify the events table in the same manner. If details about the "favorite" such as when it was added to the list etc is needed then you should use an additional table as suggested.