Шаблон проектирования для пользовательских полей в реляционной базе данных
Я поставил задачу создать (относительно) простую систему отчетности. В этих системах пользователю будет показан результат таблицы отчета. Таблица имеет несколько полей, и каждое поле дает некоторую часть информации пользователю в каждой записи. Однако моя проблема в том, что каждое поле отчета не будет объявлено разработчиком. Он должен быть объявлен пользователем системы. Так что моя таблица отчетов динамична.
Я видел пример в Data Driven Custom View Engine в ASP.NET MVC для создания динамических форм с использованием Asp.net MVC Framework, но я не знаю, подходит это для моей системы или нет.
Update1:
В настоящее время я закончил со следующей диаграммой отношений сущностей:
На приведенной выше диаграмме я храню каждую запись для отчета в Report Таблица. Также я храню тип отчета в ReportType, Для каждого поля, которое будет использоваться в отчете, я буду использовать ReportFieldValue, Тип полей будет храниться в ReportField,
Поэтому, если я хочу сначала добавить запись в мою базу данных, я добавляю строку в Report Таблица. Затем для каждой добавленной записи я добавлю строку в ReportFieldValue Таблица.
Однако, как вы можете заметить, в этом подходе я должен хранить каждое значение поля в char(255). Проблема для полей типа как datetime это не должно быть сохранено как строка. Существует ли какая-либо схема проектирования или архитектура для систем такого типа?
4 ответа
Избегайте строковых данных путем замены VALUE с NUMBER_VALUE, DATE_VALUE, STRING_VALUE, Эти три типа достаточно хороши в большинстве случаев. Вы можете добавить XMLTYPE и другие необычные столбцы позже, если они необходимы. А для Oracle используйте VARCHAR2 вместо CHAR для экономии места.
Всегда старайтесь хранить значения как правильный тип. Собственные типы данных быстрее, меньше, проще в использовании и безопаснее.
У Oracle есть универсальная система типов данных (ANYTYPE, ANYDATA и ANYDATASET), но эти типы сложны в использовании и их следует избегать в большинстве случаев.
Архитекторы часто думают, что использование единого поля для всех данных облегчает задачу. Это облегчает создание красивых изображений модели данных, но делает все остальное более сложным. Рассмотрим эти вопросы:
- Вы не можете делать ничего интересного с данными, не зная типа. Даже для отображения данных полезно знать тип для выравнивания текста. В 99,9% всех случаев использования пользователю будет очевидно, какой из 3 столбцов является релевантным.
Разработка безопасных для типов запросов к строковым данным является болезненной. Например, скажем, вы хотите найти "Дата рождения" для людей, рожденных в этом тысячелетии:
select * from ReportFieldValue join ReportField on ReportFieldValue.ReportFieldid = ReportField.id where ReportField.name = 'Date of Birth' and to_date(value, 'YYYY-MM-DD') > date '2000-01-01'Можете ли вы обнаружить ошибку? Вышеприведенный запрос опасен, даже если вы сохранили дату в правильном формате, и очень немногие разработчики знают, как правильно ее исправить. Oracle имеет оптимизации, которые затрудняют навязывание определенного порядка операций. Вам будет нужен такой запрос, чтобы быть в безопасности:
select * from ( select ReportFieldValue.*, ReportField.* --ROWNUM ensures type safe by preventing view merging and predicate pushing. ,rownum from ReportFieldValue join ReportField on ReportFieldValue.ReportFieldid = ReportField.id where ReportField.name = 'Date of Birth' ) where to_date(value, 'YYYY-MM-DD') > date '2000-01-01';Вам не нужно просить каждого разработчика писать свои запросы таким образом.
Ваш дизайн представляет собой разновидность модели данных Entity Attribute Value (EAV), которая часто рассматривается как шаблон для проектирования базы данных.
Возможно, лучшим подходом для вас будет создание таблицы значений отчетности, скажем, с 300 столбцами (от NUMBER_VALUE_1 до NUMBER_VALUE_100, VARCHAR2_VALUE_1..100 и DATE_VALUE_1..100).
Затем спроектируйте остальную часть своей модели данных, чтобы отслеживать, какие отчеты используют, для каких столбцов и для чего они используют каждый столбец.
Это имеет два преимущества: во-первых, вы не храните даты и числа в строках (преимущества которых уже были отмечены), а во-вторых, вы избегаете многих проблем производительности и целостности данных, связанных с моделью EAV.
РЕДАКТИРОВАТЬ - добавить некоторые эмпирические результаты модели EAV
Используя базу данных Oracle 11g2, я переместил 30 000 записей из одной таблицы в модель данных EAV. Затем я запросил модель, чтобы вернуть эти 30 000 записей.
SELECT SUM (header_id * LENGTH (ordered_item) * (SYSDATE - schedule_ship_date))
FROM (SELECT rf.report_type_id,
rv.report_header_id,
rv.report_record_id,
MAX (DECODE (rf.report_field_name, 'HEADER_ID', rv.number_value, NULL)) header_id,
MAX (DECODE (rf.report_field_name, 'LINE_ID', rv.number_value, NULL)) line_id,
MAX (DECODE (rf.report_field_name, 'ORDERED_ITEM', rv.char_value, NULL)) ordered_item,
MAX (DECODE (rf.report_field_name, 'SCHEDULE_SHIP_DATE', rv.date_value, NULL)) schedule_ship_date
FROM eav_report_record_values rv INNER JOIN eav_report_fields rf ON rf.report_field_id = rv.report_field_id
WHERE rv.report_header_id = 20
GROUP BY rf.report_type_id, rv.report_header_id, rv.report_record_id)
Результаты были:
1 row selected.
Elapsed: 00:00:22.62
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2026 | 53 (67)|
| 1 | SORT AGGREGATE | | 1 | 2026 | |
| 2 | VIEW | | 130K| 251M| 53 (67)|
| 3 | HASH GROUP BY | | 130K| 261M| 53 (67)|
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | 130K| 261M| 36 (50)|
| 6 | TABLE ACCESS FULL | EAV_REPORT_FIELDS | 350 | 15050 | 18 (0)|
|* 7 | INDEX RANGE SCAN | EAV_REPORT_RECORD_VALUES_N1 | 130K| | 0 (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID| EAV_REPORT_RECORD_VALUES | 372 | 749K| 0 (0)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("RV"."REPORT_HEADER_ID"=20)
8 - filter("RF"."REPORT_FIELD_ID"="RV"."REPORT_FIELD_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
275480 consistent gets
465 physical reads
0 redo size
307 bytes sent via SQL*Net to client
252 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Это 22 секунды, чтобы получить 30000 строк по 4 столбца в каждой. Это слишком долго. С плоского стола мы бы смотрели под 2 секунды, легко.
Используйте MariaDB с динамическими столбцами. По сути, это позволяет вам поместить все столбцы сборника в один столбец, но при этом предоставить вам эффективный доступ к ним.
Я бы оставил несколько общих полей в своих столбцах.
Больше обсуждения EAV и предложений (и как это сделать без динамических столбцов).
Ну, у вас есть очень хорошее замечание о хранении данных в правильных типах данных.
И я согласен, что это создает проблему для пользовательских систем данных.
Одним из способов решения этой проблемы является добавление таблиц для каждой группы типов данных (целые числа, числа с плавающей запятой, строки, двоичные числа и даты) вместо сохранения значения в ReportFieldValue Таблица. Однако это усложнит вашу жизнь, поскольку вам придется выбирать и объединять несколько таблиц, чтобы получить единый результат.
Другой способ будет добавить столбец типа данных в ReportFieldValue и создайте пользовательскую функцию для динамического приведения данных из строк к соответствующему типу данных (используя значение в столбце типа данных), чтобы вы могли использовать это для сортировки, поиска и т. д.
Сервер Sql также имеет тип данных, называемый sql_variant он должен поддерживать несколько типов, и хотя я никогда не работал с ним, документация кажется многообещающей.