какое минимальное количество таблиц нужно для реализации связи вида один к одному для двух сущностей
Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Часть 3.2: Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты
Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Продолжаем изучать теорию реляционных баз данных и в этой части мы познакомимся с видами и типами связей между таблицами в реляционных базах данных. Так же мы познакомимся с такими термина, как: кортеж, атрибут и отношения. Данная тема является базовой и ее понимание необходимо для работы с базами данных и для их проектирования.
Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты.
Сразу скажу, что связей между таблицами в реляционной базе данных всего три. Поэтому их изучение, понимание и восприятие пройдет быстро, легко и безболезненно. Приступим к изучению.
Термины кортеж, атрибут и отношение в реляционных базах данных
В своей публикации я буду стараться объяснять теорию баз данных не с математической точки зрения, а на примерах. Грубо говоря, на пальцах. Во-первых, практические примеры позволяют легче усваивать материал. Во-вторых, с математической теорией проще разобраться, когда понимаешь суть происходящего.
Давайте разбираться с тем, что такое: отношение, кортеж, атрибут в реляционной базе данных.
Таблица с данными из базы данных World
У нас есть простая таблица City из базы данных World, в которой есть строки и столбцы. Но термины: таблица, строка, столбец – это термины стандарта SQL.
Кстати: ни одна из существующих в мире СУБД не имеет полной поддержки того или иного стандарта SQL, но и ни один стандарт SQL полностью не реализует математику реляционных баз данных.
В терминологии реляционных баз данных: таблица – это отношение (принимается такое допущение), строка – это кортеж, а столбец – атрибут. Иногда вы можете услышать, как некоторые разработчики называют строки записями. Чтобы не было путаницы в дальнейшем предлагаю использовать термины SQL.
Если рассматривать таблицу, как объект (например книга), то столбец – это характеристики объекта, а строки содержат информацию об объекте.
Виды и типы связей между таблицами в реляционных базах данных
Давайте теперь рассмотрим то, как могут быть связаны таблицы в реляционных базах данных. Сразу скажу, что всего существует три вида связей между таблицами баз данных:
• связь один к одному;
• связь один ко многим;
• связь многие ко многим.
Рассмотрим, как такие связи между таблицами могут быть реализованы в реляционных базах данных.
Реализация связи один ко многим в теории баз данных
Связь один ко многим в реляционных базах данных реализуется тогда, когда объекту А может принадлежать или же соответствовать несколько объектов Б, но объекту Б может соответствовать только один объект А. Не совсем понятно, поэтому смотрим пример ниже.
Реализация связи один ко многим в реляционных базах данных
У нас есть таблица, в которой содержатся данные о клиентах и у нас есть таблица, в которой хранятся их телефоны. Мы можем смело утверждать, что у одного клиента может быть несколько телефонов, но в тоже время мы можем быть уверены в том, что один конкретный номер может быть только у одного клиента. Это типичный пример связи один ко многим.
Связь многие ко многим
Связь многие ко многим реализуется в том случае, когда нескольким объектам из таблицы А может соответствовать несколько объектов из таблицы Б, и в тоже время нескольким объектам из таблицы Б соответствует несколько объектов из таблицы А. Рассмотрим простой пример.
Пример связи многие ко многим
У нас есть таблица с книгами и есть таблица с авторами. Приведу два верных утверждения. Первое: одну книгу может написать несколько авторов. Второе: автор может написать несколько книг. Здесь мы наблюдаем типичную ситуацию, когда связь между таблицами многие ко многим. Такая связь (связь многие ко многим) реализуется путем добавления третьей таблицы.
Связь один к одному
Связь один к одному – самая редко встречаемая связь между таблицами. В 97 случаях из 100, если вы видите такую связь, вам необходимо объединить две таблицы в одну.
Пример связи один к одному
Таблицы будут связаны один к одному тогда, когда одному объекту таблицы А соответствует один объект таблицы Б, и одному объекту таблицы Б соответствует один объект таблицы А. Как я уже говорил: если вы видите, что связь один к одному – смело объединяйте таблицы в одну, за исключением тех случаев, когда происходит модернизация базы данных.
Например, у нас была таблица, в которой хранились данные о сотрудниках компании. Но произошли какие-то изменения в бизнес-процессе и появилась необходимость создать таблицы с теми же самыми сотрудниками, но не для всей компании, а разбив их по отделам. Таблицы отделов будут дочерними по отношению к таблице, в которой хранятся данные обо всех сотрудниках компании, и связаны такие таблицы будут связью один к одному.
Мы рассмотрели все виды связей между таблицами и то, как они реализуются в базах данных. В дальнейшем, когда мы начнем создавать свои базы данных, информация о видах связи между таблицами нам очень поможет.
Создание связи «один к одному»
Проверьте, как это работает!
Что такое связь «один к одному»?
Связи «один к одному» часто используются для получения важных данных, необходимых для ведения бизнеса.
Связь «один-к-одному» — это связь между информацией из двух таблиц, когда каждая запись используется в каждой таблице только один раз. Например, связь типа «один-к-одному» может использоваться между сотрудниками и их служебными автомобилями. Каждый работник указан в таблице «Сотрудники» только один раз, как и каждый автомобиль в таблице «Служебный транспорт».
Связи «один-к-одному» можно использовать, если у вас есть таблица со списком элементов, но конкретные сведения о них зависят от типа. Например, у вас может быть таблица контактов, в которой некоторые сотрудники являются сотрудниками, а другие — субподрядчиками. Для сотрудников нужно знать их номера, расширения и другие ключевые сведения. Для субподрядчиков нужно знать, помимо прочего, название компании, номер телефона и тариф на выставление счета. В этом случае нужно создать три отдельные таблицы — «Контакты», «Сотрудники» и «Субподрядчики», а затем создать связь «один-к-одному» между таблицами «Контакты» и «Сотрудники» и связь «один-к-одному» между таблицами «Контакты» и «Субподрядчики».
Общие сведения о создании связи «один к одному»
Связи «один-к-одному» создаются путем связывания индекса первой таблицы, в качестве которого обычно выступает первичны ключ, с индексом второй таблицы, причем их значения совпадают. Пример:
Часто бывает, что лучший способ создать подобную связь — назначить вторичной таблице функцию поиска значений из первой таблицы. Например, вы можете сделать поле «Код автомобиля» в таблице «Сотрудники» полем подстановки, которое будет искать значение индекса «Код автомобиля» в таблице «Служебный транспорт». Таким образом исключается случайное добавление кода автомобиля, который на самом деле не существует.
Важно: При создании связи «один-к-одному» следует тщательно обдумать, требуется ли включать для нее обеспечение целостности данных.
Целостность данных помогает Access поддерживать порядок данных путем удаления связанных записей. Например, при удалении сотрудника из таблицы «Сотрудники» также удаляются записи о его льготах из таблицы «Льготы». Но в некоторых связях, таких как в этом примере, целостность данных не имеет смысла: если удалить сотрудника, мы не хотим, чтобы автомобиль удалялся из таблицы «Автомобиль компании», так как он по-прежнему будет принадлежать компании и будет назначен другому сотруднику.
Инструкции по созданию связи типа «один к одному»
Вы можете создать связь «один-к-одному», добавив в таблицу поле подстановки. (Инструкции см. в статье Создание таблиц и назначение типов данных.) Например, чтобы указать, какие автомобили назначены определенным сотрудникам, вы можете добавить в таблицу «Сотрудники» поле «Код автомобиля». После этого воспользуйтесь мастером подстановок для создания связи между полями.
В режиме конструктора добавьте новое поле, выберите значение Тип данных, а затем запустите мастер подстановок.
В мастере по умолчанию выбран поиск значений в другой таблице, поэтому нажмите кнопку Далее.
Выберите таблицу с ключом (обычно первичным), который вы хотите добавить в первую таблицу, и нажмите кнопку Далее. В рассмотренном примере следует выбрать таблицу «Служебный транспорт».
Добавьте в список Выбранные поля поле с необходимым ключом. Нажмите кнопку Далее.
Задайте порядок сортировки и, при необходимости, измените ширину поля.
В последнем окне установите флажок Включить проверку целостности данных и нажмите кнопку Готово.
Russian (Pусский) translation by Yuri Yuriev (you can also view the original English article)
Сегодня мы продолжаем наше путешествие в мир SQL и связанных баз данных. В третьей части этой серии мы узнаем, как работать с несколькими таблицами, которые имеют отношения друг с другом. Во-первых, мы рассмотрим некоторые базовые концепции, а затем начнем работать с JOIN queries в SQL.
Вы также можете увидеть базы данных SQL в действии, просмотрев SQL scripts, apps and add-ons на рынке Envato.
Напоминание
Введение
При создании базы данных здравый смысл подсказывает, что мы используем отдельные таблицы для разных типов сущностей. Например: клиенты, заказы, предметы, сообщения. Но нам также нужно иметь отношения между этими таблицами. Например, клиенты делают заказы, а заказы содержат предметы. Эти отношения должны быть представлены в базе данных. Кроме того, при получении данных с помощью SQL нам нужно использовать определённые типы запросов JOIN, чтобы получить то, что нам нужно.
Существует несколько типов отношений базы данных. Сегодня мы рассмотрим следующее:
При выборе данных из нескольких таблиц с отношениями мы будем использовать запрос JOIN. Существует несколько типов JOIN, и мы собираемся узнать следующее:
Мы также узнаем об оговорках ON и USING.
Отношения один к одному
Предположим, у вас есть таблица для клиентов:
Мы можем поместить информацию об адресе клиента в отдельную таблицу:
Теперь мы имеем отношение между таблицей Customers и таблицей Addresses. Если каждый адрес может принадлежать только одному клиенту, это отношение «Один к одному». Имейте в виду, что такого рода отношения не очень распространены. Наша начальная таблица, которая включала адрес вместе с клиентом, в большинстве случаев могла работать нормально.
Обратите внимание: теперь в таблице Customers есть поле с именем «address_id», которое ссылается на запись соответствия в таблице Address. Это называется «Foreign Key» и используется для всех видов отношений баз данных. Мы рассмотрим этот вопрос позже.
Мы можем показать отношения между клиентскими и адресными записями следующим образом:
Обратите внимание, что существование отношений может быть необязательным, например, есть запись клиента, у которой нет связанной записи адреса.
Отношения «один ко многим» и «многие к одному»
Это наиболее часто используемый тип отношений. Рассмотрим веб-сайт e-commerce со следующим:
В этих случаях нам необходимо создать отношения «один ко многим». Вот пример:
У каждого клиента может быть ноль, один или несколько заказов. Но заказ может принадлежать только одному клиенту.
Отношения «многие ко многим»
В некоторых случаях вам может потребоваться несколько экземпляров с обеих сторон. Например, каждый заказ может содержать несколько элементов. И каждый элемент также может быть в нескольких заказах.
Для этих отношений нам нужно создать дополнительную таблицу:
Таблица Items_Orders имеет только одну цель, а именно, чтобы создать отношение «многие ко многим» между элементами и заказами.
Вот картинка таких отношений:
Если вы хотите включить записи items_orders в график, это может выглядеть так:
Самостоятельные ссылки
Это используется, когда таблица должна иметь отношения с самой собой. Например, у вас есть реферальная программа. Клиенты могут направлять других клиентов на ваш веб-сайт. Таблица может выглядеть так:
Клиенты 102 и 103 были переданы клиентом 101.
На самом деле это может быть похоже на отношение «один ко многим», поскольку один клиент может ссылаться на нескольких клиентов. Также он может выглядеть, как древовидная структура:
Один клиент может ссылаться на ноль, одного или несколько клиентов. К каждому клиенту может обращаться только один клиент, или вообще никто.
Если вы хотите создать самостоятельную ссылку «многие ко многим», вам понадобится дополнительная таблица, вроде той, что мы говорили в предыдущем разделе.
Foreign Keys
До сих пор мы узнали только о некоторых концепциях. Теперь пришло время воплотить их в жизнь с помощью SQL. Для этой части нам нужно понять, что такое Foreign Keys.
В приведённых выше примерах отношений мы всегда имели эти поля «**** _ id», которые ссылались на столбец в другой таблице. В этом примере столбец customer_id в таблице Orders является столбцом Foreign Key:
В базе данных типа MySQL есть два способа создания столбцов внешних ключей:
Чёткое определение Foreign Key
Давайте создадим простую таблицу клиентов:
Теперь таблицу заказов, в которой будет Foreign Key:
Оба столбца (customers.customer_id и orders.customer_id) должны иметь одинаковую структуру данных. Если один является INT, другой не должен быть BIGINT, например.
Обратите внимание, что в MySQL только механизм InnoDB имеет полную поддержку Foreign Keys. Но другие механизмы хранения данных по-прежнему позволят вам указывать их без каких-либо ошибок. Кроме того, столбец Foreign Key индексируется автоматически, если не указать для него другой индекс.
Без явной декларации
Та же таблица заказов может быть создана без явного объявления столбца customer_id как Foreign Key:
Далее мы собираемся узнать о JOIN-запросах.
Визуализация отношений
Моим любимым программным обеспечением для проектирования баз данных и визуализации отношений Foreign Key является MySQL Workbench.
После разработки базы данных вы можете экспортировать SQL и запустить его на своем сервере. Это очень удобно для больших и сложных баз данных.
JOIN Queries
Для извлечения данных из базы, имеющей отношения, нам часто приходится использовать JOIN queries.
Прежде чем начать, давайте создадим таблицы и некоторые образцы данных для работы.
У нас 4 клиента. У одного клиента два заказа, у двух клиентов по одному заказу, а у одного клиента нет заказа. Теперь давайте посмотрим различные виды JOIN queries, которые мы можем запустить в этих таблицах.
Перекрестное соединение
Это тип JOIN query по умолчанию, если условие не указано.
Результатом является так называемый «Cartesian product» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Так как каждая таблица имела 4 строки, мы получили результат из 16 строк.
Ключевое слово JOIN может быть опционально заменено запятой.
Конечно, такой результат не очень полезен. Давайте посмотрим на другие типы соединений.
Обычное соединение
При таком типе JOIN query таблицы должны иметь имя соответствующего столбца. В нашем случае обе таблицы имеют столбец customer_id. Таким образом, MySQL будет присоединяться к записям только тогда, когда значение этого столбца соответствует двум записям.
Внутреннее соединение
Когда указано условие соединения, выполняется Inner Join. В этом случае было бы неплохо иметь поле customer_id в обеих таблицах. Результаты должны быть похожими на Natural Join.
Результаты те же, за исключением небольшой разницы. Столбец customer_id повторяется дважды, один раз для каждой таблицы. Причина в том, что мы просто попросили базу данных соответствовать значениям этих двух столбцов. Но сами они не знают, что представляют одну и ту же информацию.
Давайте добавим еще несколько условий в запрос.
ON Clause
Прежде чем перейти к другим типам соединений, нам нужно посмотреть ON clause. Это полезно для помещения условий JOIN в отдельное предложение.
Теперь мы можем отличить условие JOIN от условий WHERE. Но есть и небольшая разница в функциональности. Мы увидим это в примерах LEFT JOIN.
USING Clause
USING clause похоже на предложение ON, но оно короче. Если столбец имеет одинаковое имя в обеих таблицах, мы можем указать его здесь.
На самом деле это похоже на NATURAL JOIN, поэтому столбец join (customer_id) не повторяется дважды в результатах.
Левое (внешнее) соединение
Хотя у Энди нет заказов, его запись все ещё отображается. Значения под столбцами второй таблицы имеют значение NULL.
Это полезно для поиска записей, которые не имеют отношений. Например, мы можем искать клиентов, которые не разместили какие-либо заказы.
Всё, что мы сделали, это нашли NULL для order_id.
Также обратите внимание, что ключевое слово OUTER является необязательным. Вы можете просто использовать LEFT JOIN вместо LEFT OUTER JOIN.
Условия
Теперь давайте рассмотрим запрос с условием.
Так что случилось с Энди и Сэнди? LEFT JOIN должен был вернуть клиентов без соответствующих заказов. Проблема в том, что предложение WHERE блокирует эти результаты. Чтобы их получить, мы можем попытаться включить условие NULL.
У нас Энди, но нет Сэнди. Тем не менее это выглядит не так. Чтобы получить то, что мы хотим, нам нужно использовать ON clause.
Правое (внешнее) соединение
RIGHT OUTER JOIN работает точно так же, но порядок таблиц обратный.
На этот раз у нас нет результатов NULL, потому что каждый заказ имеет соответствующую запись клиента. Мы можем изменить порядок таблиц и получить те же результаты, что и в LEFT OUTER JOIN.
Теперь у нас есть эти значения NULL, потому что таблица Customers находится на правой стороне соединения.
Заключение
Спасибо, что прочитали статью. Надеюсь, вам понравилось! Пожалуйста, оставляйте свои комментарии и вопросы, и хорошего дня!
Не забудьте проверить SQL scripts, apps and add-ons на рынке Envato. Вы получите представление о возможностях баз данных SQL, и сможете найти идеальное решение, которое поможет вам в текущем проекте разработки.
Следуйте за нами на Twitter или подпишитесь на Nettuts + RSS Feed для получения лучших обучающих материалов по веб-разработке в Интернете.