Table design to enforce interesting constraint logic
I have a situation that I'm not sure I can enforce via pure table design. But I'm interested if someone can solve this for me. If not, we'll just use application logic to fully enforce it. I'm going to use two examples. The first example is simpler and I will show how I solved it. The second example is the one that I actually need to enforce.
Example 1:
Contracts are assigned to unique combinations of Equipment, Rooms and Window Type. Equipment and Rooms are always required but Window may not be.
For this, I would set up a table with a unique index on the following fields:
- ContractID not null,
- EquipmentID not null,
- RoomID not null,
- WindowTypeID null
I can't use a primary key since WindowID is nullable.
Example 2:
Same as above except that there may be multiple Window Types for a given contract. In pseudo-code, I want the following:
- ContractID not null,
- EquipmentID not null,
- RoomID not null,
- WindowTypeList null
where WindowTypeList represents a unique combination of 1 or more WindowTypes.
What I haven't figured out is how to model WindowTypeList so the table above enforces the unique combination of Equipment, Rooms and (1 or more) Window Types.
BTW, I'm doing this with SQL Server 2005.
Thanks.
Example 1:
Contracts are assigned to unique combinations of Equipment, Rooms and Window Type. Equipment and Rooms are always required but Window may not be.
For this, I would set up a table with a unique index on the following fields:
- ContractID not null,
- EquipmentID not null,
- RoomID not null,
- WindowTypeID null
I can't use a primary key since WindowID is nullable.
Example 2:
Same as above except that there may be multiple Window Types for a given contract. In pseudo-code, I want the following:
- ContractID not null,
- EquipmentID not null,
- RoomID not null,
- WindowTypeList null
where WindowTypeList represents a unique combination of 1 or more WindowTypes.
What I haven't figured out is how to model WindowTypeList so the table above enforces the unique combination of Equipment, Rooms and (1 or more) Window Types.
BTW, I'm doing this with SQL Server 2005.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was going to say the table is unnecessary since both of our solutions use the same number of tables and the same number of fields for those tables. But I then realized your solution doesn't enforce the situation I want. For example, you could only have 1 entry in ContractAssignment for ContactID = 1, EquipmentID = 1 and RoomID = 1. In fact though, it should support alll of the following entries:
ContactID = 1, EquipmentID = 1, RoomID = 1 with WindowTypeList null
ContactID = 1, EquipmentID = 1, RoomID = 1 with WindowTypeList = 1
(where WindowTypeList consists of WindowTypeID =1)
ContactID = 1, EquipmentID = 1, RoomID = 1 with WindowTypeList = 2
(where WindowTypeList consists of WindowTypeID =1 and WindowTypeID = 2)
But your alternative has greatly helped me to understand my design being what I want. Thanks.
ContactID = 1, EquipmentID = 1, RoomID = 1 with WindowTypeList null
ContactID = 1, EquipmentID = 1, RoomID = 1 with WindowTypeList = 1
(where WindowTypeList consists of WindowTypeID =1)
ContactID = 1, EquipmentID = 1, RoomID = 1 with WindowTypeList = 2
(where WindowTypeList consists of WindowTypeID =1 and WindowTypeID = 2)
But your alternative has greatly helped me to understand my design being what I want. Thanks.
If the Window Types you're working with are limited in number and you can afford to change their keys to be multiples of 2 then you could use a bitmap column in WindowTypeList to represent any allowed combination of Window Types.
The three entries you gave us above would be represented as:
ContactID = 1, EquipmentID = 1, RoomID = 1, WindowTypeList = 0x00000000
ContactID = 1, EquipmentID = 1, RoomID = 1, WindowTypeList = 0x00000001
ContactID = 1, EquipmentID = 1, RoomID = 1, WindowTypeList = 0x00000003
However, be aware that the bitwise and- and or- operators in Sql Server only work with integer field types, not binary or varbinary. The ones I've typed above use a binary(4) just for clarity, however, if you store them as binary you'll probably have to be converting them back and forth each time you need to set or clear the individual Window Type bits -- probably best to keep them as integer types then.
The three entries you gave us above would be represented as:
ContactID = 1, EquipmentID = 1, RoomID = 1, WindowTypeList = 0x00000000
ContactID = 1, EquipmentID = 1, RoomID = 1, WindowTypeList = 0x00000001
ContactID = 1, EquipmentID = 1, RoomID = 1, WindowTypeList = 0x00000003
However, be aware that the bitwise and- and or- operators in Sql Server only work with integer field types, not binary or varbinary. The ones I've typed above use a binary(4) just for clarity, however, if you store them as binary you'll probably have to be converting them back and forth each time you need to set or clear the individual Window Type bits -- probably best to keep them as integer types then.
I'm providing a possible solution to the original request which I think is better because it uses a single column as originally requested and does not involve adding additional tables (# 26155813) therefore I'd like the author to consider my answer and decide if the points should be split in some other way. Thanks.
ASKER
I don't want to use a bit field. For confidentiality, I've changed the field names to protect the innocent. Even the the real WindowTypeID will be, for practical purposes, less than a thousand, wouldn't using a bit field mean I can't enforce the referential integrity between WindowTypeList values and WindowTypeID. (Note that WindowTypeID will contain many holes in the value; we can't asumme all values from 1 to n will be present.)
Also, in reality, this model will be shared by 2 (or possibly) three different applications, at least one of which uses an Access Database and another uses Microsoft SQL which further complicates the use of a bit valued field. It is an elegant alternative but not a good solution in our situation.
Thank you anyway.
Also, in reality, this model will be shared by 2 (or possibly) three different applications, at least one of which uses an Access Database and another uses Microsoft SQL which further complicates the use of a bit valued field. It is an elegant alternative but not a good solution in our situation.
Thank you anyway.
That's all right, thanks.
ContractAssignment
ContractAssignmentID - PK
ContractID not null
EquipmentID not null
RoomID not null
ContractAssignmentWindowsT
ContractAssignmentID - PK
WindowTypeID - PK
WindowType
WindowTypeID - PK
You are essential doing the same thing now, but with an extra unnecessary table, WindowsTypeList.
Greg