Отношения в SQL pt. 1 - Таблицы для проектирования

Я использовал MongoDB в течение большей части моей ранней разработки Javascript, и был избалован тем, насколько простым был мангуст. Однако по мере того, как вы углубляетесь в бэкэнд-разработку, становится трудно игнорировать SQL.

Вы заметите много различий между SQL и mongoose - в частности, отсутствие эквивалента для mongoose для систем баз данных SQL. И теперь, поскольку все хранится в таблицах (а не в объектах), отношения также обрабатываются по-другому.

Типы отношений

Для меня это была новая концепция при использовании баз данных SQL. Пуритане SQL будут утверждать, что существует только два типа отношений: Многие-к-одному и Многие-ко-многим (с небольшим подмножеством, относящимся к Нет как другой тип отношений).

При их определении очень важно направление. Мы подойдем к этому с точки зрения таблицы и рассмотрим пять типов отношений: - Соединение-к-одному, Один-к-соединению, Многие-к -Один, Один-ко-многим и Многие-ко-многим. Однако по пути вы познакомитесь с еще одним или двумя типами для завершения.

Один к одному

Подождите, это даже не один из указанных вами родственников.

Итак, чтобы понять, где подходит Соединение, мы должны сначала взглянуть на взаимно-однозначные отношения и понять, почему ни одно из них (один-к-одному, соединение-к-одному и один-к-соединению ) Рекомендовано. Мы будем использовать две таблицы USER и PROFILE. Взаимосвязь заключается в том, что каждый Пользователь может иметь только один Профиль, а каждый Профиль может принадлежать только одному Пользователю. Изначально две таблицы будут выглядеть примерно так:

Однако, если между этими двумя таблицами существует истинная взаимно-однозначная связь, их вообще не должно быть. Эту структуру таблицы лучше всего оптимизировать путем объединения двух таблиц вместе для формирования одной таблицы, содержащей оба набора полей:

Однако нам все еще могут понадобиться две отдельные таблицы по разным причинам (за счет эффективных запросов к базе данных). Одна таблица может содержать конфиденциальные данные, или ожидается, что простое ее существование будет временным. В этом случае нам все равно придется изобрести способ связывания или объединения строк между двумя таблицами.

Присоединяйтесь к одному

Строки в таблице USER не точно совпадают со строками в таблице PROFILE. Необходимо добавить еще один столбец, чтобы связать одну таблицу с другой. В истинных отношениях «один к одному» не имеет значения, в какой таблице это происходит. Ключевое слово Присоединиться помогает идентифицировать таблицу, содержащую этот столбец.

В этом примере таблица USER имеет отношение соединение к одному с таблицей PROFILE и, таким образом, будет содержать столбец profile_id. Идентифицирует строку в таблице PROFILE, которая связана с пользователем.

USER.profile_id will have 2 constraints
- FOREIGN key constraint (referencing the PROFILE table)
- UNIQUE constraint, as the relationship is one-to-one

Один-к-присоединиться

Если бы вместо этого ссылочный столбец был сохранен в таблице PROFILE, таблица USER имела бы отношение один-к-соединению с ПРОФИЛЬ таблицы. И наоборот, таблица PROFILE будет иметь отношение соединения к одному с таблицей USER. Две таблицы будут выглядеть примерно так: -

PROFILE.user_id will have 2 constraints
- FOREIGN key constraint (referencing the USER table)
- UNIQUE constraint, as the relationship is one-to-one

Присоединяйтесь к присоединению

!!!! НЕ РЕКОМЕНДУЕТСЯ !!!!

Не было бы лучше, если бы обе таблицы содержали reference_ids, чтобы я мог легко получить доступ к одной из другой?

Нет. Нет, не пойдет. Ошибки неизбежно возникают при вставке и обновлении данных. Они будут выброшены при удалении строк. Циклическая ссылка могла быть хорошей в MongoDB, но здесь она определенно не рекомендуется.

Выберите таблицу, в которой будет столбец объединения, и придерживайтесь его! Или, еще лучше, просто объедините две таблицы вместе.

Однако для любопытных это будет выглядеть так:

PROFILE.user_id will have 2 constraints
- FOREIGN key constraint (referencing the USER table)
- UNIQUE constraint, as the relationship is one-to-one
USER.profile_id will have 2 constraints
- FOREIGN key constraint (referencing the PROFILE table)
- UNIQUE constraint, as the relationship is one-to-one

Один ко многим

Давайте воспользуемся здесь двумя разными таблицами: АВТОР и КНИГА.

Каждая книга может принадлежать автору, но несколько книг могут иметь одного и того же автора. Другими словами, таблица AUTHOR имеет отношение один ко многим с таблицей BOOK. (У каждого автора может быть несколько книг)

В MongoDB и других базах данных NoSQL вы можете хранить это как массив с вашим документом.

{
  id: 1,
  name: JK Rowling,
  dob: 1965, 
  Books: [1, 2]
}
As long as Author.Books references the Book schema/model. 

Это не работает и в SQL по нескольким причинам. Первый - это неспособность многих систем управления реляционными базами данных хранить массивы. И если массивы могут быть сохранены посредством сериализации или преобразования в строку, тогда теряется возможность легко просеивать элементы в массиве. Postgres поддерживает типы массивов, и их можно запрашивать с помощью оператора ANY. Это полезно, но все же не дает представления о том, как следует проектировать отношения "один ко многим".

Вместо этого, поскольку у каждой книги может быть только один автор, идентификатор автора следует сохранить в виде столбца в КНИГЕ таблица. Это избавляет нас от необходимости сохранять массивы любого типа.

BOOK.Author_id will have 1 constraint
- FOREIGN key constraint (referencing the AUTHOR table)

Это очень естественно приводит нас к ...

Многие к одному

Это наша первая настоящая SQL-связь. Используя тот же пример, что и выше, мы можем сказать, что таблица BOOK имеет отношение многие-к-одному с таблицей AUTHOR. Столбец внешнего ключа хранится в таблице BOOK со ссылкой на идентификатор соответствующего автора.

BOOK.Author_id will have 1 constraint
- FOREIGN key constraint (referencing the AUTHOR table)

Многие ко многим

Но если мы не можем хранить массивы, как мы можем иметь дело с отношениями «многие ко многим»! Давайте создадим здесь новую таблицу GENRE, в которой для каждой книги может быть несколько жанров и каждый жанр может быть связан со множеством книг.

Идея состоит в том, чтобы создать совершенно новую таблицу - RELATION_BOOK_GENRE. В этой новой таблице будут существовать два столбца: один относится к таблице BOOK, а другой - к таблице Genre. Чтобы снизить риск избыточности, мы также можем ввести ограничение, гарантирующее, что никакие две строки в этой таблице не могут быть идентичными.

Relation_Book_Genre will have 3 constraints
- FOREIGN key constraint on book_id (referencing the BOOK table)
- FOREIGN key constraint on genre_id (referencing the GENRE table)
- UNIQUE key constraint on ( book_id, genre_id )

Никто

В завершение я должен упомянуть отношение None. Это когда таблица не имеет отношения к другой таблице.

Вывод

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

Следующая статья будет посвящена созданию таблиц в SQL с учетом отношений.