Сложные ограничения на поля в базе данных SQL Server 2008

У меня есть следующая (упрощенная) структура, которая позволяет мне отслеживать оборудование, назначенное ячейке. Поскольку оборудование может быть в ячейке только один раз в таблице, я создал ограничение, которое говорит, что idEquipment и idCell должны быть уникальными в таблице.

CREATE TABLE [dbo].[CellEquipment](
    [idEquipment] [int] NOT NULL,
    [idCell] [int] NULL,
 CONSTRAINT [PK_CellEquipment] UNIQUE NONCLUSTERED 
(
    [idEquipment] ASC,
    [idCell] ASC
)

Это ограничение гарантирует, что я никогда не добавлю одно и то же оборудование в ячейку дважды.

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

CREATE TABLE [dbo].[CellEquipment](
    [idEquipment] [int] NOT NULL,
    [idCell] [int] NULL,
    [DateAdded] [datetime] NULL,
    [DateRemoved] [datetime] NULL,
)

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

idCell/idEquipment are unique (like before)   OR
idCell/idEquipment's new DateAdded doesn't fall between a DateAdded/Removed OR
idCell/idEquipment's new DateRemoved doesn't fall between a DateAdd/Removed or
idCell/idEquipment's doesn't have a record with DateRemoved=NULL

Ограничения Check не могут этого сделать, и ограничения уникального индекса тоже не могут этого сделать. Кстати, контрольное ограничение может быть создано, чтобы гарантировать, что DateAdded < DateRemoved (наряду с другими отношениями ограничений NULL/NOT NULL)

Нужно ли мне навязывать эти отношения из кода, транзакции, другой модели?

Возможно, есть шаблон проектирования, о котором я не знаю, который может помочь с хранением таких исторических данных?


sql-server-2008 unique-constraint
person Hucker    schedule 22.04.2011    source источник
comment
Вы сказали: «Это ограничение гарантирует, что я никогда не добавлю одно и то же оборудование в ячейку дважды. Но это позволяет добавлять одно и то же оборудование в несколько ячеек. Это то, что вы намеревались?   -  person Mike Sherrill 'Cat Recall'    schedule 22.04.2011
comment
К сожалению, есть случаи, когда это разрешено. Например, есть печь (очень большая), которая обслуживает несколько камер. Поскольку печь «откалибрована», ее необходимо отслеживать.   -  person Hucker    schedule 22.04.2011
comment
Я бы не сказал, что это печально. Я бы сказал, что так работает бизнес.   -  person Mike Sherrill 'Cat Recall'    schedule 22.04.2011

Ответы (1)


arrow_upward
1
arrow_downward

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

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

create table work_order_equpiment (
  idEquipment integer not null,
  idCell integer not null,
  foreign key (idEquipment, idCell) references CellEquipment (idEquipment, idCell),
  work_order_number integer not null references workorders (work_order_number),
  primary key (idEquipment, idCell, work_order_number)
);

Это делает очень простым получение оборудования, используемого для данного рабочего задания. Чтобы получить оборудование, используемое на определенную дату, присоедините CellEquipment, work_order_equipment и заказы на работу и посмотрите даты в таблице заказов на работу.

person Mike Sherrill 'Cat Recall'    schedule 22.04.2011
comment
Я думаю, что это добавляет избыточности базе данных, хотя вы, вероятно, не видите этого в том, что я сказал. В настоящее время мы регистрируем каждый соответствующий шаг в производстве, кто (сотрудник), что (рабочий заказ), как (рабочий этап), когда (отметка времени), где (рабочая ячейка). Если таблица ячейки/оборудования может быть осведомлена о дате, я могу найти данные, добавив только 2 даты к каждому элементу оборудования. Если я сделаю то, что вы предлагаете, у меня будут десятки или сотни тысяч новых записей (много шагов * много машин). - person Hucker; 22.04.2011
comment
То, что вы предлагаете, имеет преимущество только в отслеживании текущего состояния рабочей ячейки. Мое предлагаемое решение отслеживает полную историю рабочей ячейки, что не является обязательным требованием (и, вероятно, не будет). - person Hucker; 22.04.2011
comment
Если таблица ячейки/оборудования может быть осведомлена о дате, я могу найти данные, добавив только 2 даты к каждому элементу оборудования. Я так не думаю. Я думаю, вам нужно будет добавить строку, включая эти две даты, для каждого заказа на работу. И я не вижу никакого очевидного способа для базы данных сказать, что этот элемент оборудования использовался в этом рабочем задании. Он может только сказать, что это оборудование использовалось в эту дату. Не то же самое в большинстве случаев, но это может быть то же самое в вашем случае. - person Mike Sherrill 'Cat Recall'; 22.04.2011
comment
У меня есть таблица со столбцами WO/date. У меня есть вторая таблица со столбцами ячейки/даты/оборудования. Я могу присоединиться к этим двоим и сказать, какое оборудование было в камере на момент прохождения ЗН. При условии, что данные выводятся, однако наличие линейного технического штрих-кода на каждой единице оборудования в каждом рабочем задании не будет летать, что приведет к тысячам транзакций. Все это говорит о том, что моя цель в моем OQ заключалась в том, чтобы поддерживать целостность данных, и теперь вы заставляете меня думать о структурных решениях. - person Hucker; 22.04.2011
comment
Структура — ваш друг. (Иногда это ваш единственный друг.) - person Mike Sherrill 'Cat Recall'; 22.04.2011