Основная ассоциативная таблица?

Рассмотрим модель сопоставления клиентов и услуг. Клиенты могут быть как поставщиками, так и потребителями услуг в разное время. Клиентами могут быть отдельные лица или группы (компании), причем последние имеют несколько контактов. Контакты могут иметь несколько адресов, телефонов, e-mail. Некоторые из этих отношений будут отношениями «один к одному» (например, услуга поставщику), но большинство из них будут отношениями «один ко многим» или «многие ко многим» (несколько контактов в компании будут иметь один и тот же адрес).

В этой модели обычно существует несколько ассоциативных таблиц, например, client_contact, contract_addr, contact_phone, contact_email, service_provider, service_consumer и т. д.

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

Однако мне пришло в голову: почему бы не иметь единую «главную» ассоциативную таблицу, содержащую все ассоциации? Потребуется, чтобы эта главная таблица имела «тип ассоциации» в дополнение к двум ПК, и чтобы все ПК были одного типа (целые, GUID и т. д.).

С одной стороны, запросы стали бы более сложными, потому что для каждого соединения нужно было бы указывать тип и PK. С другой стороны, все соединения будут иметь доступ к одной и той же таблице, и при соответствующем индексировании и кэшировании производительность может значительно возрасти.

Я предположил, что может быть шаблон (или анти-шаблон), описывающий этот подход, но ничего не нашел в Интернете. Кто-нибудь пробовал? Если да, то масштабируется?

Любые ссылки, которые вы можете предоставить, будут оценены.


design-patterns database-design anti-patterns associative-table
person djhill8262    schedule 27.11.2010    source источник
comment
Добавлено в избранное и проголосовано, так как я чувствую, что это действительно плохая идея, но я не могу точно определить точную (техническую) причину. Кто-то может возразить, что вы очень, ОЧЕНЬ уязвимы для проблем с блокировкой с этой настройкой, и вы не можете добавить метаданные в свои отношения «многие ко многим», если это необходимо. Кроме того, я бы предположил, что правильная СУБД оптимизирована для работы с ситуациями, которые вы упоминаете в своем случае.   -  person Erik van Brakel    schedule 27.11.2010
comment
Это была моя мысль, поэтому я был удивлен, не найдя ее задокументированной как действительно плохую идею, по крайней мере, там, где было бы много CRUD. Я подозреваю, что с небольшими объемами TX и там, где запросы могут работать с низкой изоляцией, это может быть жизнеспособно. Я предполагал, что единственная основная таблица может обеспечить лучшую оптимизацию, но это может зависеть от конкретной СУБД. Было бы поучительно сравнить планы (с мастером и помощником регуара).   -  person djhill8262    schedule 27.11.2010
comment
Я думаю, что type станет частью ключа или индексов более высокого порядка, поэтому соединения будут выглядеть примерно так: on Type = 'Type1' AND PK1 = PK2? Будет ли производительность действительно лучше в этом случае?   -  person Mike K.    schedule 30.11.2010

Ответы (3)


arrow_upward
1
arrow_downward

То, что вы описываете, напоминает мне таблицы фактов из хранилищ данных. Насколько я понимаю, вы начинаете с типичной схемы транзакций с таблицей для моделирования всех отношений «многие ко многим». Затем, чтобы реструктурировать данные для более легкого многомерного анализа, вы можете агрегировать некоторые/все отношения в вашей схеме в одну широкую таблицу, где каждый столбец является ключом. Это эффективно выполняет все возможные соединения заранее и сбрасывает их в таблицу, инвертируя цель соединений запросов с отслеживанием отношений на получение свойств ваших сущностей.

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

person spieden    schedule 30.11.2010
comment
Спасибо dacc, это дало мне образец для исследований и, возможно, может привести к другим. Быстрый поиск обнаружил несколько статей, связанных со звездообразной схемой (складирование), описывающих накопление моментальных снимков для таких приложений, как одобрение ипотечных кредитов и производственные процессы. Они не совпадают с моей моделью, но шаблон имеет некоторое сходство, и может быть полезен метод использования представлений в качестве псевдонимов (например, для клиентов, контактов, служб и т. д.). У меня есть некоторое время простоя на праздники, и я могу собрать что-нибудь, чтобы посмотреть, как оно себя ведет. Спасибо! - person djhill8262; 30.11.2010

arrow_upward
0
arrow_downward

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

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

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

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

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

Мне было бы интересно посмотреть, какие результаты вы получите, но я был бы удивлен, если бы было преимущество в производительности.

person joelt    schedule 04.12.2010

arrow_upward
0
arrow_downward

Это можно решить с помощью абстракции и наследования таблиц.

Индивидуальный клиент, клиент-организация, поставщик услуг — все это Стороны, играющие Роли.

Адрес электронной почты, номер телефона, веб-адрес и физический адрес — все это адреса.

person Neil McGuigan    schedule 17.06.2013