Within the database (in this case, PostgreSQL, but this is not the point) there are several small tables that contain practically immutable, but very often used data:

  • page groups (pagegroups),
  • pages (pages),
  • templates
  • Positions on pages (positions)
  • modules (modules),
  • user groups (usergroups).

Each table has the ID, title, alias, and display order fields. There are connections between the data:

  • user groups have different access rights
  • modules can be found at different positions on different pages,
  • each page can have its own template,
  • The template consists of positions (header, sidebar_left, canvas, sidebar_right, footer), which can be displayed or not , etc. (the list of links should remain open)

I want to put all the data in one table, where to present the data in the form of "key / value". Broke the whole head. Example database (PostgreSQL):

CREATE TABLE core ( id serial primary key, level int, group int, parent int references core, key text, value text );

Fragment of data:

 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');

In addition to the general question about the structure of the table confuses me this:

  • The need for the level field is not sure, remove?
  • Since the parent and the dependent entries have several key / value pairs, how do you organize the connection? by ID, by group or the third way? In other words, what should be in the parent field?
  • How to link, say, pages, modules and templates?

Example for the last question:

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

I fully admit that after such a table is obtained, I will return to the original - normalized - variant. But since there is an opportunity to try different options - I want to try this one too.

  • First, figure it out. For what? What problem will you solve this? At least in the matter did not see this? If it doesn't matter where you want to go, it doesn't matter where to go. - asianirish

2 answers 2

Nafig such a structure.

Different entities - different tables!

What a perverted desire to shove everything into one?

Even if now you miraculously dazzle all this so that it works, in the future there will be big problems with scaling and changing each entity.

  • I would shift the focus from "nafig such a structure" to "nafig such a structure." Specifically, the described version does not suit me. I think it is possible to do with four fields and the lack of problems when scaling. The perverted aspiration is caused by the desire to obtain basic data about the system within a single query without JOINs and other unions. Again, the data is extremely rarely changed and extremely often read. - Denis Khvorostin
  • I left everything as it is, but the task itself is still interesting. Three typical approaches to solving were recently described on Habré - Denis Khvorostin

The base element of the table is the node to which both its properties and descendant nodes are attached.

 CREATE TABLE core ( id serial primary key, parent int references core, node bool, key text, value text );
  • Two keys (id, parent) are used to link data to each other;
  • A boolean field (node) is used to distinguish data types (the actual nodes and properties). TRUE if the node is FALSE if the properties.
  • key and value are the properties themselves.

Sample data:

 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'); -- очередность модулей на странице

The node field can be removed. The point is that the key / value for nodes is NULL (which corresponds to TRUE in the node field), whereas NOT NULL in these fields == FALSE.