Объединение идентичных таблиц, но с сохранением отдельной ссылочной целостности

Рассмотрим размерную модель с таблицами фактов типа (fk_dim1value, fk_dim2value, ..., value), где fk_X столбцы являются внешними ключами в соответствующие тривиальные таблицы измерений dim1value (id, value), dim2value (id, value), и т. Д.

Эти таблицы фактов и измерений собираются автоматически из разрозненных источников, поэтому их много ... и они избыточны: все таблицы значений измерений структурно идентичны, (id, value), представляя простые коллекции текстовых значений без дополнительной семантики ( единственное отличие состоит в том, что разные внешние ключи ссылаются на них в различных таблицах фактов). Менее тривиальные размерные типы, вероятно, появятся позже, но набор различных типов размеров останется небольшим.

Поэтому я хочу объединить таблицы измерений в одну таблицу dimvalue (fk_dim, dimvalue_id, value), где fk_dim ссылается на таблицу dimension (dim_id, name), а dimvalue_id уникален только в пределах каждого измерения. Тогда естественный первичный ключ будет составным: (fk_dim, dimvalue_id).

Столбцы внешнего ключа таблицы фактов теперь все ссылаются на одну и ту же таблицу dimvalue (fk_dim, dimvalue_id, value) ... но, конечно, каждый столбец связан с определенным измерением и, таким образом, по-прежнему должен быть ограничен ссылкой на значения этого измерения конкретно (горизонтальный раздел унифицированного таблица dimvalue).

Есть ли (разумный) способ сделать это?

Я имею в виду что-то вроде «наполовину составного» внешнего ключа, то есть ссылку из одного столбца на «срез» составного PK с фиксированным значением для другого столбца (столбцов). «Полностью составной» FK будет FOREIGN KEY (col1, col2) REFERENCES dimvalue (fk_dim, dimvalue_id), но здесь fk_dim фиксировано, и поэтому «исходная» сторона ключа - это всего лишь один столбец, ссылающийся на второй столбец dimvalue первичного ключа; что-то вроде FOREIGN KEY (fk_dim7value) REFERENCES dimvalue (fk_dim=7, dimvalue_id).

Возможно ли что-то подобное? Или я заблудился в этом последнем абзаце? Должен ли я отказаться и просто использовать внешний ключ для всей dimvalue таблицы, а затем добавить проверочные ограничения для ограничения по размерности? Или ссылочная целостность требует, чтобы я отказался еще больше и просто принял все отдельные идентичные таблицы?

(Влияние ограничений на производительность записи не имеет значения; производительность чтения - это цель проекта.)


postgresql sql database foreign-keys referential-integrity
person Gunnlaugur Briem    schedule 09.09.2009    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы изложили эти ключевые соображения

  • Данные собираются из разрозненных систем, поэтому я прихожу к выводу, что это таблица «отчетов», а не система типа «операции» или «транзакции».
  • каждая таблица фактов содержит по одному фрагменту бизнес-данных на строку, то есть столбец "значение".
  • ваша таблица фактов, кажется, содержит только одну "меру" или "факт"
  • производительность записи не имеет значения, целью является только производительность чтения. Это подтверждает мой вывод о том, что это «отчетная» таблица.

Учитывая, что вам нужно быстрое чтение, я бы выбрал дизайн «большой таблицы». Конечно, дизайн больших таблиц УЖАСНО для транзакционных систем, но это не так. Под большой таблицей я подразумеваю ТАБЛИЦА (DIM1VALUE, DIM2VALUE, DIM3VALUE, DIM4VALUE .... DIMNVALUE, FACTVALUE)

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

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

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

person Eric Sabine    schedule 10.09.2009
comment
Спасибо; вы правы, это схема «отчетности», но мне не нравится подход «большой таблицы». Это увеличило бы ширину таблиц фактов (значения измерений могут быть широкими), что затруднило бы ввод-вывод запросов. Это не спасло бы меня, так как я все равно могу избежать соединения с таблицами измерений. И это связывало бы меня с этой моделью только тривиальных значений измерений, которая не является постоянной (я не совсем понял это, извините; я изменил вопрос). - person Gunnlaugur Briem; 14.09.2009