System for keeping track of user favorites

Question

On my website, I have a table movies and a table users

I'm trying to have an "Add to favs" button that a user can click, which will add that movie to his favorites (ajax / javascript not necessary at the moment, just php).

So what's the simplest way I could do something like that? I've thought about this but I can't seem to find a solution (all I think of is way too complicated, and in my opinion not possible).

What's your thoughts?

I don't need a ready-made script, just an idea that could get me working (although if you have an example of such script, I'd be happy to look at it).

Thanks!

Answer 1

This is a many-to-many relationship. A user can favorite many movies, and a movie can be favored by many users. In an RDBMS, you represent a many-to-many relationship with a third table. I call this an intersection table but it goes by other names too.

Create a table with two columns. The columns are both foreign keys, referencing movies and users, respectively.

CREATE TABLE Favorites (
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  PRIMARY KEY (user_id, movie_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
); 

When a user chooses to favorite a movie:

INSERT INTO Favorites (user_id, movie_id) VALUES (?, ?)

When a user decides they don't like a movie any longer, delete the corresponding row:

DELETE FROM Favorites WHERE (user_id, movie_id) = (?, ?)

To get the set of movies favored by a given user:

SELECT movie_id FROM Favorites WHERE user_id = ?

To get the set of users who favor a given movie:

SELECT user_id FROM Favorites WHERE movie_id = ?

---

Regarding one of your comments:

You shouldn't make the "Add to favorite" a link. Indexers like Google will follow links, and then before you know it, every user has favorited every movie.

The general best practice is that read-only operations can be GET requests, while operations that write to the database can be POST requests. This means that you need to use a <form> element to submit POST requests, not an <a href="..."> element.