Структура таблицы в БД (денормализация)

#структуры_данных #база_данных

В рамках базы данных (в данном случае - PostgreSQL, но это не суть) есть несколько
небольших таблиц, которые содержат практически неизменяемые, но очень часто используемые
данные:

группы страниц (pagegroups), 
страницы (pages), 
шаблоны (templates),
позиции на страницах (positions),
модули (modules), 
группы пользователей (usergroups).

В каждой из таблиц есть поля ID, название, алиас и порядок показа. Между данными
есть связи: 

группы пользователей имеют разные права доступа, 
модули могут встречаются на разных позициях разных страниц,
каждой странице может быть задан свой шаблон,
шаблон состоит из позиций (header, sidebar_left, canvas, sidebar_right, footer),
которые могут отображаться или нет и т.п. (список связей должет остаться открытым)

Хочу свести все данные в одну таблицу, где представить данные в виде "ключ/значение".
Сломал всю голову. Пример базы (PostgreSQL):
CREATE TABLE core (
  id serial primary key,
  level int,
  group int,
  parent int references core,
  key text,
  value text
);

Фрагмент данных:
INSERT INTO core (id, level, group, parent, key, value) VALUES
(1, 0, 1, 0, 'type', 'coreelements'),
(2, 0, 1, 0, 'name', 'Компоненты системы'),
(3, 1, 2, 1, 'type', 'page'),
(4, 1, 2, 1, 'name', 'Страницы'),
(5, 1, 2, 1, 'order', ''),
(6, 1, 3, 1, 'type', 'template'),
(7, 1, 3, 1, 'name', 'Шаблоны'),
(8, 1, 3, 1, 'order', ''),
(9, 1, 4, 1, 'type', 'position'),
(10, 1, 4, 1, 'name', 'Позиции'),
(11, 1, 4, 1, 'order', '18,19,20,21,22'),
(12, 1, 5, 1, 'type', 'module'),
(13, 1, 5, 1, 'name', 'Модули'),
(14, 1, 5, 1, 'order', ''),
(15, 2, 18, 4, 'name', 'Шапка'),
(16, 2, 18, 4, 'alias', 'header'),
(17, 2, 19, 4, 'name', 'Рабочая зона'),
(18, 2, 19, 4, 'alias', 'canvas'),
(19, 2, 20, 4, 'name', 'Левый сайдбар'),
(20, 2, 20, 4, 'alias', 'sidebar_left'),
(21, 2, 21, 4, 'name', 'Правый сайдбар'),
(22, 2, 21, 4, 'alias', 'sidebar_right'),
(23, 2, 22, 4, 'name', 'Подвал'),
(24, 2, 22, 4, 'alias', 'footer');

Помимо общего вопроса о структуре таблицы смущает меня вот что:

В необходимости поля level не уверен, убрать?
Поскольку родитель и зависимые записи имеют по несколько пар "ключ/значение", как
организовать связь? по ID, по group или третьим способом? Иными словами, что должно
быть в поле parent?
Как связать, скажем, страницы, модули и шаблоны?

Пример к последнему вопросу:
Страница 'settings', в header имеет модуль 6, в canvas - модули 1, 3 и 2, в остальных
- ничего.

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

Ответы

Ответ 1

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


Ответ 2

Базовый элемент таблицы - узел, к которому привязаны как его свойства, так и узлы-потомки.
CREATE TABLE core (
  id serial primary key,
  parent int references core,
  node bool,
  key text,
  value text
);


Два ключа (id, parent) используются для связи данных между собой;
Булево поле (node) используется для отличия типов данных (собственно узлов и свойств).
TRUE, если узел, FALSE, если свойства.
key и value - собственно свойства. 

Пример данных:
INSERT INTO core (id, parent, node, key, value) VALUES
(3, NULL, TRUE, NULL, NULL), -- узел-родитель
(4, 3, FALSE, 'alias', 'page'), -- свойства узла-родителя (4-6)
(5, 3, FALSE, 'name', 'Страницы'),
(6, 3, FALSE, 'order', '90,96,101,106'),
------
(90, 3, TRUE, NULL, NULL), -- узел-потомок
(91, 90, FALSE, 'name', 'Настройки системы'), -- свойства узла-потомка (91-94)
(92, 90, FALSE, 'alias', 'settings'), 
(93, 90, FALSE, 'template', '29'), -- связь с шаблоном
(94, 90, FALSE, 'module', '54,62,58'); -- очередность модулей на странице

Поле node можно убрать. Дело в том, что key/value для узлов равны NULL (что соответствует
TRUE в поле node), тогда как NOT NULL в этих полях == FALSE.