CODEWITHSUNDEEP
mysqldatabase
Valdrinium
Valdrinium

Reputation: 1416

Design dababase where every user has a list of favorites

I have a users database and a comments database on a website, and I want to give each user the ability to have a list of favorite comments, and I don't know exactly how to implement this.

Should I create a new table for each user containing the IDs of his/hers favorite comments or is there a better approach?

Upvotes: 0

Views: 1249

Answers (2)

tleb
tleb

Reputation: 4606

I would use three tables :

  1. The users table, containing every users, including their personal informations, etc ;
  2. The comments table, containing every comments ;
  3. The favorites table, containing the id of the row, the id of the user and the id of the comment.

I did not name each table well, you should think more about the tables' names (e.g: favorites is a bad name).

Upvotes: 1

Jason McCreary
Jason McCreary

Reputation: 73011

A common, normalized approach would be a many to many relationship.

The schema for such a table, at a minimum, would be:

user_id
comment_id

Where user_id and comment_id are foreign keys to the users and comments table, respectively.

Upvotes: 3

Related Questions