Хранение отношений в базе данных MySQL

Question

Я создаю много-много взаимосвязей между контентом, но чтобы упростить это, я пока воспользуюсь более простыми отношениями. Примером могут служить отношения между фильмами. Как правильно хранить эти данные?

Я изначально делал это:

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

Таким образом, Matrix 2 является продолжением Matrix, но потом я понял, что не имеет смысла хранить отношение родственного фильма вместо отношения фактического фильма. Тогда я попробовал это вместо:

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

Теперь я сохраняю реальное отношение фильма вместо связанного с ним фильма, поэтому строка имеет больше смысла. Это также более буквально, Matrix является приквелом Matrix 2.

Однако затем я понял, что во втором интерфейсе это будет выглядеть так для страницы Matrix: Prequel - Matrix 2

И для Матрицы 2 страницы: продолжение - Матрица

Таким образом, первый способ, по-видимому, более правильно хранит данные в бэкэнде, но не во внешней части. И второй способ кажется, что он не хранит данные правильно в бэкэнде, но во внешнем интерфейсе это имеет больше смысла.

Так что в этом случае я должен хранить данные наоборот (2-й способ)? Должен ли я вообще беспокоиться об этом? Пока это имеет смысл в переднем конце?

Answer 1

Вам не нужно хранить как обратные, так и прямые отношения. В отличие от связанного списка, вам не нужно хранить prev а также next указатели, чтобы иметь возможность перемещаться вперед и назад.

Например, почему бы не сделать:

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 |
+----+----------+---------------+------------------+

Теперь, если вам нужно найти все продолжения Matrix:

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

Если вам нужно вместо этого найти все приквелы Matrix 2, вы знаете, что это просто список фильмов с related_movie_id 2 как продолжение:

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

Допустим, вам нужны все фильмы, связанные с матрицей 2 (которая имеет неявный приквел и непосредственно указанный ответвление):

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 |
+----+---------------+

Запрос немного сложнее, чем если бы вы хранили избыточные данные. Но я предпочитаю не дублировать информацию там, где это не нужно.