Привет всем!
Не знаю куда запостить этот вопрос: в архитектуру или в базы данных. Вопрос по организации хранения данных.
Допустим у нас есть несколько сущностей, которые нужно хранить в базе данных. У этих сущностей часть атрибутов совпадает, часть различается.
Все сущности допускают некоторый единообразный функционал. Например (или/или/или):
1. Все рассматриваемые сущности поддерживают иерарахическое хранение (когда, связи между родительской и дочерней сущностями хранятся в отдельной таблице)
2. Все сущности поддерживают workflow с возможностью отката действия (придется хранить журнал workflow-транзакций, чтобы обеспечить откат именно в то состояние, в котором сущность была ДО выполнения откатываемого действия)
3. Все сущности поддерживают логирование выполняемых над ними действий с возможностью отыскать ту сущность, над которой выполнялось действие зафиксированное определенной строкой лога (очевидно, в таблице логов придется хранить ID-шник сушности, действию над которой соотвествует конкретная строка)
4. ...
Да бывает случаев, когда нужно обеспечить некую единообразную обработку сущностей, причем обработка эта потребует в базе данных специальной таблицы (или таблиц).
Причем, как правило, сущности слишком разные, чтобы засунуть их в одну таблицу.

Итак имеем: несколько таблиц для хранения сущностей.
Нужно обеспечить: выполнение некоторого функционала для сущностей, хранящихся в каждой из таблиц.

СОБСТВЕННО ВОПРОС: как правильнее организовать ту часть структуры базы данных (таблицы, хранимые процедуры), которая обеспечивает выполнение "общего" функционала?

Вижу следующие пути решения:
1. Для каждой таблицы сущностей завести свой набор таблиц и хранимок и проигнорировать тот факт, что в базе получается множество таблиц с практически одинаковой структурой и хранимок с почти одинаковым кодом. Явный минус: задолбаешься такое сопровождать (кодогенерация этот минус немного смягчает, но ИМХО весьма немного...).
2. Завести один экземпляр набора "общих" таблиц и хранимок для всех таблиц сущностей. Сущности по-прежнему хранятся каждая в своей таблице. В этом случае, вероятно, придется отказаться от внешних ключей между таблицами сущностей и таблицами "общего функционала".
3. Вынести общие атрибуты сущностей в отдельную таблицу, связать ее в отношении 1:1 с таблицами, содержашими остальные атрибуты сущностей. Общие таблицы и хранимки "завязываются" на одну общую таблицу. Мне этот вариант нравится больше всего, но "общие" таблицы становятся узким местом, а сущностей с "общим функционалом" может быть много (очень много).

Хочется узнать мнение более грамотных и опытных товарищей.

PS. Вопрос, в общем-то пока скорее теоретический, чем имеющий значение для текущего конкретного проекта. Но вопрос лично для меня ВАЖНЫЙ!

Здравствуйте, stomsky, Вы писали:

S>Допустим у нас есть несколько сущностей, которые нужно хранить в базе данных. У этих сущностей часть атрибутов совпадает, часть различается.

Если сущности _разные_ и общий код носит чисто инфраструктурый характер, я бы остановился на варианте 1. Кодогенерация не так уж ужасна, особенно если отучить лазить руками в сгенеренный код. Как бонус — не придётся ломать всё, когда для одной сущности понадобится дополнительный фукнционал.

Здравствуйте, stomsky, Вы писали:

S>1. Для каждой таблицы сущностей завести свой набор таблиц и хранимок ...
S>2. Завести один экземпляр набора "общих" таблиц и хранимок для всех таблиц сущностей....
S>3. Вынести общие атрибуты сущностей в отдельную таблицу...


п. 2 не совсем понял....

п. 3(а) Вместо дополнительных таблиц, остальные атрибуты сущностей можно хранить в XML (если СУБД поддерживает)
п. 4. Можно использовать наследование (если СУБД поддерживает) или создавать общие представления (виды) через UNION. Но в этом случае планировщик SQL запроса должен уметь подключать индексы внутри UNION, иначе будут тормоза.


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

Для SELECT, DELETE два варианта
1. Одна общая таблица. В этом случае накладно строить индекс по дополнительным атрибутам. Вариант идеальный, если выборка по дополнительным атрибутам не сильно напрягает систему.
2. Много таблиц объединенных в общие представления. Тут главное чтобы планировщик умел подставлять индексы внутри UNION запросов, иначе тормоза...


В любом случае вначале нужно определить какой цели мы хотим достичь. Что мы хотим облегчить, улучшить, и чем мы готовы пожертвовать.

