Горизонтальное хранение атрибутов на примере Oracle

При проектировании баз данных часто возникает вопрос: как лучше организовать хранение данных? Одним из ключевых решений является выбор между горизонтальным и вертикальным хранением данных. Оба подхода имеют свои особенности, преимущества и области применения.
Вертикальное хранение данных (Vertical Attribute Storage) предполагает, что каждый атрибут сущности представлен отдельным столбцом в таблице. Это традиционный подход, который используется в большинстве реляционных баз данных.
Горизонтальное хранение атрибутов (Horizontal Attribute Storage) — это подход к проектированию базы данных, при котором атрибуты сущности хранятся в виде строк в таблице, а не в виде отдельных столбцов. Этот метод часто используется для хранения динамических или пользовательских атрибутов, количество и структура которых заранее неизвестны. Его далее и рассмотрим на основе таблицы, содержащей информацию о местоположениях (LOCATION).

Пример структуры таблицы

Рассмотрим следующую таблицу LOCATION_REFNUM, которая используется для хранения атрибутов местоположений:

Пример данных

Предположим, у нас есть два местоположения: склад и офис. Для каждого из них мы хотим хранить рабочие часы и адрес (и не хотим эти данные заносить в предустановленные аттрибуты таблицы). Вставим данные в таблицу:

INSERT INTO LOCATION_REFNUM (LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE, DOMAIN_NAME, INSERT_USER, INSERT_DATE)
VALUES ('WAREHOUSE_001', 'WORKING_HOURS', '8:00-18:00', 'PUBLIC', 'admin', SYSDATE);

INSERT INTO LOCATION_REFNUM (LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE, DOMAIN_NAME, INSERT_USER, INSERT_DATE)
VALUES ('WAREHOUSE_001', 'ADDRESS', '123 Main St', 'PUBLIC', 'admin', SYSDATE);

INSERT INTO LOCATION_REFNUM (LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE, DOMAIN_NAME, INSERT_USER, INSERT_DATE)
VALUES ('WAREHOUSE_002', 'WORKING_HOURS', '9:00-17:00', 'PUBLIC', 'admin', SYSDATE);

Т.е. из данных имеем идентификатор исходного объекта (которому хотим добавить динамический аттрибут), идентификатор аттрибута (LOCATION_REFNUM_QUAL_GID) и его значение (LOCATION_REFNUM_VALUE).

Получение данных

Для получения всех атрибутов конкретного местоположения можно использовать запрос:

SELECT LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE
FROM LOCATION_REFNUM
WHERE LOCATION_GID = 'WAREHOUSE_001';

Результат:

Преобразование в вертикальный вид

Но часто возникает необходимость преобразовать данные из горизонтального хранения в вертикальное (например, для отчетов),

Способ 1. Использовать оператор PIVOT:

 SELECT * FROM (
  SELECT LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE
  FROM LOCATION_REFNUM
) 
PIVOT (
  MAX(LOCATION_REFNUM_VALUE)
  FOR LOCATION_REFNUM_QUAL_GID IN ('WORKING_HOURS' AS ATTRIBUTE_1, 'ADDRESS' AS ATTRIBUTE_2)
)
WHERE ATTRIBUTE_1 is not null;

Способ 2. Через группировку GROUP BY

select l.location_gid,
 MAX(CASE WHEN lr.LOCATION_REFNUM_QUAL_GID = 'WORKING_HOURS' THEN lr.LOCATION_REFNUM_VALUE END) AS DELLIN_ADDRESSID,
    MAX(CASE WHEN lr.LOCATION_REFNUM_QUAL_GID = 'ADDRESS' THEN lr.LOCATION_REFNUM_VALUE END) AS DELLIN_COUNTERAGENTID
    FROM 
    location l
LEFT JOIN 
    location_refnum lr ON lr.location_gid = l.location_gid
    AND lr.LOCATION_REFNUM_QUAL_GID IN ('WORKING_HOURS', 'ADDRESS')
GROUP BY 
    l.location_gid

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

Способ 3. Через подзапросы (думаю, что для больших массивов менее предпочтителен из соображений производительности)

SELECT 
    lr.LOCATION_GID,
    (SELECT LOCATION_REFNUM_VALUE 
     FROM LOCATION_REFNUM 
     WHERE LOCATION_GID = lr.LOCATION_GID 
       AND LOCATION_REFNUM_QUAL_GID = 'WORKING_HOURS') AS WORKING_HOURS,
    (SELECT LOCATION_REFNUM_VALUE 
     FROM LOCATION_REFNUM 
     WHERE LOCATION_GID = lr.LOCATION_GID 
       AND LOCATION_REFNUM_QUAL_GID = 'ADDRESS') AS ADDRESS
FROM 
    (SELECT DISTINCT LOCATION_GID FROM LOCATION_REFNUM) lr;

Результат:

Заключение

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

Преимущества горизонтального хранения

1. Гибкость: Легко добавлять новые атрибуты без изменения структуры таблицы.
2. Масштабируемость: Подходит для систем с большим количеством динамических или пользовательских атрибутов.
3. Универсальность: Один и тот же подход можно применять для разных типов данных.

Недостатки горизонтального хранения

1. Сложность запросов: Для получения данных требуется большее количество SQL-запросов или сложные преобразования.
2. Производительность: При большом объеме данных запросы могут выполняться медленнее из-за необходимости объединения строк.