Самое краткое руководство по проектированию Баз Данных

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

1. создаю свою систему, удовлетворяющую требованиям
2. импортирую данные из исходной базы

Эта заметка о пункте номер два.

Я впервые столкнулся с полностью ненормализованной базой. Т.е. в ней были нарушены практически все принципы построения реляционных БД. Но тем не менее эта база использовалась продолжительное время. Не стану вдаваться в подробности, отмечу лишь что вызвало первый шок — таблицы с именами «январь», «февраль» и т.д. для графика работы. Поверьте, дальше все было гораздо хуже. Я понимаю, что не мне судить человека, который это создал — система, использовалась не один год и в какой-то мере удовлетворяла потребности заказчика. Просто я не хочу больше сталкиваться с такими «базами». Надеюсь данная заметка поможет в этом.

Самое краткое руководство по проектированию Баз Данных.

В качестве примера будем проектировать базу по учету товаров. С древовидным каталогом и данными о производителях.

1. Объекты

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

Необходимо избегать дублирования данных. Например недопустимо хранить в каждой записи таблицы «товар» полную информацию о производителе. Т.к. при изменении каких-то данных производителя, придется искать все упоминания о нем в таблице «товары». Назовем нашим таблицы item, node и company.

2. Первичный ключ

Что бы «обращаться» к конкретному объекту необходимо дать ему уникальный номер. Вообще говоря это может быть любое уникальное поле или группа полей (например, в случае учета сотрудников — номер паспорта или фамилия, имя, отчество), однако по многим причинам гораздо удобней сделать отдельное поле с уникальным значением. Это поле и есть первичный ключ. Обычно это поле называют «id» (идентификатор).

3. Связи, внешние ключи

Все объекты каким-то образом связаны друг с другом — производители производят товары, товары размещаются в каталоге и т.п. Отношения бывают трех видов:

один-ко-многим

один производитель может создавать много разных товаров. Реализуется просто — в таблице объектов, которых «много» создается поле с id объекта, который «один». В случае товаров и производителей нужно в таблицу item добавить поле company_id, которое будет содержать id производителя данного товара. Такое поле называют внешним ключем.

многие-ко-многим

любой товар может присутствовать сразу в нескольких разделах каталога. Такая связь хранится в отдельной таблице с полями id товара и id раздела. Таким образом каждая запись таблицы означает присутствие товара в разделе каталога.

один-к-одному

допустим наш товар это книги и диски. Их общая информация и тип товара хранятся в таблице item, а данные специфичные для книг и для дисков будем хранить в таблицах book и disk соответственно. Т.е. для каждой записи в таблице book есть ровно одна запись в item. По сути это один объект хранится в двух таблицах.

Реализуется так — первичный ключ таблицы book содержит id из таблицы item. Т.е. первичный ключ одновременно является внешним ключем.

дерево

по сути это тоже что и один-ко-многим. Один раздел каталога содержит много других. Реализация такая же — запись таблицы node содержит id родительского раздела (parent_id)

4. обеспечение целостности

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

То же самое на SQL

1. создаем таблицы

-- раздел каталога
create table node (
id numeric not null, -- первичный ключ
parent_id numeric not null, -- внешний ключ. ссылается на родительский раздел
name varchar(200)
);

-- компания-производитель
create table company (
id numeric not null, -- первичный ключ
name varchar(1000),
);

-- товар
create table item (
id numeric not null, -- первичный ключ
company_id numeric not null, -- внешний ключ. ссылается на компанию-производителя
type varchar(10) NOT NULL, -- 'book' или 'disc'
name varchar(1000), -- наименование
qty numeric, -- кол-во товара
price numeric -- цена за единицу
);


2-3-4. Создаем недостающие связи и указываем какие поля являются первичными и внешними ключами.

-- товар - книга
create table book (
id numeric not null, -- одновременно первичный и внешний ключ, ссылающийся на item
author varchar(1000)
);

-- товар - диск
create table disk (
id numeric not null, -- одновременно первичный и внешний ключ, ссылающийся на item
play_time numeric
);

create table node_item (
node_id numeric not null,
item_id numeric not null
);

-- для каждой таблицы указываем ее первичный ключ
alter table node add constraint "PK_NODE" primary key (id);
alter table item add constraint "PK_ITEM" primary key (id);
alter table company add constraint "PK_COMPANY" primary key (id);
alter table book add constraint "PK_BOOK" primary key (id);
alter table disk add constraint "PK_DISK" primary key (id);
-- у таблицы, реализующей отношение многие-ко-многим, первичный ключ составной.
alter table node_item add constraint "PK_NODE_ITEM" primary key (node_id, item_id);

-- указываем внешние ключи и на что они ссылаются
alter table node add constraint "FK_NODE_PARENT" foreign key (parent_id) references node(id);
alter table item add constraint "FK_ITEM_COMPANY" foreign key (company_id) references company(id);

alter table node_item add constraint "FK_NODEITEM_NODE" foreign key (node_id) references node(id);
alter table node_item add constraint "FK_NODEITEM_ITEM" foreign key (item_id) references item(id);

alter table book add constraint "FK_BOOK_ITEM" foreign key (id) references item(id);
alter table disk add constraint "FK_DISK_ITEM" foreign key (id) references item(id);