Схема базы данных SQL с минимальным количеством таблиц

Материал из OpenWiki
Перейти к: навигация, поиск


Основная предпосылка

Обычно в схеме базы данных (БД) предусматривается отдельные таблицы для каждого класса объектов/сущностей/явлений. Название таблицы адекватно названию класса. Кроме того часто вводятся дополнительные вспомогательные и подчиненные таблицы. Количество таблиц существенно увеличивается. Таблицы представляет совокупность свойств/атрибутов сущностей. Для сложных объектов число колонок/полей становится очень большим. Подчиненные таблицы часто вводятся в случаях, когда значением определённого свойства может быть не одно значение из множества допустимых, а сразу несколько (множественность значений).

Некоторые СУБД предлагают типы данных "массив" (ARRAY), для которых в одном поле можно сохранять массив значений.

В описываемой схеме организации БД принимается необходимость хранения каждого значения каждого свойства каждого объекта в виде отдельной записи. К чему приводит данная предпосылка рассказано в тексте данной статьи.

Постулат 1

Все таблицы в этой схеме обязательно имеют одну колонку/поле - целочисленный идентификатор (сокращенно ИД), являющийся первичным ключом (PRIMARY KEY). Все идентификаторы генерируются из одной последовательности, т.е. сколько бы ни было таблиц в схеме нет ни одной записи с одинаковыми идентификаторами.

Понимание этого постулата возможно после дальнейшего изучения данной статьи. Генерация единой последовательности возможна как средствами самого сервера СУБД (например, sequence у PostgreSQL), так и привлечением специальных средств языков программирования или даже создание/использование специального сервера/сервиса.

Шаг 1

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

Cтруктура таблицы значений

  • "ид" integer not null primary key, -- значение берется из генеральной последовательности
  • "ид1" integer not null, -- ссылка на таблицы/колонки объектов
  • "значение" <нужный тип данных> -- непосредственное значение

Выясняется, что нет возможности ввести ограничение целостности (FOREIGN KEY) для колонки "ид1", поскольку оно должно ссылаться на множество таблиц/полей.

Шаг 2

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

Структура таблицы идентификаторов ("Ид")

  • "ид" integer not null primary key, -- значение берется из генеральной последовательности
  • "пользователь" integer not null foreign key("Ид"."ид"), -- идентификатор пользователя (он тоже объект)
  • "дата" <штамп времени> not null default now() -- дата создания записи (т.е. объекта/свойства)

Для колонки "пользователь" введение foreign key("Ид"."ид") возможно будет только после создания в этой таблице записи values(0,0,now()). Эта так называемая пустая запись будет означать момент "сотворения" системы. На нее придется ссылаться до возникновения первого пользователя, а может даже и после.

Таким образом, целостность схемы данных завязывается на таблицу "Ид". Можно условно считать, что генерация глобальных идентификаторов исходит из этой таблицы. Это полезно для СУБД, в которой нет понятия SEQUENCE, но есть AUTOINCREMENT. Выводится второй постулат схемы.

Послулат 2

В таблицах все поля, хранящие идентификаторы, кроме первичных ключей, будут иметь ограничение целостности данных FOREIGN KEY("Ид"."ид").

Шаг 3

Значения в таблицах "Значения..." повторяются. Необходимо ввести уникальность колонки "значение".

Вводим таблицу "Связи":

  • ид, integer not null primary key, идентификатор записи связи из генеральной последовательности
  • ид1, integer not null foreign key ("Ид","ид"), первичный/родительский идентификатор
  • ид2, integer not null foreign key ("Ид","ид"), вторичный/потомковый идентификатор

Индексы таблицы "Связи":

  • UNIQUE("ид1", "ид2")
  • INDEX("ид2")

Идентификатор в ячейке свойства таблицы класса теперь ссылается не на таблицу "Значения...", а на записи таблицы "Связи" по колонке "ид1", а колонка "ид2" как раз на записи таблицы "Значения...". Одна запись в таблице значений может использоваться в разных объектах/свойствах. Прежде чем поместить запись с некоторым значением в таблицу значений нужно проверить наличие такого значения и если оно уже когда-то было сохранено извлечь эту запись и получить ид этой записи. Любое свойство класса может хранить значения разных типов, в этом случае используются разные таблицы значений.

Доработка таблиц "Значения..."

ALTER TABLE "Значения..." ADD CONSTARINT "..." UNIQUE("значение");

