Противоречивые желания в дизайне базы данных с полями двух похожих функций

Question

Ладно, я сейчас готовлю стол для "Коробок".

Теперь элемент Box, в зависимости от того, для чего он используется / статус элемента, может в конечном итоге быть связан с блоком "Shipping" или "Returns".

Элемент Коробки может быть неисправен: если это так, в строке Элемента Коробки будет установлен флаг (IsDefective), а Элемент Коробки будет помещен в коробку "Возвращает" (с другими предметами, которые будут возвращены этому поставщику). В противном случае, элемент Коробки в конечном итоге будет помещен в коробку "Доставка" (вместе с другими отправляемыми элементами). (Обратите внимание, что у ящиков "Доставка" и "Возврат" есть свои собственные таблицы: для всех ящиков не существует единой общей таблицы... хотя, возможно, я должен рассмотреть возможность сделать это, если это возможно, в качестве третьей возможности?)

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

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

BoxItemID Описание IsDefective ShippingBoxID ReturnBoxID и т. Д...

Это сделало бы отношения ясными, но это кажется расточительным (так как только одно из отношений будет использоваться в любое время). Тогда я подумал, что мог бы иметь только одно поле для BoxID и определить, на какой BoxID он ссылается (идентификатор доставки или возврата), на основе поля IsDefective:

BoxItemID Описание IsDefective BoxID и т. Д...

Это кажется менее расточительным, но не подходит мне. Отношение не очевидно.

Итак, я поставил это перед вами, гуру базы данных Stackru. Что бы вы сделали в такой ситуации?

РЕДАКТИРОВАТЬ: Спасибо всем за ваш вклад! Это дает мне много думать. Во-первых, я собираюсь использовать ORM в следующий раз, когда начну такой проект. =) Для двоих, так как я не сейчас, я укушу четыре байта и использую два поля.

Спасибо всем еще раз!

Answer 1

Я с Психотическим Ядом и Маттлантом.

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

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

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

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

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

Answer 2

Я хотел бы рассмотреть возможность создания единой таблицы для блоков, а тип блока будет столбцом таблицы блоков. Это упростит отношения и упростит возможность запроса типа блока. Таким образом, элемент box имеет только один внешний ключ для boxId.

Answer 3

Я бы использовал то, что Hibernate называет Table-per-subclass, поэтому моя БД получит 3 таблицы для Boxes: Box, ShippingBox, а также ReturnBox, ФК в BoxItem будет указывать на Box,

Answer 4

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

Как насчет этого?

BoxItem:
BoxItemID, Description, IsDefective

Box:
BoxID, Description

BoxItemMap:
BoxID, BoxItemID, BoxItemType

Тогда у BoxItemType может быть перечисление или целое число, в котором вы определяете константы в вашем приложении как "Возврат" или "Доставка" в качестве типа ящика.

Answer 5

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

В основном у вас есть базовая таблица с названием box. Тогда у вас есть две другие таблицы, коробка для доставки и коробка для возврата. Эти два добавляют любые дополнительные поля, которые являются особенными для них. они связаны с ящиком с базовой таблицей 1:1 fk.Boz и имеют общие поля для всех типов ящиков.

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

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

Почти все ORM поддерживают эту стратегию.

Answer 6

Я бы выбрал только одну таблицу BoxItems с IsDefective, ShippingBoxID, полями, связанными с коробкой доставки, ReturnBoxID и полями, относящимися к коробке возврата. Некоторые поля всегда будут NULL для каждой записи.

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

Answer 7

Я бы, наверное, пошел с:

BoxTable:
box_id, box_descrip, box_status_id ...
     1, Lovely Box, 1
     2, Borked box, 2
     3, Ugly Box, 3
     4, Flammable Box, 4

       BoxStatus:
       box_status_id, box_status_name, box_type_id, ....
                   1,Shippable, 1
                   2,Return, 2
                   3,Ugly, 2
                   4,Dangerous,3

                BoxType:
                box_type_id, box_type_name, ...
                          1, Shipping box, ...
                          2, Return box, ....
                          3, Hazmat box, ...

Таким образом, "Состояние ящика" определяет тип ящика, и он гибок, если позже вам понадобится расширить на несколько уровней статуса или типов ящиков.