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

Материал из OpenWiki
Перейти к: навигация, поиск
(Послулат 2)
(Шаг 3)
Строка 53: Строка 53:
 
Идентификатор в ячейке свойства таблицы класса теперь ссылается не на таблицу "Значения...", а на записи таблицы "Связи" по колонке "ид1", а колонка "ид2" как раз на записи таблицы "Значения...". Одна запись в таблице значений может использоваться в разных объектах/свойствах. Прежде чем поместить запись с некоторым значением в таблицу значений нужно проверить наличие такого значения и если оно уже когда-то было сохранено извлечь эту запись и получить ид этой записи. Любое свойство класса может хранить значения разных типов, в этом случае используются разные таблицы значений.
 
Идентификатор в ячейке свойства таблицы класса теперь ссылается не на таблицу "Значения...", а на записи таблицы "Связи" по колонке "ид1", а колонка "ид2" как раз на записи таблицы "Значения...". Одна запись в таблице значений может использоваться в разных объектах/свойствах. Прежде чем поместить запись с некоторым значением в таблицу значений нужно проверить наличие такого значения и если оно уже когда-то было сохранено извлечь эту запись и получить ид этой записи. Любое свойство класса может хранить значения разных типов, в этом случае используются разные таблицы значений.
  
Доработка таблиц "Значения...":
+
====Доработка таблиц "Значения..."====
  
 
  ALTER TABLE "Значения..." ADD CONSTARINT "..." UNIQUE("значение");
 
  ALTER TABLE "Значения..." ADD CONSTARINT "..." UNIQUE("значение");

Версия 15:42, 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, по умолчанию 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";