Design conditional relationship

Question

I need help with designing my database tables.

Employee
Id
EmployeeTypeId

EmployeeType
Id
Name

Car
Id
EmployeeId

How do I enforce that only one type of employee (driver) can be a foreign key in the Car table or should I redesign the tables?

Answer 1

I consider it a good idea to forearm the database such that implausible data cannot be entered. To enforce this here, however, is a bit tricky...

Solution 1:

Add EmployeeTypeId to the Car table. Then make (EmployeeId, EmployeeTypeId) a foreign key to the Employee table (where you might have to create a uniqe constraint on the two fields, in order to be able to use them for a foreign key reference). Then add a constraint on Car.EmployeeTypeId to ensure it's a driver. I know this looks redundant, but it really is no problem, because you cannot assign an Employee another EmployeeType here, so consistency is still guaranteed. I admit this approach is a bit clumsy, though.

Solution 2:

Use a before-insert tigger on the Car table, look up the employee and make sure it is a driver, else throw an exception. This is a better solution in my opinion, alone for its simplicity. You could then add a column to table Car holding a unique name for the types that you use, e.g. UniqueName = 'DRIVER', so you don't have to use the ID as a magic number. You see, normally one EmployeeType is a s good as the other in a database. If you want to build special logic on a certain entry, you need a handle for this. The unique name is one way to do this, a flag IsDriver = TRUE/FALSE would be another.