Дизайн базы данных для связи таблицы с 1 из 2 других таблиц

Question

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

Итак, у меня есть следующий простой набор таблиц:

[users]
- user_id (PK)

[agents]
- agent_id (PK)

[message_threads]
- message_thread_id (PK)

[message]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)

Чего у меня нет, так это отношения из сообщения и человека, который отправил сообщение.

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

Я знаю, у меня есть несколько вариантов, но у всех есть свои минусы.

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

[message]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
- user_id (FK users.user_id, ALLOW NULL)
- agent_id (FK agents.agent_id, ALLOW NULL)

Варианты 2: Это затрудняет получение всех сообщений в одном столбце SELECT.

[message_by_user]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
- user_id (FK users.user_id)

[message_by_agent]
- message_id (PK)
- message_thread_id (FK messages_threads.message_thread_id)
- agent_id (FK agents.agent_id)

Единственное, что я не могу сделать, это объединить пользователей и агентов в одну таблицу. Это установлено в камне.

Answer 1

Похоже, что пользователи и агенты - это каждый подкласс суперкласса, который я назову "персоной". Вы можете иметь еще одну таблицу для лиц с person_id в качестве PK. Затем вы можете заменить user_id на person_id в таблице пользователей. Аналогично замените agent_id на person_id в таблице агентов.

обратите внимание, что в двух таблицах подклассов, пользователи и агенты, person_id выполняет двойную функцию. Это ПК для собственного стола, а также ФК для стола персон. Это обеспечивает непосредственный характер отношений IS-A между пользователями и людьми, а также между агентами и людьми.

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

Это один экземпляр шаблона проектирования, который называется "Наследование таблиц классов", а другой - "Общий первичный ключ". Вы можете получить более подробную информацию об этом, посетив теги с тем же именем здесь, в Stackru, или воспользовавшись поиском в Интернете Мартина Фаулера.