для чего нужен первичный ключ в базе данных
Для чего нужен первичный ключ в базе данных
Под базой данных (БД) понимают хранилище структурированных данных, при этом данные должны быть непротиворечивы, минимально избыточны и целостны.
Одно из важнейших достоинств реляционных баз данных состоит в том, что можно хранить логически сгруппированные данные в разных таблицах и задавать связи между ними, объединяя их в единую базу. Такая организация данных позволяет уменьшить избыточность хранимых данных, упрощает их ввод и организацию запросов и отчетов.
Понятие первичного ключа
В каждой таблице БД может существовать первичный ключ. Под первичным ключом понимают поле или набор полей, однозначно (уникально) идентифицирующих запись. Первичный ключ должен быть минимально достаточным: в нем не должно быть полей, удаление которых из первичного ключа не отразится на его уникальности.
Данные таблицы «Преподаватель»
В качестве первичного ключа в таблице «Преподаватель» может выступать только «Таб. №», значения других полей могут повторяться внутри данной таблицы.
Правила хорошего тона при разработке структур баз данных, и чисто практические соображения должны побудить разработчика всегда определять первичный ключ для таблицы базы данных.
Реляционные отношения (связи) между таблицами базы данных
Существует три разновидности связей между таблицами базы данных:
Отношение «один-ко-многим» имеет место, когда одной записи родительской таблицы может соответствовать несколько записей в дочерней таблице.
Связь «один-ко-многим» является самой распространенной для реляционных баз данных.
В широко распространенной нотации структуры баз данных IDEF 1 X отношение « один-ко-многим » изображается путем соединения таблиц линией, которая на стороне дочерней таблицы оканчивается кружком или иным символом. Поля, входящие в первичный ключ для данной ТБД, всегда расположены вверху и отчеркнуты от прочих полей линией.
Отношение «один-к-одному» имеет место, когда одной записи в родительской таблице соответствует одна запись в дочерней таблице.
Данное отношение используют, если не хотят, чтобы таблица БД «не распухала» от второстепенной информации.
Отношение «многие-ко-многим» имеет место, когда:
а) записи в родительской таблице может соответствовать больше одной записи в дочерней таблице;
б) записи в дочерней таблице может соответствовать больше одной записи в родительской таблице.
Например, каждой студент изучает несколько дисциплин. Каждая дисциплина изучается несколькими студентами.
Многие СУБД (в частности Access ) не поддерживают связи «многие-ко-многим» на уровне индексов и ссылочной целостности. Считается, что всякую связь «многие-ко-многим» можно заменить на одну или более связей «один-ко-многим».
Ссылочная целостность и каскадные воздействия
Рассмотрим наиболее часто встречающуюся в базах данных связь «один-ко-многим». Как можно заметить, дочерняя и родительская таблицы связаны между собой по общему полю «Шифр группы». Назовем это поле полем связи.
Возможны два вида изменений, которые приведут к утере связей между записями в родительской и дочерней таблицах:
· изменение значения поля связи в записи родительской таблицы без изменения значений полей связи в соответствующих записях дочерней таблицы;
· изменение значения поля связи в одной из записей дочерней таблицы без соответствующего изменения значения полей связи в родительской и дочерней таблицах.
Чтобы предотвратить потерю ссылочной целостности, используется механизм каскадных изменений. Он состоит в обеспечении следующих требований:
· необходимо запретить изменение поля связи в записи дочерней таблицы без синхронного изменения полей связи в родительской таблице;
· при изменении поля связи в записи родительской таблице, следует синхронно изменить значения полей связи в соответствующих записях дочерней таблицы;
· при удалении записи в родительской таблице, следует удалить соответствующие записи в дочерней таблице.
Необходимость разрешения или запрещения каскадных изменений обычно реализуется в СУБД при определении связей между таблицами. Собственно, таким образом, и происходит создание ссылочной целостности.
Понятие внешнего ключа
Для обеспечения ссылочной целостности в дочерней таблице создается внешний ключ. Во внешний ключ входят поля связи дочерней таблицы. Для связей типа «один-ко-многим» внешний ключ по составу полей должен совпадать с первичным ключом родительской таблицы.
Индексы и методы доступа
Индексы представляют собой механизмы быстрого доступа к данным в таблицах БД.
Сущность индексов состоит в том, что они хранят значения индексных поле (т.е. полей, по которым построен индекс) и указатель на запись в таблице.
При последовательном методе доступа для выполнения запроса к таблице БД просматриваются все записи таблицы, от первой до последней.
При индексно-последовательном методе доступа для выполнения запроса к таблице БД указатель в индексе устанавливается на первую строку, удовлетворяющую условию запроса (или его части), и считывается запись из таблицы по хранящемуся на нее в индексе указателю.
Определение первичных и внешних ключей таблиц БД приводят к созданию индексов по полям, объявленным в составе первичных или внешних ключей.
Нормализация таблиц при проектировании БД
На этом этапе процесс проектирования структур БД является процессом творческим, неоднозначным, с другой стороны, узловые его моменты могут быть формализованы.
Одной из таких формализаций является требование, согласно которому реляционная база данных должна быть нормализована. Процесс нормализации имеет своей целью устранение избыточности данных и заключается в приведении к третьей нормальной форме (3НФ).
Первая нормальная форма (1НФ) требует, чтобы каждое поле таблицы БД:
· не содержало повторяющихся групп.
Неделимость поля означает, что значение поля не должно делиться на более мелкие значения. Например, если в поле «Подразделение» содержится название факультета и название кафедры, требование неделимости не соблюдается и необходимо из данного поля выделить или название факультета, или кафедры в отдельное поле.
Повторяющимися являются поля, содержащие одинаковые по смыслу значения. Например, если требуется получить статистику сдачи экзаменов по предметам, можно создать поля для хранения данных об оценке по каждому предмету. Однако в этом случае мы имеем дело с повторяющимися группами.
Вторая нормальная форма (2НФ) требует, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно определял запись и не был избыточен. Те поля, которые зависят только от части первичного ключа, должны быть выделены в составе отдельных таблиц.
Третья нормальная форма (ЗНФ) требует, чтобы значение любого поля таблицы, не входящего в первичный ключ, не зависело от значения другого поля, не входящего в первичный ключ.
Пример логической модели базы данных «Сессия»
BestProg
Реляционные базы данных. Понятие ключа. Виды ключей. Отношения. Главная и подчиненная таблицы
В данной теме, на примере двух таблиц, определяются основные понятия реляционных баз данных, а именно:
Содержание
Поиск на других ресурсах:
Входные данные
Пусть задана база данных работников предприятия, которая состоит из двух таблиц. Первая таблица содержит данные о работнике. Вторая таблица содержит сведения о заработной плате работника.
Таблицы имеют следующую структуру.
Таблица «Работник». Содержит данные о работнике
Таблица «Зарплата». Содержит сведения о заработной плате работников.
Вопрос/ответ
1. Что такое первичный ключ в таблице базы данных? Для чего используются первичные ключи?
При работе с таблицами в реляционных базах данных, желательно (необходимо), чтобы каждая таблица имела так называемый первичный ключ.
Первичный ключ – это поле, которое используется для обеспечения уникальности данных в таблице. Это означает, что значение (информация) в поле первичного ключа в каждой строке (записи) таблицы может быть уникальным.
Уникальность необходима во избежание неоднозначности, когда неизвестно к какой записи таблицы можно обратиться, если в таблице есть повторяющиеся записи (две записи имеют одинаковые значения во всех полях таблицы).
Пример. Для таблицы «Работник» можно ввести дополнительное поле, которое будет первичным ключом. Однако, поле (атрибут) «Табельный номер» также обеспечивает уникальность. Так как, теоретически, не может быть двух одинаковых табельных номеров. На практике могут быть случаи, что один и тот же табельный номер будет введен по ошибке и совпадут значения всех полей таблицы. В результате возникнут два одинаковых записи в таблице. Во избежание такой ошибки, лучше создать в таблице дополнительное поле-счетчик, которое обеспечит уникальность.
Также для таблицы «Зарплата» можно ввести дополнительное поле, которое будет первичным ключом.
2. Что такое отношение (связь) между таблицами (relationship)? Пример
Таблицы в реляционной модели данных могут иметь связи между собой. Такие связи называются отношениями. Для таблиц «Работник» и «Зарплата» можно установить связь по полю «Табельный номер».
Пример. Проанализируем таблицы «Работник» и «Зарплата». В этих таблицах можно установить отношение между таблицами на основе поля «Табельный номер». То есть, связь между таблицами происходит на основе поля (атрибуту) «Табельный номер».
Это означает следующее. Если нужно найти начисленную заработную плату в таблице «Зарплата» для работника Иванов И.И., то нужно выполнить следующие действия:
Рис. 1. Иллюстрация связи между таблицами. Табельный номер 2145 таблицы «Работник» отображается в таблице «Зарплата»
Рис. 2. Связь (отношение) между полями таблиц
3. Что такое внешний ключ (foreign key)? Пример
Понятие «внешний ключ» есть важным при рассмотрении связанных таблиц.
Внешний ключ – это одно или несколько полей (атрибутов), которые являются первичными в другой таблице и значение которых заменяется значениями первичного ключа другой таблицы.
Пример. Пусть между таблицами «Работник» и «Зарплата» существует взаимосвязь по полю «Табельный номер». В этом случае, поле «Табельный номер» таблицы «Работник» может быть первичным ключом, а поле «Табельный номер» таблицы «Зарплата» внешним ключом. Это означает, что значения поля «Табельный номер» таблицы «Зарплата» заменяются значениями поля «Табельный номер» таблицы «Работник».
4. Что такое рекурсивный внешний ключ?
Рекурсивный внешний ключ – это внешний ключ, который ссылается на одну и ту же таблицу, к которой он принадлежит. В этом случае поле (атрибут), которое соответствует внешнему ключу, есть ключом одного и того же отношения (связи).
5. Могут ли первичный и внешний ключи быть простыми или составными (сложными)?
Первичный, вторичный и внешний ключи могут быть как простыми так и составными (сложными). Простые ключи – это ключи, которые содержат только одно поле (один атрибут). Составные (сложные) ключи – это ключи, которые содержат несколько полей (атрибутов).
6. Какое отличие между искусственным и естественным ключом? Пример
Естественной ключ обеспечивает уникальность из самой сущности предметной области. Бывают случаи, когда значения записей некоторого поля (полей) таблицы есть уникальными. Это поле может быть естественным ключом.
Искусственный ключ вводится дополнительно для обеспечения уникальных значений. Чаще всего искусственный ключ есть полем типа счетчик (counter). В таком поле, при добавлении новой записи (строки) в таблицу, значение счетчика увеличивается на 1 (или другую величину). Если запись удалить из таблицы, то максимальное значение счетчика строк уже не уменьшается, а остается как есть. Как правило, за этим все следит система управления базами данных.
Пример. В таблице «Работник» естественном ключом есть поле (атрибут) «Табельный номер». Поле «Табельный номер» есть само по себе уникальным, так как не может быть двух работников с одинаковым табельным номером.
В таблице «Зарплата» значение во всех четырех полях могут случайно повториться. Поэтому, здесь целесообразно добавить дополнительное поле-счетчик, которое будет искусственным ключом. В этом случае таблица «Зарплата» с дополнительным полем может иметь приблизительно следующий вид:
где поле «Номер» есть искусственным ключом, который обеспечивает уникальность.
7. Какие существуют способы выбора первичного ключа?
Существует 3 способа выбора первичного ключа:
8. Что означают термины «главная таблица» (master) и «подчиненная таблица» (detail)?
Если между таблицами есть связь, то одна из них может быть главной (master), а другая подчиненной (detail). Главная таблица отображает все записи, которые помещаются в ней. Подчиненная таблица отображает только те записи, которые соответствуют значению ключа главной таблицы, который на данный момент есть активным (текущим). Если изменяется текущая запись главной таблицы, то изменяется множество доступных записей подчиненной таблицы.
Пример. Если рассмотреть таблицы «Работник» и «Зарплата», то таблица «Работник» есть главной, а таблица «Зарплата» есть подчиненной.
9. Какие существуют типы отношений (связей) между таблицами?
Существует 4 основных типа отношений между таблицами:
Пример. Если рассмотреть отношение между таблицами «Работник» и «Зарплата», то это отношения есть типа «один ко многим». Таблица «Работник» есть главной. Таблица «Зарплата» есть подчиненной.
Про uuid-ы, первичные ключи и базы данных
Статья посвящена альтернативным версиям Qt-драйверов для работы с базами данных. По большому счету отличий от нативных Qt-драйверов не так много, всего пара: 1) Поддержка типа UUID; 2) Работа с сущностью «Транзакция» как с самостоятельным объектом. Но эти отличия привели к существенному пересмотру кодовой реализации исходных Qt-решений и изменили подход к написанию рабочего кода.
Первичный ключ: UUID или Integer?
Впервые с идеей использовать UUID в качестве первичного ключа я познакомился в 2003 году, работая в команде дельфистов. Мы разрабатывали программу для автоматизации технологических процессов на производстве. СУБД в проекте отводилась существенная роль. На тот момент это была FireBird версии 1.5. По мере усложнения проекта появились трудности с использованием целочисленных идентификаторов в качестве первичных ключей. Опишу пару сложностей:
Проблема архитектурная: периодически заказчики присылали справочные данные с целью включения их в новую версию дистрибутива. Иногда справочники содержали первичные ключи уже имеющиеся в нашей базе. Приходилось устранять коллизии в процессе агрегирования данных. На этом проблемы не заканчивались: при разворачивании нового дистрибутива периодически возникали обратные коллизии.
Проблема программная: чтобы получить доступ к вставленной записи нужно было выполнить дополнительный SELECT-запрос, который возвращал максимальное значение первичного ключа (значение для только что вставленной записи). Причем этот процесс должен был проходить в пределах одной транзакции. Далее можно было обновлять или корректировать запись. Это сейчас я знаю, что некоторые драйверы БД возвращают значение первичного ключа для вставленной записи, но в 2003 году мы такими знаниями не обладали, да и не припомню что бы Делфи-компоненты возвращали что-то подобное.
Использование UUID-ов в качестве первичных ключей сводило к минимуму архитектурную проблему, и полностью решало программную. UUID-ключ генерировался перед началом вставки записи на стороне программы, а не в недрах сервера БД, таким образом дополнительный SELECT-запрос стал не нужен, и требование единой транзакции утратило актуальность. FireBird версии 1.5 не имел нативной поддержки UUID-ов, поэтому использовались строковые поля длинной в 32 символа (дефисы из UUID-ов удалялись). Факт использования строковых полей в качестве первичных ключей нисколько не смущал, нам не терпелось опробовать новый подход при работе с данными.
У UUID-ов есть свои минусы: 1) Существенный объем; 2) Более низкая скорость работы по сравнению с целочисленными идентификаторами. В рамках проекта достоинства оказались более значимы, чем указанные недостатки. В целом, опыт оказался положительным, поэтому в последующих решениях при создании реляционных связей предпочтение отдавалось именно UUID-ам.
Примечание: Более подробный анализ UUID vs Integer для СУБД MS SQL можно посмотреть в статье «Первичный ключ – GUID или автоинкремент?»
Первый драйвер для FireBird
В 2012 году мне снова довелось поработать с FireBird. Нужно было создать небольшую программу по анализу данных. Разработка велась с использованием QtFramework. Примерно в это же время у FireBird вышла версия 2.5 с нативной поддержкой UUID-ов. Я подумал: «Почему бы не добавить в Qt-драйвер для FireBird поддержку типа QUuid?» Так появилась первая версия Qt-драйвера с поддержкой UUID-ов. Этот вариант не сильно отличался от оригинальной версии драйвера и, в основном, был ориентирован на использование в однопоточных приложениях.
Появление сущности «Транзакция»
Следующая модификация Qt-драйвера для FireBird произошла в конце 2018 года. Наша фирма взялась за разработку проекта по анализу данных большого объема. Для фирмы выросшей из стартап-а эта работа была очень важна, как с финансовой, так и с репутацио́нной точек зрения. Сроки исполнения были весьма жесткие. В качестве СУБД была выбрана FireBird, несмотря на определенные сомнения в ее пригодности. Хорошим вариантом могла бы стать PostgreSQL, но у нашей команды на тот момент отсутствовал опыт эксплуатации данной СУБД.
Новая концепция не нуждалась в сущности «транзакция», как в самостоятельной единице, тем не менее, я не стал ее упразднять. Дальнейшая эксплуатация показала, что наличие объекта «транзакция» делает работу с базой данных более гибкой, дает больше инвариантов при написании кода. Например, разработчик может передать объект «Транзакция» в функцию в качестве параметра, явно говоря таким образом, что внутри нужно работать в контексте указанной транзакции. В функции можно проверить активна транзакция или нет, можно выполнить COMMIT или ROLLBACK. Для вспомогательных операций можно создавать альтернативную транзакцию, не затрагивающую основную. Таких возможностей нет у нативных Qt-драйверов.
Ниже приведен пример с тремя функциями, которые последовательно вызываю друг друга. Каждая функция запрашивает объект подключения к базе данных (Driver) у пула коннектов. Так как функции вызываются в одном потоке, они получают объект коннекта, ссылающийся на одно и тоже подключение к БД. Далее в каждой функции создается собственный независимый объект транзакции и все последующие запросы будут выполняются в его контексте.
Приведенный пример не будет работать с нативным Qt-драйвером, причина описана выше: ограничение на одно подключение и одну транзакцию
В примере экземпляры транзакций (1-3) созданы для наглядности. В рабочем коде их можно опустить. В этом случае транзакции будут создаваться неявно внутри объекта QSqlQuery. Неявные транзакции всегда завершаются ROLLBACK-ом для SELECT-запросов и попыткой COMMIT-а для всех остальных.
Ниже показано как можно использовать одну транзакцию для трех sql-запросов. Подтвердить или откатить транзакцию можно в любой из трех функций.
Драйвер для PostgreSQL
Драйвер для MS SQL
Чего нет в классе Driver
Описываемые здесь драйверы не повторяют один в один функционал Qt-решений. В классе оставлены следующие методы:
С введением сущности «Транзакция» они утратили актуальность и нужны исключительно для отладки и диагностирования их вызовов из Qt-компонентов.
Ряд функций не используются нами в работе, поэтому они либо не реализованы, либо реализованы и помечены внутри программными точками останова, то есть разработчику при первом вызове придется их отладить. Вот эти функции:
Заморожена поддержка механизма событий. Обсудив с коллегами этот функционал, мы пришли к заключению, что на данном этапе в нем нет необходимости. Возможно, в будущем решение будет пересмотрено, но пока у нас нет серьезных доводов в пользу событийного механизма.
Новые функции
Лицензионные ограничения
Зависимости
В реализации драйверов используется система логирования ALog, которая является составной частью библиотеки общего назначения SharedTools.
Демо-примеры
Специально для этой статьи был создан демонстрационный проект. Он содержит примеры работы с тремя СУБД: FireBird, PostgreSQL, MS SQL. Репозиторий с драйверами расположен здесь, он подключен в проект как субмодуль. Библиотека SharedTools так же подключена как субмодуль.
Проект создан с использованием QtCreator, сборочная система QBS. Есть четыре сборочных сценария:
Драйвера в первую очередь разрабатывались для работы в Linux, поэтому эксплуатационное тестирование выполнялось именно для этой ОС. В Windows будет работать FireBird-драйвер (проверено), для остальных драйверов тестирование не проводилось.
Демо-примеры записывают следующие логи:
При первом запуске, примеры проверяют наличие тестовой базы данных. Если базы не обнаружено, в лог-файл будет выведен скрипт для ее создания.
Заключение
Черновой вариант статьи не предполагал наличие этого раздела, за что старый товарищ и, по совместительству, корректор подверг меня критике: «Мол, непонятна мотивация, целеполагание неясно. Зачем ты вообще писал эту статью?!» Что ж, исправляюсь!
Руководство по проектированию реляционных баз данных (4-6 часть из 15) [перевод]
Выкладываю продолжение перевода цикла статей для новичков.
В настоящих и последующих — больше информации по существу.
Начало — здесь.
4. ТАБЛИЦЫ И ПЕРВИЧНЫЕ КЛЮЧИ
Как вы уже знаете из прошлых частей, данные хранятся в таблицах, которые содержат строки или по-другому записи. Ранее я приводил пример таблицы, содержащей информацию об уроках. Давайте снова на нее взглянем.
В таблице имеются 6 уроков. Все 6 – разные, но для каждого урока значения одинаковых полей хранятся в таблице, а именно: tutorial_id (идентификатор урока), title (заголовок)и category (категория). Tutorial_id – первичный ключ таблицы уроков. Первичный ключ – это значение, которое уникально для каждой записи в таблице.
В таблице клиентов ниже customer_id – первичный ключ. В данном случае первичный ключ – также уникальное значение (число) для каждой записи.
Первичные ключи в повседневной жизни
В базе данных первичные ключи используются для идентификации. В жизни первичные ключи вокруг нас везде. Каждый раз, когда вы сталкиваетесь с уникальным числом это число может служить первичным ключом в базе данных (может, но не обязательно должно использоваться как таковое. Все базы данных способны автоматически генерировать уникальное значение для каждой записи в виде числа, которое автоматически увеличивается и вставляется вместе с каждой новой записью [Т.н. синтетический или суррогатный первичный ключ – прим.перев.]).
Что объединяет эти примеры? То, что во всех из них в качестве первичного ключа выбирается уникальное, не повторяющееся значение для каждой записи. Еще раз. Значения поля таблицы базы данных, выбранного в качестве первичного ключа, всегда уникально.
Что характеризует первичный ключ? Характеристики первичного ключа.
Первичный ключ служит для идентификации записей.
Первичный ключ используется для идентификации записей в таблице, для того, чтобы каждая запись стала уникальной. Еще одна аналогия… Когда вы звоните в службу технической поддержки, оператор обычно просит вас назвать какой-либо номер (договора, телефона и пр.), по которому вас можно идентифицировать в системе.
Если вы забыли свой номер, то оператор службы технической поддержки попросит предоставить вас какую-либо другую информацию, которая поможет уникальным образом идентифицировать вас. Например, комбинация вашего дня рождения и фамилия. Они тоже могут являться первичным ключом, точнее их комбинация.
Первичный ключ уникален.
Первичный ключ всегда имеет уникальное значение. Представьте, что его значение не уникально. Тогда его бы нельзя было использовать для того, чтобы идентифицировать данные в таблице. Это значит, что какое-либо значение первичного ключа может встретиться в столбце, который выбран в качестве первичного ключа, только один раз. РСУБД устроены так, что не позволят вам вставить дубликаты в поле первичного ключа, получите ошибку.
Еще один пример. Представьте, что у вас есть таблица с полями first_name и last_name и есть две записи:
| first_name | last_name |
| vasya |pupkin |
| vasya |pupkin |
Т.е. есть два Васи. Вы хотите выбрать из таблицы какого-то конкретного Васю. Как это сделать? Записи ничем друг от друга не отличаются. Вот здесь и помогает первичный ключ. Добавляем столбец id (классический вариант синтетического первичного ключа) и…
Id | first_name | last_name |
1 | vasya |pupkin |
2 | vasya |pupkin |
Теперь каждый Вася уникален.
Типы первичных ключей.
Обычно первичный ключ – числовое значение. Но он также может быть и любым другим типом данных. Не является обычной практикой использование строки в качестве первичного ключа (строка – фрагмент текста), но теоретически и практически это возможно.
Составные первичные ключи.
Часто первичный ключ состоит из одного поля, но он может быть и комбинацией нескольких столбцов, например, двух (трех, четырех…). Но вы помните, что первичный ключ всегда уникален, а значит нужно, чтобы комбинация n-го количества полей, в данном случае 2-х, была уникальна. Подробнее об этом расскажу позднее.
Поле первичного ключа часто, но не всегда, обрабатывается самой базой данных. Вы можете, условно говоря, сказать базе данных, чтобы она сама автоматически присваивала уникальное числовое значение каждой записи при ее создании. База данных, обычно, начинает нумерацию с 1 и увеличивает это число для каждой записи на одну единицу. Такой первичный ключ называется автоинкрементным или автонумерованным. Использование автоинкрементных ключей – хороший способ для задания уникальных первичных ключей. Классическое название такого ключа – суррогатный первичный ключ [Как и упоминалось выше. – прим. перев.]. Такой ключ не содержит полезной информации, относящейся к сущности (объекту), информация о которой хранится в таблице, поэтому он и называется суррогатным.
5. СВЯЗЫВАНИЕ ТАБЛИЦ С ПОМОЩЬЮ ВНЕШНИХ КЛЮЧЕЙ
Когда я начинал разрабатывать базы данных я часто пытался сохранять информацию, которая казалась родственной, в одной таблице. Я мог, например, хранить информацию о заказах в таблице клиентов. Ведь заказы принадлежат клиентам, верно? Нет. Клиенты и заказы представляют собой отдельные сущности в базе данных. И тому и другому нужна своя собственная таблица. А записи в этих двух таблицах могут быть связаны для того, чтобы установить отношения между ними. Проектирование базы данных – это решение двух вопросов:
Один-ко-многим.
Клиенты и заказы имеют связь (состоят в отношениях) один-ко-многим потому, что один клиент может иметь много заказов, но каждый конкретный заказ (их множество) оформлен только одним клиентом, т.е. может иметь только одного клиента. Не беспокойтесь, если на данный момент понимание этой связи смутно. Я еще расскажу о связях в следующих частях.
Одно является важным сейчас – то, что для связи один-ко-многим необходимо две отдельные таблицы. Одна для клиентов, другая для заказов. Давайте немного попрактикуемся, создавая эти две таблицы.
Какую информацию мы будем хранить? Решаем первый вопрос.
Для начала мы определимся какую информацию о заказах и о клиентах мы будем хранить. Чтобы это сделать мы должны задать себе вопрос: “Какие единичные блоки информации относятся к клиентам, а какие единичные блоки информации относятся к заказам?”
Проектируем таблицу клиентов.
Заказы действительно принадлежат клиентам, но заказ – это это не минимальный блок информации, который относится к клиентам (т.е. этот блок можно разбить на более мелкие: дата заказа, адрес доставки заказа и пр., к примеру).
Поля ниже – это минимальные блоки информации, которые относятся к клиентам:
Давайте перейдем к непосредственному созданию этой таблицы в SQLyog (естественно, что вы можете использовать любую другую программу). Ниже приведен пример того, как могла бы выглядеть таблица в программе SQLyog после создания. Все графические приложения для управления базами данных имеют приблизительно одинаковую структуру интерфейса. Вы также можете создать таблицу с помощью командной строки без использования графической утилиты.
Создание таблицы в SQLyog. Обратите внимание, что выбран флажок первичного ключа (PK) для поля customer_id. Поле customer_id является первичным ключом. Также выбран флажок Auto Incr, что означает, что база данных будет автоматически подставлять уникальное числовое значение, которое, начиная с нуля, будет каждый раз увеличиваться на одну единицу.
Проектируем таблицу заказов.
Какие минимальные блоки информации, необходимые нам, относятся к заказу?
Ниже – пример таблицы в SQLyog.
Проект таблицы. Поле customer является ссылкой (внешним ключом) для поля customer_id в таблице клиентов.
Эти две таблицы (клиентов и заказов) связаны потому, что поле customer в таблице заказов ссылается на первичный ключ (customer_id) таблицы клиентов. Такая связь называется связью по внешнему ключу. Вы должны представлять себе внешний ключ как простую копию (копию значения) первичного ключа другой таблицы. В нашем случае значение поля customer_id из таблицы клиентов копируется в таблицу заказов при вставке каждой записи. Таким образом, у нас каждый заказ привязан к клиенту. И заказов у каждого клиента может быть много, как и говорилось выше.
Создание связи по внешнему ключу.
Вы можете задаться вопросом: “Каким образом я могу убедиться или как я могу увидеть, что поле customer в таблице заказов ссылается на поле customer_id в таблице клиентов”. Ответ прост – вы не можете сделать этого потому, что я еще не показал вам как создать связь.
Ниже – окно SQLyog с окном, которое я использовал для создания связи между таблицами.
Создание связи по внешнему ключу между таблицами заказов и клиентов.
В окне выше вы можете видеть, как поле customer таблицы заказов слева связывается с первичным ключом (customer_id) таблицы клиентов справа.
Теперь, когда вы посмотрите на данные, которые могли бы быть в таблицах, вы увидите, что две таблицы связаны.
Заказы связаны с клиентами через поле customer, которое ссылается на таблицу клиентов.
На изображении вы видите, что клиент mary поместила три заказа, клиент pablo поместил один, а клиент john – ни одного.
Вы можете спросить: “А что же именно заказали все эти люди?” Это хороший вопрос. Вы возможно ожидали увидеть заказанные товары в таблице заказов. Но это плохой пример проектирования. Как бы вы поместили множественные продукты в единственную запись? Товары – это отдельные сущности, которые должны храниться в отдельной таблице. И связь между таблицами заказов и товаров будет являться связью один-ко-многим. Я расскажу об этом далее.
6. СОЗДАНИЕ ДИАГРАММЫ СУЩНОСТЬ-СВЯЗЬ
Ранее вы узнали как записи из разных таблиц связываются друг с другом в реляционных базах данных. Перед созданием и связыванием таблиц важно, чтобы вы подумали о сущностях, которые существуют в вашей системе (для которой вы создаете базу данных) и решили каким образом эти сущности бы связывались друг с другом. В проектировании баз данных сущности и их отношения обычно предоставляются в диаграмме сущность-связь (англ. entity-relationship diagram, ERD). Данная диаграмма является результатом процесса проектирования базы данных.
Сущности.
Вы можете задаться вопросом, что же такое сущность. Нуу… это “вещь” в системе. Там. Моя Мама всегда хотела, чтобы я стал учителем потому, что я очень хорошо объясняю различные вещи.
В контексте проектирования баз данных сущность – это нечто, что заслуживает своей собственной таблицы в модели вашей базы данных. Когда вы проектируете базу данных, вы должны определить эти сущности в системе, для которой вы создаете базу данных. Это скорее вопрос диалога с клиентом или с собой с целью выяснения того, с какими данными будет работать ваша система.
Давайте возьмем интернет-магазин для примера. Интернет-магазин продает товары. Товар мог бы стать очевидной сущностью в системе интернет-магазина. Товары заказываются клиентами. Вот мы с вами и увидели еще две очевидных сущности: заказы и клиенты.
Заказ оплачивается клиентом… это интересно. Мы собираемся создавать отдельную таблицу для платежей в базе данных нашего интернет-магазина? Возможно. Но разве платежи – это минимальный блок информации, который относится к заказам? Это тоже возможно.
Если вы не уверены, то просто подумайте о том, какую информацию о платежах вы хотите хранить. Возможно, вы захотите хранить метод платежа или дату платежа. Но это все еще минимальные блоки информации, которые могли бы относиться к заказу. Можно изменить формулировки. Метод платежа — метод платежа заказа. Дата платежа – дата платежа заказа. Таким образом, я не вижу необходимости выносить платежи в отдельную таблицу, хотя концептуально вы бы могли выделить платежи как сущность, т.к. вы могли бы рассматривать платежи как контейнер информации (метод платежа, дата платежа).
Давайте не будет слишком академичными.
Как вы видите, есть разница между сущностью и непосредственно таблицей в базе данных, т.е. это не одно и то же. Специалисты отрасли информационных технологий могут быть ОЧЕНЬ академичными и педантичными в этом вопросе. Я не такой специалист. Эта разница зависит от вашей точки зрения на ваши данные, вашу информацию. Если вы смотрите на моделирование данных с точки зрения программного обеспечения, то вы можете прийти к множеству сущностей, которые нельзя будет перенести напрямую в базу данных. В данном руководстве мы смотрим на данные строго с точки зрения баз данных и в нашем маленьком мире сущность – это таблица.
Держитесь там, вы действительно близки к получению вашей ученой степени по базам данных.
Как вы видите определение того, какие сущности имеет ваша система – это немного интеллектуальный процесс, который требует некоторого опыта и часто – это предмет для внесения изменений, пересмотров, раздумий, но, конечно, это не ракетостроение.
Диаграмма сущность-связь может быть достаточно большой, если вы работаете над сложным приложением. Некоторые диаграммы могут содержать сотни или даже тысячи таблиц.
Связи.
Второй шаг в проектировании баз данных – это выбор того, какие связи существуют между сущностями в вашей системе. Сейчас это может быть немного сложно для понимания, но, повторюсь еще раз, это не ракетостроение. С приобретением некоторого опыта и переосмысления выполненной работы вы будете завершать очередную модель базы данных верным или почти верным образом.
Итак. Я рассказал вам о связи один-ко-многим и я расскажу вам больше о связях в дальнейших частях этого руководства, поэтому сейчас я больше не буду останавливаться на этом. Просто запомните, что решение о том, какие связи будут иметь ваши сущности – важная часть проектирования баз данных и эти связи отображаются в диаграмме сущность-связь.