[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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.
0
ZekeLA
Asked:
ZekeLA
  • 3
  • 3
  • 2
2 Solutions
 
JestersGrindCommented:
Probably the best solution is to remove WindowsTypeID from this table because there is a 0  to many relationship.  Actually it's probably a many to many relationship because the combination of ContractID, EquipmentID and RoomID can have many WindowTypeIDs associated with it and I assume WindowTypeID can be used across many different combinations of the above columns.  That being said,  put a table between this table and the WindowsType table.  The table will have the following columns, ContractID, EquipmentID, RoomID and WindowsTypeID.  This is called a linking table.  

Greg


0
 
ZekeLAAuthor Commented:
Thanks Greg. I played with this some since posting and came up with that as part of the solution. What I ended up with was:

ContractAssignment - unique index on all four columns
 - ContractID not null,
 - EquipmentID not null,
 - RoomID not null,
 - WindowTypeListID null

WindowsTypeList - primary key on WindowsTypeListID
 * WindowTypeListID not null

WindowsTypeListDetail - composite primary key on WindowsTypeListID and WindowsTypeID
 * WindowTypeListID not null,
 * WindowTypeID not null

The WindowsTypeList table is needed so I can enforce the relationship between ContractAssignment and WindowsTypeListDetail. I can't join directly since WindowsTypeListDetail can have multiple records for a given WindowTypeListID.

Thanks.
0
 
JestersGrindCommented:
I don't think the WindowsTypeList is necessary.  Is there a ContractAssignmentID as the primary key of the ContractAssignment table?  If so, that should be used in place of the WindowTypeListID in the WindowsTypeListDetail table.  I think this is the structure that you are looking for.

ContractAssignment
ContractAssignmentID - PK
ContractID not null
EquipmentID not null
RoomID not null

ContractAssignmentWindowsType (or whatever you want to call this linking table)
ContractAssignmentID - PK
WindowTypeID - PK

WindowType
WindowTypeID - PK

You are essential doing the same thing now, but with an extra unnecessary table, WindowsTypeList.

Greg


0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
ZekeLAAuthor Commented:
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.
0
 
jchevaliCommented:
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.
0
 
jchevaliCommented:
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.
0
 
ZekeLAAuthor Commented:
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.
0
 
jchevaliCommented:
That's all right, thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now