Дизайн базы данных. Сводная таблица с 3 внешними ключами или две сводные таблицы?

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

Клиент перейдет к форме, завершит ввод и выберет место, которое затем отобразит доступные временные интервалы. Я застрял с этими двумя проектами баз данных, и мне интересно, какой из них лучше.

Table 'Customers' -
| id | name |

Table 'Events' -
| id | name | form_fields (json)

Table 'Venues' -
| id |  address | event_id |

Table 'Timeslots' -
| id | datetime | slots | venue_id |

Pivot Table 'Tickets' -
|id | customer_id | timeslot_id | event_id | form_data (json)
Table 'Customers' -
| id | name |

Table 'Events' -
| id | name | form_fields (json)

Table 'Venues' -
| id |  address | event_id |

Table 'Timeslots' -
| id | datetime | slots | venue_id |

Pivot Table 'Tickets' -
| id | customer_id | timeslot_id |

Pivot Table 'EventCustomers' -
| id | customer id | event_id | form_data (json)

Кроме того, я буду хранить HTML-разметку пользовательской формы, созданной администратором, в «form_fields» (json), и пусть клиент заполнит форму и сохранит значения в «form_data» (json).
Также имеет смысл сохранять пользовательскую форму и данные в формате json?

Спасибо.

0

Решение

Чтобы ответить на ваш вопрос (даже если он немного не по теме):

Ни один из вышеперечисленных.

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

Например, можете ли вы иметь запись билетов, которая:

  • Нет записи клиента (customer_id = null)
  • Не имеет временного интервала (timeslot_id = null) — временный интервал связан с местом или местом и временем события.
  • Нет события (event_id = null)

Если вы ответили «нет» на все эти вопросы, мы должны собрать все эти данные за один раз (но не обязательно в одной таблице).

Теперь, на мой взгляд, совершенно ясно, что вы могли / не должны иметь билет, который:

  • не был назначен клиенту
  • не имеет события
  • не имеет временного интервала
  • не имеет места
  • чье количество превышает количество слотов для события (это вы больше всего пропустили)

Поэтому я буду считать, что это наши «основные» ограничения

Проблемы с вашим вторым случаем:

  • Вы можете продать билет клиенту для определенного временного интервала (в месте проведения), но на неизвестное событие. Запись в тикетах и ​​отсутствие записи в таблице EventCustomers

  • Вы также можете зарегистрировать клиента на мероприятие без билета или временного интервала / места проведения. Запись в EventCustomers и отсутствие записи в таблице заявок

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

Проблемы с вашим первым делом:

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

Итак, в первом случае мы хотим это (в идеале):

Pivot Table 'Tickets' -
|id | customer_id | timeslot_id | event_id | form_data (json)
//for this table you would want this compound, unique index
Unique Key ticket (customer_id,timeslot_id,event_id)

Это привело меня к количеству «слотов», поскольку это означало бы, что у клиента может быть только одна запись билетов на событие и временной интервал / место проведения. Это относится к той части, о которой я говорил, что вы по большей части скучали, то есть у вас нет возможности отследить, сколько вы использовали. Сначала вы можете разрешить дубликаты в этой таблице. «Мы можем просто добавить еще несколько билетов, верно?» — вы думаете, и это легко исправить, нет.

Выставка:

Pivot Table 'Tickets' -
|id | customer_id | timeslot_id | event_id | form_data (json)
| 1 |      1      |      1      |     1    | {}
| 2 |      1      |      1      |     1    | {}

Созерцая Exhibit A рассмотрим некоторые основные правила проектирования БД:

В хорошем дизайне БД вы всегда хотите (в идеале)

  • суррогатный первичный ключ, ключ, не имеющий отношения к данным, это id
  • естественный ключ, уникальный ключ, который является частью данных. Примером может быть, если у вас есть поле электронной почты, привлеченное к customerВы можете сделать это уникальным, чтобы избежать добавления дублирующих клиентов. Это часть данных, которая по своей природе уникальна и является частью данных.

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

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

Почему уникальность так важна?

Как только вы разрешите дублирование с помощью сводных таблиц, вы столкнетесь с несколькими проблемами (особенно если у вас есть дополнительные данные, такие как form_data):

  • Как отличить эти записи?
  • Какой из дубликатов является официальной копией, которая отвечает.
  • Как вы синхронизируете эти дополнительные данные, если вам нужно изменить form_data, в какую запись вы вносите изменения. Только одна? Который из? И то и другое? как вы поддерживаете синхронизацию всех дубликатов.
  • Что делать, если случайно введен дубликат, как вы узнаете, что он был случайно? Откуда вы знаете, что это настоящий дубликат или настоящий дубликат, а не действительная запись?
  • Даже если вы знали, что это был случайный дубликат, как вы решаете, какой из дубликатов должен быть удален, это относится к авторитетной записи.

В короткие сроки это действительно становится беспорядком.

в заключение (что я бы предложил)

Table 'customer' -
| id | name |

Table 'event' -
| id | name | form_fields (json)

