Проектирование таблиц для базы данных

Процесс проектирование таблиц базы данныхПока в нашей базе данных всего две таблицы, и до сих пор мы еще не говорили о том, как принимается решение о том, какие данные попа­дут в каждую из таблиц; мы делали то, что представлялось нам разум­ным. На самом деле проектирование схемы базы данных (корректно называть конструкцию, включающую в себя таблицы, столбцы и отно­шения, схемой) может быть очень сложным занятием.

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

 

Несколько эвристических правил

К счастью, для достаточно простых баз данных (приблизительно до де­сяти таблиц) проектирование не так уж сложно, и вы, приобретя неко­торые навыки, сможете создавать разумные конструкции.

В этом разделе рассмотрим простую базу данных, которую мы уже на­чали создавать, и попытаемся понять, какими соображениями следует руководствоваться, определяя, какие таблицы необходимо создать.

При проектировании базы данных обычно выполняется «нормализа­ция», то есть применяется некоторый набор правил, гарантирующих, что данные будут распределены соответствующим образом. Для начала нам потребуется несколько основных правил. Надеемся (и настаиваем на том, что этого не следует делать), что вам не придет в голову, прочитав эти правила, сразу же взяться за создание базы, со­стоящей из двадцати таблиц. Эти правила приведены только для того, чтобы облегчить читателям понимание базы данных, которая используется в моем блоге для изу­чения SQL и PostgreSQL.

 

Правило первое - разбивайте данные на столбцы

Первое правило состоит в том, что в каждый столбец должен быть по­мещен один фрагмент информации (атрибут). Это кажется вполне ло­гичным. Уже в электронной таблице информация по каждому клиен­ту была естественным образом разбита на столбцы так, что, например, фамилия была отделена от почтового индекса.

В электронных таблицах соблюдение этого правила делает более удоб­ной работу с данными, например сортировку по индексу. Что же каса­ется баз данных, важнейшим условием является правильное разбие­ние данных на атрибуты. Почему это так важно? С практической точ­ки зрения весьма сложно будет указать, что нас интересуют данные, находящиеся с 29-й по 35-ю позицию в столбце адреса, т. к. именно там должен находиться почтовый индекс. Если данных достаточно много, наступит момент, когда индекс появится на другой позиции, и мы получим неверный фрагмент информации. Еще одна причина не­обходимости корректного разбиения на столбцы состоит в том, что все столбцы базы данных должны содержать данные одного типа.

 

Правило второе - однозначно идентифицируйте каждую строку

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

В любом случае необходимо задать что-то так, чтобы, используя это что-то, можно было с полной уверенностью сказать: «Глядя на X в этой строке, можно не сомневаться, что его значение будет отличаться от значений всех остальных строк данной таблицы». Если не удается выбрать столбец или комбинацию столбцов, которая однозначно опре­деляла бы строку, необходимо ввести дополнительный столбец, смысл которого будет только в обеспечении уникальности строки. В таблицу customer был введен дополнительный столбец customer_id, и теперь стро­ки идентифицируются однозначно.

 

Правило третье - удаляйте повторяющуюся информацию

Когда мы пытались сохранить данные о заказах в таблице customer, она становилась некрасивой из-за проблемы повторяющихся групп. Для каждого клиента приходилось повторять информацию о заказе столько раз, сколько требовалось. То есть предугадать, сколько столб­цов нужно отвести для заказов, было невозможно. В базе данных ко­личество столбцов фиксируется при проектировании. Так что необхо­димо заранее решить, сколько потребуется столбцов, какого они будут типа, и дать каждому столбцу название, только тогда можно будет со­хранять какие-то данные. Ни в коем случае не храните повторяющие­ся группы данных в одной строке.

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

Если говорить более формально, была создана зависимость «многие-к- одному», другими словами, несколько заказов могло быть получено от одного клиента.

 

Правило четвертое - давайте непротиворечивые названия

Вероятно, это правило сложнее всего выполнить. Как назвать таблицу или столбец? Если название не придумывается, это часто бывает сиг­налом того, что в конструкции таблицы или столбца что-то неладно.