Если общей выборки по сущностям не требуется, то не заморачивайся и реализуй каждую сущность самостоятельно, некоторые универсальные функции можно реализовать просто подставляя в них имя (ID) сущности (группировки, транзакции, логи, ...)

Здравствуйте, stomsky, Вы писали:

Приведите вашу БД в 3NF а потом уже думайте. Куча вопросов в итоге отпадёт сама собой. Судя по тому что вы говорит она у вас даже не в 2NF. Пока вы базу к нормальному виду не приведёте, вы будете страдать

Здравствуйте, Sorc17, Вы писали:
S>Приведите вашу БД в 3NF а потом уже думайте. Куча вопросов в итоге отпадёт сама собой. Судя по тому что вы говорит она у вас даже не в 2NF. Пока вы базу к нормальному виду не приведёте, вы будете страдать
Да привести-то к 3НФ не большая проблема.

Рассмотрим модельный пример:
1. Сущность "Сотрудник" с атрибутами {ID_сотрудника; ФИО_сотрудника (для простоты допустим нам не надо разделять фамилию, имя и отчество)}
2. Сущность "Банковская_карта" с атрибутами {ID_Карты; Номер_карты; ID_владельца_карты}, где ID_владельца_карты — ссылка на [Сотркдник].[ID_сотрудника]

Для простоты будем считать, что ID-шники обеих сущностей имеют один тип.

Для каждой из сущностей нам надо реализовать, например, workflow.
Сущности, с точки зрения предметной области (например, мы производим денежные зачисления на банковские карты сотрудников) совершенно разные. А модель конечного автомата, на основе которой будет строиться функционал рабочих потоков один и тот же...
Чем мне здесь поможет 3-я нормальная форма???

Если бы сущностей было бы немного, я бы пошел на некую денормализацию и ввел бы технологическую сущность "Сущность_поддерживающая_workflow" и на нее завязал бы с одной строны функционал WF, а с другой в отношении 1:1 привязал бы строки из таблиц "Сотрудник" или "Банковская_карта".
Это тоже, конечно, вариант не без недостатков, но их можно компенсировать использованием хранимых процедур.
Но таблица, хранящая сущности "Сущность_поддерживающая_workflow" становится "узким местом" и меня это смущает.

Здравствуйте, stomsky, Вы писали:


S>Рассмотрим модельный пример:
S>1. Сущность "Сотрудник" с атрибутами {ID_сотрудника; ФИО_сотрудника (для простоты допустим нам не надо разделять фамилию, имя и отчество)}
S>2. Сущность "Банковская_карта" с атрибутами {ID_Карты; Номер_карты; ID_владельца_карты}, где ID_владельца_карты — ссылка на [Сотркдник].[ID_сотрудника]

Из собственной практики:
Для каждой сущности авто-заполняйте универсальные атрибуты: Тип сущности, Ид, Шифр, Краткое наименование (не обязательно), Полное наименование, Описание (Дополнение к полному наименованию)
Например:
1. Сущность "Сотрудник" (Ид — ID_сотрудника; Шифр — табельный номер; Краткое наименование — Фамилия И.О.; Полное наименование — Фамилия Имя Отчество; Описание — Где, кем работает и др.)
2. Сущность "Банковская_карта" (Ид — ID_Карты; Шифр — номер карты; Краткое наименование — Фамилия И.О. владельца; Полное наименование — Фамилия Имя Отчество владельца; Описание — Где, кем работает и др.)

Перечисленных 6 атрибутов вам будет достаточно для создания любых универсальных механизмов (Поиск-Выбор, Ссылка, Печать). Можно еще добавить атрибут ИдГруппы для универсального механизма группировки

Здравствуйте, sereginseregin, Вы писали:
S>Перечисленных 6 атрибутов вам будет достаточно для создания любых универсальных механизмов (Поиск-Выбор, Ссылка, Печать). Можно еще добавить атрибут ИдГруппы для универсального механизма группировки
Напоминает 1С-ку
Только по моему вопросу это ничего не проясняет. Для меня, во всяком случае
Вот завел я эти общие атрибуты для каждой сущности. И каждая сущность с этими общими (и еще всякими специфическими) атрибутами хранится в своей таблице.
А теперь мне надо реализовать общий функционал, который ТРЕБУЕТ ХРАНЕНИЯ ПРОМЕЖУТОЧНЫХ РЕЗУЛЬТАТОВ В ОТДЕЛЬНОЙ ТАБЛИЦЕ.
Например, для реализации workflow придется завести таблицу "Журнал_переходов_между_состояниями". И в этом "журнале" будет столбец со ссылкой (и внешним ключом) на нашу сущность ("Сотрудник" или "Банковская_карта").
Наличие же внешнего ключа предполагает наличие только одной master-таблицы. Это либо "Сотрудник", либо "Банковская_Карта".
А функционал workflow должны поддерживать ОБЕ сущности.
Как быть? Завести для каждой сущности свою таблицу-"журнал"? (что мне здесь и предлагают)
А может завести искусственную сущность "Сущность_поддерживающая_workflow", которая будет связана с "журналом" в отношении 1:M, и с сущностями "Сотрудник" и "Банковская_карта" в отношении 1:1 (либо с "Сотрудником", либо с "Банковской_картой" в зависимости от значения атрибута "Сущность_поддерживающая_workflow"."Тип_сущности")?

