Схема базы данных 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.
Источник — «https://wiki.opennet.ru/?title=Схема_базы_данных_SQL_с_минимальным_количеством_таблиц&oldid=1982»