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

Материал из OpenWiki
Перейти к: навигация, поиск
м (Cтруктура таблицы значений)
м (Структура таблицы идентификаторов (Ид))
Строка 26: Строка 26:
 
Для обеспечения множественной целостности одной колонки, ссылающейся на несколько других колонок вводится специальная таблица, в которую помещаются все идентификаторы данной схемы данных. В этой таблице также полезно ввести дополнительные поля.
 
Для обеспечения множественной целостности одной колонки, ссылающейся на несколько других колонок вводится специальная таблица, в которую помещаются все идентификаторы данной схемы данных. В этой таблице также полезно ввести дополнительные поля.
  
====Структура таблицы идентификаторов (Ид)====
+
====Структура таблицы идентификаторов ("Ид")====
* "'''ид'''", integer, not null, primary key, значение берется из генеральной последовательности
+
* "'''ид'''" integer not null primary key, -- значение берется из генеральной последовательности
* "'''пользователь'''", integer, not null, foreign key("Ид"."ид"), идентификатор пользователя (он тоже объект)
+
* "'''пользователь'''" integer not null foreign key("Ид"."ид"), -- идентификатор пользователя (он тоже объект)
* "'''дата'''", штамп времени, not null, по умолчанию now(), дата создания записи (т.е. объекта/свойства)
+
* "'''дата'''" <штамп времени> not null default now() -- дата создания записи (т.е. объекта/свойства)
  
 
Для колонки "пользователь" введение foreign key("Ид"."ид") возможно будет только после создания в этой таблице записи values(0,0,now()). Эта так называемая пустая запись будет означать момент "сотворения" системы. На нее придется ссылаться до возникновения первого пользователя, а может даже и после.
 
Для колонки "пользователь" введение foreign key("Ид"."ид") возможно будет только после создания в этой таблице записи values(0,0,now()). Эта так называемая пустая запись будет означать момент "сотворения" системы. На нее придется ссылаться до возникновения первого пользователя, а может даже и после.

Версия 15:57, 20 сентября 2010


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

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

Некоторые серверы СУБД предлагают массивовые типы данных (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.