Complex Constraints on fields in SQL Server 2008 database

I have the following (simplified) structure that allows me to track equipment that has been assigned to a cell. Since equipment can only be in the cell once in the table, I've created a constraint that says that idEquipment and idCell must be unique in the table.

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

This constraint ensures that I never add the same piece of equipment to a cell twice.

So now I've been tasked with keeping history information. I need to be able to pull up a workorder, see its date, and then find what equipment was used on that work order. A solution is to add date information to the table like this:

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

Now my constraint from above is broken. idCell/idEquipment are no longer unique since equipment can be removed and re-added to the cell. Now I have some tricky date issues to contend with. To ensure data integrity the following must be true for changes to the database:

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

The Check constraints don't have the power to pull this off and the unique index constraints can't do it either. BTW a Check Constraint can be created to ensure that DateAdded < DateRemoved (along with other NULL/NOT NULL constraint relationships)

Do I need to do enforce the开发者_StackOverflowse relationships from code, a transaction, a different model?

Perhaps there is a design pattern that I don't know about that can help with storing such historical data?

Since I don't know what equipment and cell mean to you, I could be way off base here.

But it seems to me that the important information is in the question "What equipment was used in work order number ?" Dates don't really give you that information. But this (air code) structure might.

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)
);

That makes it dead simple to get the equipment used on a given work order. To get the equipment used on a given date, join CellEquipment, work_order_equipment, and workorders, and look at the dates in the workorders table.