Multiple reference in SQL

Question

Hi! I'm working on a db but i'm kinda new to this so i've bumped into a problem today. I've got some tables: OFFICE, ROOM, EMPLOYEE and DOCUMENT. Document must specify the sender, which can be a single employee, an entire room or an entire office so it must have a reference to the primary keys of those tables. Should I do a "parallel" table for handle it (for example i've done one for handle the multiple recipients documents) or there is another way? Thank you

Answer 1

I would be inclined to have a foreign key to each of the three tables with a check constraint that ensures only one will have a value. In this way, you can still use standard referential integrity. Btw, this assumes that the business rule is that each document must have one and only one sender.

Create Table Document
(
    SenderEmployeeId ...
    , SenderRoomId ...
    , SenderOfficeId....
    , Constraint CK_Document_SingleSender Check ( Case 
                                                    When SenderEmployeeId Is Not Null And SenderRoomId Is Null And SenderOfficeId Is Null Then 1
                                                    When SenderRoomId Is Not Null And SenderEmployeeId Is Null And SenderOfficeId Is Null Then 1
                                                    When SenderOfficeId Is Not Null And SenderEmployeeId Is Null And  SenderRoomId Is Null Then 1
                                                    Else 0
                                                    End = 1)
)

Answer 2

You are trying to create conditional foreign keys which you can't do in SQL Server. I think creating a table to hold the Document and the Sender is a good idea but you won't be able to create the foreign keys. You could implement a Check Constraint though to control the data.

Answer 3

I would implement this with parallel tables as you mentioned. The tables would be as such:

OfficeDocuments (OfficeID, DocumentID)

RoomDocuments (RoomID, DocumentID)

EmployeeDocuments (EmployeeID, DocumentID)

You can design more flexible queries with a design like this, such as joinin against the Room and Office tables to acquire a list of employees.

The method you choose should depend on how flexible you need to be. If you are designing only a query or two to go along with this table, it may make sense to have a denormalized table instead (using check constraints and multiple attributes for each type of table which can be a sender).