Table 'venue' -
| id |  address | slots |

Table 'show' -
| id | datetime | venue_id | event_id |

Table 'purchase' -
| id | show_id | customer_id | slots | created |

Table 'ticket' ( customers_shows )
| id | purchase_id | guid |

Я изменил довольно много вещей (вы можете использовать некоторые или все эти изменения):

Я изменил имена во множественном числе на единственное. Я использую множественное число только тогда, когда я делаю сводные таблицы, у которых нет дополнительных данных, такое имя будет venues_events, Это связано с тем, что запись от клиента — это единое целое, и мне не нужно делать какие-либо объединения для получения полезных данных. Запись из нашего гипотетического venues_events будет включать в себя 2 объекта, поэтому я бы сразу понял, что мне нужно выполнить объединение независимо от того, что, кроме внешних ключей, нет никаких других данных.

Теперь в случае showВы можете заметить, что это, по сути, сводная таблица. Так почему я не назвал это venues_events как я перечислил выше. Причина в том, что у нас есть datetime столбец там, что я имею в виду под «вспомогательными» данными. Так что в этом случае я мог бы получить данные только из show если бы я просто хотел datetime и мне не нужно объединение, чтобы сделать это. Таким образом, его можно рассматривать как единое целое, которое имеет несколько отношений «многие к одному». («Многие ко многим» — это «многие к одному» и «один ко многим», поэтому нам нужны сводные таблицы).

Буквы и пробелы. Я бы предложил использовать все строчные и без пробелов. MySql чувствителен к регистру и не очень удобен с пробелами. Просто с точки зрения того, что не нужно запоминать, проще ли мы назвать это venuesEvents или же VenuesEvents или же Venuesevents и т. д. Последовательность в соглашении об именовании имеет первостепенное значение в хорошем дизайне БД.

Вышесказанное в значительной степени основано на мнениях, это мой ответ, так что это мое мнение. Смирись с этим.

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

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

Приложение B (используя вашу структуру):

Table 'event' -
| id |    name   | form_fields (json) |
| 1  | "Event A" | "{}"               |
| 2  | "Event B" | "{}"               |

Table 'Venues' -
| id |    address   | event_id |
|  1 | "123 ABC SE" |    1     |
|  2 | "123 AB SE"  |    2     | //address entered wrong as AB instead ABC

Table 'Timeslots' -
| id |        datetime       | slots | venue_id |
| 1  | "2018-01-27 04:41:23" | 200   |    1     |
| 2  | "2018-01-27 04:41:23" | 200   |    2     |

В представленной выше выставке мы сразу видим, что нам нужно продублировать адрес, чтобы создать более одного события в данном месте. Таким образом, если адрес был введен неверно, он может быть правильным в некоторых местах и ​​неверным в других. Это может быть реальной проблемой, так как программно, как вы знаете, что AB должен был быть ABC когда идентификатор места и идентификатор события отличаются для этой записи. В основном, как вы отличаете эти записи во время выполнения? Вы обнаружите, что это очень сложно сделать. Основная проблема в том, что у вас много данных в VeneuesВы пытаетесь сделать с этим многое, и отношения не соответствуют ограничениям данных.

Это даже не самое страшное, так как возникает еще одна проблема, потому что теперь, когда venue_id отличается мы можем испортить наш Timeslots стол и есть 2 записи там в то же время для того же места. Тогда, потому что slots привязаны к этой таблице, мы также можем испортить вещи вниз по течению, такие как продажа большего количества билетов, чем мы должны для того времени и места. Все только начинает разрушаться.

Даже подсчет количества шоу в данном месте проведения становится реальной проблемой, этот «недостаток» присутствует в обеих моделях данных, которые вы представили.

Те же данные в моей модели

#with Unique compound Key datetime_venue_id( show.datetime, show.venue_id)

Table 'event' -
| id |    name   | form_fields (json) |
| 1  | "Event A" | "{}"               |
#| 2  | "Event B" | "{}"               |

Table 'venue' -
| id |   address    |  slots    |
|  1 | "123 ABC SE" |    200    |

Table 'show' -
| id | datetime              | venue_id | event_id |
| 1  | "2018-01-27 04:41:23" |   1      |    1     |
#| 2  | "2018-01-27 04:41:23" |   1      |    2     |

Как видите, у вас больше нет дублирующего адреса. И хотя, похоже, вы могли бы участвовать в 2 шоу для того же venue в то же время, это только потому, что у нас нет составного уникального ключа, который включает datetime а также venue_id а.к.а. Unique Key datetime_venue_id( datetime, venue_id), Если вы попытаетесь вставить эти данные с этим ограничением, MySql может взорвать вас. И если бы вы включили обе вставки (событие и шоу) в одну и ту же «транзакцию» (что я и сделал бы, в движке innodb), все это не сработало бы и не откатилось, и ни событие, ни шоу не были бы вставлены.