Колонка "ид1" теперь не нужна: 

ALTER TABLE "Значения..." DROP COLUMN "ид1";

Шаг 4

Отказ от бесконечного множества таблиц классов. После этого шага останутся только таблицы "Ид", "Связи" и 1-3 таблицы "Значения...".

Как сохранять объекты классов?

Допустим строковые типы значений будем сохранять в таблице "Значения/текст" ("значение" text), дата/время - "Значения/дата" ("значение" datetime) и т.д.

Определение понятия "Классы"

INSERT INTO "Ид" ("пользователь") values (0);-- пока пользователей нет. Получен идентификатор ид1.

INSERT INTO "Значения/текст" ("ид", "значение") values (ид1, 'Классы');-- сохранили пока просто строку

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид2.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид2, 0, ид1);

Сохранена связь идентификаторов, означает рождение понятия "Классы" с идентификатором ид2.

Понятие класс "Пользователи"

Это общая схема для понятий классов 

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид3.

INSERT INTO "Значения/текст" ("ид", "значение") values (ид1, 'Пользователи');-- сохранили строку

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид4.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид4, ид2, ид3);

Сохранена связь идентификаторов, означает рождение понятия класс "Пользователи" с идентификатором ид4. Родителем является понятие "Классы" ("ид2"=ид2)

Сохранение объекта "Пользователь"

Это общая схема для любого объекта любого класса: 

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид5.

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид6.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид6, ид4, ид5);

Сохранена связь идентификаторов, означает сохранение "пустого" объекта класса "Пользователи" с идентификаторм ид5, родитель - класс "Пользователи"("ид1"=ид4), ид6 - идентификатор связи объекта с классом. Один объект может принадлежать нескольким классам, тогда для этого вставляется соответствующее количество связей.

Сохранение свойств полученного "пустого" объекта.

Предварительно определяем понятие "Свойства классов" аналогично понятию "Классы": 

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид7.

INSERT INTO "Значения/текст" ("ид", "значение") values (ид7, 'Свойства классов');-- сохранили строку

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид8.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид8, 0, ид7);

Сохранена связь идентификаторов, означает рождение понятия "Свойства классов" с идентификатором ид8.

Определим свойство/поле "ФИО": 

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид9.

INSERT INTO "Значения/текст" ("ид", "значение") values (ид9, 'ФИО');-- сохранили строку

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид10.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид10, ид8, ид9);

Сохранена связь идентификаторов, означает что появилось свойство "ФИО" с идентификатором ид10.

Сохраним свойство ФИО для пользователя с идентификатором ид5 (см. выше) 

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид11.

INSERT INTO "Значения/текст" ("ид", "значение") values (ид11, 'Василий Пупкин');-- сохранили строку

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид12.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид12, ид5, ид10);

Сохранена связь идентификаторов, означает что к объекту ид5 привязали свойство ид10. 

INSERT INTO "Ид" ("пользователь") values (0);-- Получен идентификатор ид13.

INSERT INTO "Связи" ("ид", "ид1", "ид2") values (ид13, ид12, ид11);

Сохранена связь идентификаторов, означает что значение-строка с идентификатором ид11 определяет свойство ФИО объекта-пользователя ид5. Можно добавлять второе значение свойства ФИО при этом "ид1"=ид12, "ид2"=ид строки другого ФИО.

Далее

Можно пойти дальше 4 шага в сторону отказа от таблицы "Ид". В таблицу "Связи" переносим колонки "пользователь" и "дата".

Заключение

В данной статье представлен некий путь от обычной схемы SQL-таблиц к системе с четко определенным и минимальным количеством таблиц для любого количества классов. Можно останавливаться на шаге 3, но самый интерес пройти дальше. Несколько реально заполняемых баз данных уже тестируются по описанной схеме. Первое время немного усложняется написание запросов на выборку данных, особенно с большим количеством колонок - горизонтальные отчеты. Приходится перестраиваться в "вертикальную" выборку, которая соответствует вертикальности данной схемы.

Добавлено не автором 23.10.2010г. Обсуждение: http://www.sql.ru/forum/actualthread.aspx?tid=791373&pg=-1

Источник — «https://wiki.opennet.ru/?title=Схема_базы_данных_SQL_с_минимальным_количеством_таблиц&oldid=2219»