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

Материал из OpenWiki
Перейти к: навигация, поиск
(Послулат 2)
Строка 38: Строка 38:
  
 
''В таблицах все поля, хранящие идентификаторы, кроме первичных ключей, будут иметь ограничение целостности данных FOREIGN KEY("Ид"."ид").''
 
''В таблицах все поля, хранящие идентификаторы, кроме первичных ключей, будут иметь ограничение целостности данных 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";

Версия 15:41, 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";