Storing relationship in mysql database

Question

I'm creating a many to many relationship between content, but to simplify it I'll use an easier relationship for now. An example would be a relationship between movies. What would be the right way to store this data?

I was originally doing this:

Movie | Related Movie | Relation (Relation of the related movie)
--------------------------------
Matrix   | Matrix 2 | Sequel
Matrix 2 | Matrix   | Prequel

So Matrix 2 is the sequel of Matrix, but then I realized it doesn't seem to make sense to store the relation of the related movie instead of the relation of the actual movie. So then I tried this instead:

Movie | Relation | Related Movie 
--------------------------------
Matrix   | Prequel | Matrix 2
Matrix 2 | Sequel  | Matrix

Now I'm storing the actual relation of the movie instead of the related movie, so the row makes more sense. It's also more literal, Matrix is the prequel of Matrix 2.

However, then I realized in the front end using the 2nd way it would look like this for the Matrix page: Prequel - Matrix 2

And for the Matrix 2 page: Sequel - Matrix

So the first way seems to store the data more correctly in the backend, but not the front end. And the second way seems that it doesn't store the data correctly in the backend, but in the front end it makes more sense.

So in this case, should I actually store the data the other way around (2nd way)? Should I even be concerned about this at all? As long as it makes sense in the front end?

Answer 1

You needn't store both the backwards and forwards relations. Unlike a linked list, you don't have to store the prev and next pointers to be able to traverse backwards and forwards.

For example, why not do:

SELECT * FROM movies;
+----+---------------+
| id | title         |
+----+---------------+
|  1 | Matrix        |
|  2 | Matrix 2      |
|  3 | The Animatrix |
+----+---------------+

SELECT * FROM movie_relations;
+----+----------+---------------+------------------+
| id | movie_id | relation_type | related_movie_id |
+----+----------+---------------+------------------+
|  1 |        1 | sequel        |                2 |
|  2 |        1 | offshoot      |                3 |
|  3 |        2 | offshoot      |                3 |
+----+----------+---------------+------------------+

Now, if you need to find all sequels of Matrix:

SELECT related_movie_id FROM movie_relations 
WHERE movie_id = 1 AND relation_type = 'sequel'

If you need to instead find all prequels of Matrix 2, you know that this is simply the list of movies with related_movie_id 2 as a sequel:

SELECT movie_id FROM movie_relations 
WHERE related_movie_id = 2 AND relation_type = 'sequel'

Let's say you need all movies related to Matrix 2 (which has an implicit prequel and a directly specified offshoot):

SELECT DISTINCT(movies.id), title FROM movies 
    LEFT JOIN movie_relations mr_direct ON mr_direct.related_movie_id = movies.id 
    LEFT JOIN movie_relations mr_implicit ON mr_implicit.movie_id = movies.id 
WHERE mr_direct.movie_id = 2 OR mr_implicit.related_movie_id = 2;

+----+---------------+
| id | title         |
+----+---------------+
|  1 | Matrix        |
|  3 | The Animatrix |
+----+---------------+

The query is slightly more complex than if you had stored redundant data. But, I prefer to not duplicate information where unnecessary.