Теперь вы можете попытаться возразить, что у вас может быть то же ограничение Уникальности для Приложения B, но, поскольку ID Места здесь отличается, вы ошибаетесь.

Тем не мение, show наша новая главная сводная таблица с внешними ключами от event а также venue а затем дополнительные данные datetime,

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

  • Что и где это событие (путем присоединения к таблице событий)
  • когда событие (метка времени)
  • сколько слотов доступно для мероприятия (путем присоединения к месту проведения стола)

Это сосредотачивает все вокруг show запись. Мы можем построить «шоу» независимо от клиента или билетов. Потому что на самом деле заказчик не участвует в шоу, и включение его в модель данных в скором времени (или в конце, в зависимости от того, как вы на это смотрите) приводит в замешательство.

Экспонат С

#in your first case
Pivot Table 'Tickets' -
|id | customer_id | timeslot_id | event_id | form_data (json)

#in your second case
Pivot Table 'Tickets' -
| id | customer_id | timeslot_id |

Pivot Table 'EventCustomers' -
| id | customer id | event_id | form_data (json)

Как я уже говорил выше, вы не можете поставить то, что я называю show что, где и когда вместе без идентификатора клиента (в любой из ваших моделей данных). Когда вы создадите свое приложение для этого позже, это станет огромной проблемой. Это может быть непреодолимым во время выполнения. По сути, вам нужны все эти данные, собранные и ожидающие на customer_id. В обеих ваших моделях это не так, и есть данные, к которым у вас может быть нелегкий доступ. Например, для первого случая (старой структуры), как вы узнали бы, что timeslot_id=20 А ТАКЖЕ event_id=32 плюс клиент равен действующему билету? Там нет прямой связи между timeslot а также event за пределами сводной таблицы, которая содержит клиента. timeslot_id=20 может быть действительным для любого события, и у вас нет возможности узнать это.

Гораздо проще сказать show=32 и проверьте, сколько слотов осталось, а затем просто сделайте запись покупки. Все готово и ждет этого.

Стол покупной
Я также добавил покупку или таблицу заказов, даже если «показы» свободны, эта таблица дает нам отличную полезность. Это также сводная таблица, но она содержит некоторые вспомогательные данные, как это делает show. ( slots а также created ).

Этот стол

  • мы привязываем стол клиента к столу показа здесь
  • у нас есть поле «создан», так что вы будете знать, когда эта запись была создана, когда билеты были приобретены
  • у нас также есть несколько слотов, которые клиент будет использовать, мы можем сделать общую сумму slots сгруппированы по show_id чтобы увидеть, сколько слотов мы «продали». С одним присоединением от show в venue мы можем узнать, сколько всего слотов имеет это «шоу» с тем же целочисленным ключом (show.id), который мы использовали выше для агрегирования. Тогда было бы несложно сравнить их, если вы захотите придумать, вы можете сделать все это в одном запросе.

Настольный билет
Теперь вам может понадобиться, а может и не понадобиться этот стол. Это имеет отношение один к одному к покупке стола. Так что order может иметь много tickets, Записи здесь будут генерироваться при совершении покупки, число зависит от того, что находится в слотах. Основное использование этой таблицы — просто предоставить уникальную запись для каждого отдельного билета. Для этого у меня есть guid столбец, который может быть просто уникальным хешем. По сути, это даст вам возможность отслеживать отдельные билеты, у меня нет достаточной информации, чтобы знать, как это будет работать в вашем случае. Вы даже можете заменить эту таблицу данными JSON, если поиск по ней не является проблемой, и это упростит ее обслуживание в случае возврата некоторых билетов. Но, как я намекнул, это очень зависит от вашего конкретного случая использования.

Несколько кратких примеров SQL

Присоединиться ко всему (просто чтобы показать отношения):

SELECT
{some fields}
FROM
ticket AS t
JOIN
puchase AS p ON t.purchase_id = p.id
JOIN
customer AS c ON p.customer_id = c.id
JOIN
show AS s ON p.show_id = s.id
JOIN
venue AS v ON s.venue_id = s.id
JOIN
event AS e ON s.event_id = e.id

Подсчет используемых слотов для шоу:

SELECT
SUM(slots) AS used_slots
FROM
puchase
WHERE
show_id = :show_id
GROUP BY show_id

Получить доступные слоты для шоу:

SELECT
v.name,
v.slots
FROM
venue AS v
JOIN
show AS s ON s.venue_id = v.id
WHERE
v.show_id = :show_id
# or you could do s.id = :show_id

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

-нота- Название таблицы event может быть зарезервированным словом в MySql, я не уверен, что он будет работать как имя таблицы. Некоторые зарезервированные слова все еще работают в некоторых частях запроса в зависимости от контекста, в котором он используется. Даже если это так, я уверен, что вы можете придумать обходной путь. По совпадению, именно поэтому я назвал purchase что вместо order как «заказ» является зарезервированным словом. (Я просто думаю о событии)

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

1

Другие решения

Других решений пока нет …

По вопросам рекламы ammmcru@yandex.ru
Adblock
detector