1

I have a little problem. I have to design a database model, part of which are three tables, named Room, Client and Employee. The rule is the following: a room can only be used by either one Client OR one Employee. Not both. Therefore, I am not sure if it is better to place into the Room table two optional foreign keys referencing Employee and Client, or place an optional foreign key for the Room in both Client and Employee. With this last solution I would add a CHAR into the Room table indicating if the local is occupied or not. Option A or B ? or any other suggestion? Thanks!

Jeremy L-G
  • 21
  • 3
  • I'd use the two optional foreign keys in the `Room` table together with an XOR-like constraint as shown here: http://stackoverflow.com/questions/11127356/add-a-sql-xor-constraint-between-two-nullable-fks. It models your one-to-one relation better than the other way where multiple `Clients` or `Employees` could reference the same `Room` unless you constrain that in both tables. And then you'd still have to deal with the either-`Client`-or-`Employee`-rule somehow.. – andyp Apr 17 '14 at 21:45

1 Answers1

0

From the rule you specified, you'd need the foreign keys in the room table.

Adding a foreign key to the employee and client tables wouldn't enforce any restriction on a room being used by multiple employees, or multiple clients.

spencer7593
  • 106,611
  • 15
  • 112
  • 140