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

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

Если у меня есть таблица пользователя со следующими свойствами:

  • user_id
  • электронная почта
  • пароль

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

  • account_id
  • account_description
  • account_balance
  • user_id

И чтобы перейти к следующему шагу, я создаю таблицу транзакций:

  • TRANSACTION_ID
  • transaction_description
  • is_withdrawal
  • account_id//Учетная запись, к которой относится эта транзакция.
  • user_id//Пользователь, которому принадлежит эта транзакция.

Имеет ли user_id в таблице транзакций хороший вариант? Это сделало бы запрос более чистым, если бы я хотел получить все транзакции для каждого пользователя, например:

SELECT * FROM transactions
JOIN users ON users.user_id = transactions.user_id

Или, я мог бы просто вернуться к таблице пользователей из таблицы учетных записей

SELECT * FROM transactions
JOIN accounts ON accounts.account_id = transactions.account_id
JOIN users ON users.user_id = accounts.user_id

Я знаю, что первый запрос намного чище, но это лучший способ пойти?

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

5 ответов

Посмотрим на него под другим углом. С чего начинается запрос или серия запросов? Если у вас есть информация о клиенте, вы можете получить информацию об учетной записи, а затем информацию о транзакции или просто транзакцию на одного клиента. Вам нужны все три таблицы для значимой информации. Если у вас есть учетная информация, вы можете получить информацию о транзакции и указатель на клиента. Но чтобы получить информацию о клиенте, вам нужно зайти в таблицу клиентов, так что вам все равно нужны все три таблицы. Если у вас есть информация о транзакциях, вы можете получить информацию об учетной записи, но это бессмысленно без информации о клиенте, или вы можете получить информацию о клиенте без информации об учетной записи, но транзакции за клиента бесполезны без данных учетной записи.

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

Наличие клиента FK в таблице транзакций может предоставить вам способ сделать "чистый" запрос, но результат этого запроса имеет сомнительную полезность. Так что вы ничего не получили. Я работал над написанием сканеров Anti-Money Laundering (AML) для международной компании по кредитным картам, поэтому я не гипотетичен. В любом случае вам всегда понадобятся все три таблицы.

Btw, тот факт, что есть FKs, в первую очередь говорит мне, что вопрос касается среды OLTP. Для среды OLAP (хранилища данных) не нужны FK или любые другие проверки целостности данных, поскольку данные хранилища являются статическими. Данные происходят из среды OLTP, где проверки целостности данных уже сделаны. Итак, вы можете денормализовать свое содержание. Поэтому давайте не будем давать ответы, применимые к среде OLAP, к вопросу, касающемуся среды OLTP.


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

Например, что сказать, что у каждой учетной записи будет только один пользователь? В вашем дизайне таблиц это все, что вы могли бы получить, и это то, что я считаю подозрительным сразу. У учетных записей в моей системе могут быть тысячи пользователей. Итак, это первое, что я задаю вашей модели. Вы на самом деле считали, что между ними должно быть 1-1 или 1-много? Или вы просто сделали asssumpltion? Datamodels НЕ легко настроить после того, как у вас есть миллионы записей, вам нужно гораздо больше планировать будущее в дизайне базы данных и гораздо больше думать о потребностях данных с течением времени, чем в дизайне приложений.

Но предположим, что у вас есть отношения один-один. И через три месяца после того, как вы поедете вживую, вы получите новую учетную запись, где им нужно иметь 3 пользователя. Теперь вы должны помнить все места, которые вы denornmalized, чтобы правильно исправить данные. Это может создать много путаницы, так как неизбежно вы забудете некоторых из них.

Кроме того, даже если вам никогда не понадобится перейти к более надежной модели, как вы собираетесь поддерживать это, если user_id изменится, поскольку они будут делать часто. Теперь, чтобы сохранить целостность данных, вам нужно иметь триггер для сохранения данных по мере их изменения. Хуже того, если данные могут быть изменены из любой таблицы, вы можете получить противоречивые изменения. Как вы справляетесь с ними?

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

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


Вы не должны использовать два внешних ключа в одной таблице. Это не хороший дизайн базы данных.

Пользователь совершает транзакции через учетную запись. Вот как это логически сделано; поэтому именно так должна быть разработана БД.

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

Пустое пространство не является необходимым и является плохим дизайном базы данных.


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

ИЗМЕНИТЬ

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


На мой взгляд, если у вас есть простые отношения Many-to-Many, просто используйте два первичных ключа и все.

В противном случае, если у вас есть отношение Many-to-Many с дополнительными столбцами, используйте один первичный ключ и два внешних ключа. Легче управлять этой таблицей как единым Entity, как это делает Doctrine. В общем случае простые отношения "многие-ко-многим" встречаются редко, и они полезны только для связывания двух таблиц.

licensed under cc by-sa 3.0 with attribution.