Многие разработчики баз данных имеют свой собственный набор пра­вил, которыми они руководствуются, именуя таблицы и столбцы для того, чтобы в названиях не было противоречивости. Например, нельзя, чтобы имена каких-то таблиц были существительными в единствен­ном числе, а других - во множественном (не «офис» и «отделы», а «офис» и «отдел»). Если выбрано имя для столбца с идентификато­рами, может быть, tablename_id, то следует придерживаться этого пра­вила и в названиях других подобных столбцов. Если вы используете сокращения, делайте это постоянно. Если столбец одной таблицы яв­ляется ключом для другой, постарайтесь дать им имена, образованные от одной основы.

Цель всех этих действий чрезвычайно проста - таблицы и столбцы должны иметь короткие значащие имена, а процесс присваивания имен в пределах базы данных должен быть последовательным. Дости­жение такой, казалось бы, простой цели, часто бывает весьма непрос­тым занятием, но и вознаграждение мы получаем не маленькое - веде­ние базы данных значительно облегчается.

 

Схема базы данных «Клиенты и заказы»

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

Схема Базы данных «Клиенты и заказы»

Рис. 1. База данных «Клиенты и заказы»
 

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

Очень важно помнить о том, кто есть кто в отношении «один-ко-мно- гим», иначе может возникнуть масса проблем. Заметьте также, что столбец, который будет использоваться для объединения двух таблиц, получил одно и то же имя в обеих таблицах - customer_id. Это не жиз­ненно важно, при желании можно было назвать эти два столбца foo и bar, но постоянство в именовании в дальнейшем очень нам поможет.

В сложных базах данных отсутствие единообразия в названиях (на­пример, customer_id, customer_ident, cust_id или cust_no) может быть чрезвычайно неудобным. Поэтому всегда разумнее потратить время на присваивание хороших и осмысленных имен, это надолго облегчит вам жизнь.

Как моделируются таблицы и связи в базе данных 

Добавляем таблицы в базу данных

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

Проблема состоит в том, что заранее не известно, сколько пунктов со­держит каждый заказ (аналогично тому, как заранее не известно, сколько заказов может разместить каждый из клиентов). Заказ мо­жет включать в себя один, два или даже сто товаров. Необходимо отде­лить информацию о том, что клиент сделал заказ, от деталей самого заказа. Следует попытаться создать нечто, подобное изображенному на рис. 2:

Расширение базы данных «Клиенты и заказы»

Рис. 2. Расширяем базу данных «Клиенты и заказы»

Как и при создании таблиц customer и orderinfo, разделим информацию на две таблицы, а затем объединим их. Правда при этом возникает одна небольшая проблема.

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

Рис. 3. Отношение между таблицами заказов и товаров

Представлено отношение «многие-ко-многим» - каждый заказ может ссылаться на множество товаров, и каждый товар может появляться во множестве заказов. Это подрывает некоторые из основ реляцион­ных баз данных. Пока не будем вдаваться в детали, заметим только, что в реляционной базе данных никакие две таблицы не должны нахо­дится в отношениях «многие-ко-многим».

Можно попытаться обойти эту проблему, создав отдельную запись в каждой строке таблицы item для каждого заказа, но тогда придется многократно повторить описание и информацию о цене для каждого товара, что нарушило бы правило третье (удалять повторяющуюся ин­формацию!).

Что касается на­шей проблемы, то для нее существует стандартное решение. Необхо­димо создать третью таблицу между двумя существующими, она будет реализовывать отношение «многие-ко-многим». Надо сказать, что объ­яснять сложнее, чем делать, поэтому просто создадим эту третью таб­лицу, orderline, которая свяжет заказы с товарами.

Создана таблица, строки которой соответствуют каждой строчке зака­за (рис. 4). Для каждой отдельной строки можно определить, из ка­кого она заказа, по столбцу orderinfo_id, а товар, на который она ссы­лается, - по столбцу item_id.

Один товар может присутствовать во многих строках таблицы orderline, а один заказ также может встречаться в нескольких ее строках. Од­нако каждая строка промежуточной таблицы ссылается только на один товар и может присутствовать только в одном заказе.

Рис. 4. Добавление в базу данных таблицы orderline

Обратите внимание, что в новую таблицу не введен уникальный иден­тификатор для каждой строки. Дело в том, что комбинация orderin- fo_id и item_id всегда уникальна. Но и здесь есть одна почти незамет­ная проблема. Что произойдет, если клиент закажет два экземпляра товара в одном заказе?