Здравствуйте, stomsky, Вы писали:

S>Здравствуйте, sereginseregin, Вы писали:
S>>Перечисленных 6 атрибутов вам будет достаточно для создания любых универсальных механизмов (Поиск-Выбор, Ссылка, Печать). Можно еще добавить атрибут ИдГруппы для универсального механизма группировки
S>Напоминает 1С-ку
S>Только по моему вопросу это ничего не проясняет. Для меня, во всяком случае
S>Вот завел я эти общие атрибуты для каждой сущности. И каждая сущность с этими общими (и еще всякими специфическими) атрибутами хранится в своей таблице.
S>А теперь мне надо реализовать общий функционал, который ТРЕБУЕТ ХРАНЕНИЯ ПРОМЕЖУТОЧНЫХ РЕЗУЛЬТАТОВ В ОТДЕЛЬНОЙ ТАБЛИЦЕ.
S>Например, для реализации workflow придется завести таблицу "Журнал_переходов_между_состояниями". И в этом "журнале" будет столбец со ссылкой (и внешним ключом) на нашу сущность ("Сотрудник" или "Банковская_карта").
S>Наличие же внешнего ключа предполагает наличие только одной master-таблицы. Это либо "Сотрудник", либо "Банковская_Карта".
S>А функционал workflow должны поддерживать ОБЕ сущности.
S>Как быть? Завести для каждой сущности свою таблицу-"журнал"? (что мне здесь и предлагают)
S>А может завести искусственную сущность "Сущность_поддерживающая_workflow", которая будет связана с "журналом" в отношении 1:M, и с сущностями "Сотрудник" и "Банковская_карта" в отношении 1:1 (либо с "Сотрудником", либо с "Банковской_картой" в зависимости от значения атрибута "Сущность_поддерживающая_workflow"."Тип_сущности")?

Не понимаю я что вы хотите сделать. Это слишком бизнес задачи для меня.

S>И в этом "журнале" будет столбец со ссылкой (и внешним ключом) на нашу сущность ("Сотрудник" или "Банковская_карта").
S>Наличие же внешнего ключа предполагает наличие только одной master-таблицы.

Ключ может быть составным и ссылаться сразу на обе таблицы. Единственное что по делу могу тут сказать

S>Здравствуйте, stomsky, Вы писали:

S>>А теперь мне надо реализовать общий функционал, который ТРЕБУЕТ ХРАНЕНИЯ ПРОМЕЖУТОЧНЫХ РЕЗУЛЬТАТОВ В ОТДЕЛЬНОЙ ТАБЛИЦЕ.
S>>Например, для реализации workflow придется завести таблицу "Журнал_переходов_между_состояниями". И в этом "журнале" будет столбец со ссылкой (и внешним ключом) на нашу сущность ("Сотрудник" или "Банковская_карта").
S>>Наличие же внешнего ключа предполагает наличие только одной master-таблицы. Это либо "Сотрудник", либо "Банковская_Карта".
S>>А функционал workflow должны поддерживать ОБЕ сущности.
1 Вариант — создать общее представление. Индексы отдельно для представления не создаются. При построении плана запроса планировщик будет использовать первичные ключи каждой сущности отдельно (Но не во всех СУБД это реализовано). Целостность данных реализуется триггерами

S>>А может завести искусственную сущность "Сущность_поддерживающая_workflow", которая будет связана с "журналом" в отношении 1:M, и с сущностями "Сотрудник" и "Банковская_карта" в отношении 1:1 (либо с "Сотрудником", либо с "Банковской_картой" в зависимости от значения атрибута "Сущность_поддерживающая_workflow"."Тип_сущности")?
Это 2 Вариант. Почему вы считаете что нужно создавать отдельную "Сущность_поддерживающая_workflow". Такая сущность создается одна в СУБД для всех таблиц на все сдучаи жизни. Например, что Вам мешает вести журнал изменения данных в таблицах ссылаясь на эту же сущность.


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

Ключ можно еще сделать не составной (Тип_сущности, Ид), а сквозной (Глобальный) единый для всех сущностей (ГИд)