Просто ввести еще одну строку в orderline нельзя, поскольку комбина­ция orderinfo_id и item_id должна быть уникальной. Неужели придет­ся добавить еще одну специальную таблицу для обслуживания зака­зов, содержащих несколько экземпляров одного товара? К счастью, нет. Есть гораздо более простой способ. Надо просто ввести поле «ко­личество» (quantity) в таблицу orderline, и все будет хорошо.

 

Завершение первичного проекта

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

Может случиться так, что каждый продукт будет иметь несколько штрих-кодов, потому что если производитель вносит значительные из­менения в упаковку продукта, он также часто меняет и штрих-код. Например, все наверняка видели упаковки с надписью «+20% бес­платно», стоимость такой (например) бутылки лимонада не меняется, но зато благодаря этой рекламной акции лимонада вы получаете боль­ше. Обычно в таких случаях производители изменяют штрих-код, но сам продукт, по существу, не меняется. Возникает отношение: мно­жество штрих-кодов для одного продукта. Добавляем таблицу для хранения штрих-кодов (рис. 5):

Рис. 5. Добавление в базу данных таблицы BARCODE

Заметьте, что стрелка идет в направлении от таблицы BARCODE к табли­це ITEM, потому что именно штрих-кодов может быть несколько для од­ного товара. Обратите внимание на то, что barcode_ean является пер­вичным ключом, т. к. для каждого штрих-кода должна существовать уникальная строка и (хотя один продукт и может иметь несколько штрих-кодов) ни один штрих-код не может принадлежать более чем одному продукту.

Наконец, последнее добавление, которое следует внести в проект базы данных, - объем запасов каждого продукта.

Есть два пути реализации такого добавления. Если большинство това­ров находится на складе, то информация о таких запасах весьма важ­на, и тогда можно хранить количество продуктов, имеющихся на скла­де, непосредственно в таблице item.

Но может быть и так, что у нас множество товаров, при этом чаще все­го лишь какие-то из них присутствуют на складе, а объем инфор­мации, которую приходится хранить для товаров, находящихся на складе, весьма велик. Например, для склада необходимо хранить ин­формацию о размещении, номерах партий и сроках годности. Если в картотеке имеется 500 000 товаров, а на складе есть только 1000, то хранение данных по всем товарам будет просто расточительством. У этой проблемы есть стандартное решение - дополнительная таблица.

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

Рис. 2.19. Добавление в базу данных таблицы stock

Обратите внимание , что в таблице stock в качестве уникального ключа выступает item_id, а хранящаяся в ней информация связана непосред­ственно с товарами, при этом для выполнения соединения с соответ-

ствующим товаром в таблице item используется item_id. Стрелка ука­зывает на таблицу item, потому что это главная таблица, хотя в данном случае и нет отношения «многие-к-одному».

В таком виде схема кажется явно запутанной, ведь хранимой нами до­полнительной информации очень мало. Но оставим все как есть для того, чтобы показать, как это делается, а далее в книге расскажем, как получить доступ к данным в случае, если много информации содер­жится в дополнительных таблицах (таких, как данная). Для тех, кто любит заглядывать вперед, скажем, что будет использоваться «внеш­нее объединение».

Отметьте также, что названия некоторых столбцов на рисунке под­черкнуты, это означает, что данный столбец или комбинация столбцов (например, в таблице orderinfo) гарантированно уникальны. Они обра­зуют первичный ключ таблицы.

 

Вас заинтересует / Intresting for you:

Реляционная модель и проектиро...
Реляционная модель и проектиро... 2438 просмотров Ирина Светлова Tue, 21 Nov 2017, 13:27:29
Что такое PostgreSQL? Плюсы и ...
Что такое PostgreSQL? Плюсы и ... 12865 просмотров Ирина Светлова Tue, 06 Feb 2018, 07:41:15
Redis или Memcached, что лучше...
Redis или Memcached, что лучше... 9763 просмотров Андрей Васенин Tue, 21 Nov 2017, 13:17:28
Джеймс Форгн (James Forgy) - с...
Джеймс Форгн (James Forgy) - с... 4579 просмотров Antoni Sun, 05 Aug 2018, 16:21:01
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9002 04 апр 2018 08:04
Казалось бы простые, неприложные правила проектирования баз данных, но порой их так сложно соблюсти. Есть соблазн где-то продублировать информацию, где-то объединить данные для ускорения доступа к ним. Но при этом усложняется структура БД, а значмт и ее обслуживание. Операции вставки и обновления требуют больше ресурсов. и т. д....