ВЫВОД такой:

Создавать общую сущность Вам необходимо.
1 Динамический способ, через представление (или наследование в ОРСУБД). Быстрый способ чтоб работало. Сложности с целосностью (на практике записи удаляются редко, у себя мы их восстанавливаем через свой журнал изменений, для некоторых случаев строим триггер). Еще в этом случае СУБД должна уметь использовать индексы внутри UNION запросов.
2 Статический способ, через отдельную таблицу. Те же сложности с целостностью между общей сущностью и остальными (один к одному тут не подходит), только через триггер, но зато для workflow жесткий внешний ключ на общую таблицу. Есть еще материализованное представление, как там реализовано с индексами, не пробовал.
3 Способ новаторский (пока только в теории). Все справочные сущности можно загнать в одну таблицу (на ... все эти теории РБД), все дополнительные атрибуты сохранять через XML (Индексы по XML атрибутам в некоторых СУБД строить можно, но большого ускорения в экономических системах они все равно не дают).

Какой выбрать способ? Вопрос технический, что Вам удобнее и что реализуемо в Вашей СУБД.

Здравствуйте, sereginseregin, Вы писали:

S>1 Вариант — создать общее представление. Индексы отдельно для представления не создаются. При построении плана запроса планировщик будет использовать первичные ключи каждой сущности отдельно (Но не во всех СУБД это реализовано). Целостность данных реализуется триггерами
Да на триггерах и дурак сделает! Тока потом сопровождать задолбается! Сущностей-то в реальной системе не две будет, а чуточку побольше!
Хочется не просто чтобы работало, чтобы еще и красиво было!

S>Это 2 Вариант. Почему вы считаете что нужно создавать отдельную "Сущность_поддерживающая_workflow". Такая сущность создается одна в СУБД для всех таблиц на все сдучаи жизни. Например, что Вам мешает вести журнал изменения данных в таблицах ссылаясь на эту же сущность.
И становится эта базовая сущность узким местом при вставках и выборках. Ладно, если у нас 100 сотрудников и 300 карточек. А если у нас здесь же еще платежные поручения хранятся, да файлы импортированные, да внутренняя кадровая номенклатура, да еще много-много всего? И того получается, что таблица с базовой сущностью уже в небольшой базе раздувается до нескольких миллионов строк. И выходит из нее немаленькое количество внешних ключей.
А если речь о банковской системе то там и до сотен миллионов строк дотянет запросто.
И простые запросы к базе начнут притормаживать...

S>3 Способ новаторский (пока только в теории). Все справочные сущности можно загнать в одну таблицу (на ... все эти теории РБД), все дополнительные атрибуты сохранять через XML (Индексы по XML атрибутам в некоторых СУБД строить можно, но большого ускорения в экономических системах они все равно не дают).
Улыбнуло

Сейчас чего-то склоняюсь к варианту с кодогенерацией....

Здравствуйте, stomsky, Вы писали:

S>>Это 2 Вариант. Почему вы считаете что нужно создавать отдельную "Сущность_поддерживающая_workflow". Такая сущность создается одна в СУБД для всех таблиц на все сдучаи жизни. Например, что Вам мешает вести журнал изменения данных в таблицах ссылаясь на эту же сущность.
S>И становится эта базовая сущность узким местом при вставках и выборках. Ладно, если у нас 100 сотрудников и 300 карточек. А если у нас здесь же еще платежные поручения хранятся, да файлы импортированные, да внутренняя кадровая номенклатура, да еще много-много всего? И того получается, что таблица с базовой сущностью уже в небольшой базе раздувается до нескольких миллионов строк. И выходит из нее немаленькое количество внешних ключей.
S>А если речь о банковской системе то там и до сотен миллионов строк дотянет запросто.
S>И простые запросы к базе начнут притормаживать...

1 миллион или 1000 миллионов — только на практике на конкретной железке узнаешь где узкое место. По мне, чем решение проще логически, тем лучше. А усложнять, когда уже железки или СУБД точно не тянут.

Здравствуйте, stomsky, Вы писали:

S>Хочется узнать мнение более грамотных и опытных товарищей.
Мой опыт привёл к тому, что субд надо рассматривать как нижний уровень и проектировать её так, чтобы можно было с ней эффективно работать. Это иногда обозначает спихивание разных сущностей в одну таблицу, если запросы могут включать эти сущности одновременно (чтобы избегать join-ы больших таблиц).
Удобство работы обеспечивается языком программирования и ORM. Скажем, твоя проблема решается тупо интерфейсами.
Как следствие из этого — избегать триггеры и хранимки, а реализовывать всю логику в коде, применять их только в целях оптимизации, если что-то тормозит.

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