We’ll say that Agents get a ‘terminal code’ (to access their terminals); Managers don’t need a terminal code, but they do get to use the staff car park, so we’ll need to store their car registration — but only for Managers, Agents aren’t allowed to park at work.

which of course works, but now our mere Agent gets to park for free. (How disastrous!) We can fix this — we’ll put the NULL back in the CarReg field, and add a CHECK constraint to stop it happening again:

UPDATE dbo.Employee
SET CarReg = NULL
WHERE EmployeeID = 1
GO
ALTER TABLE dbo.Employee
ADD CONSTRAINT CK_Employee_CheckFields
CHECK
(
CASE
WHEN EmployeeTypeID = 1
THEN CASE WHEN TerminalCode IS NOT NULL AND CarReg IS NULL THEN 1 ELSE 0 END
WHEN EmployeeTypeID = 2
THEN CASE WHEN TerminalCode IS NULL AND CarReg IS NOT NULL THEN 1 ELSE 0 END
END = 1
)
GO

If we try and set the CarReg for an Agent now, it’ll fail:

The UPDATE statement conflicted with the CHECK constraint "CK_Employee_CheckFields"

Well, that works! Job done, no? Not really… every time we need to add or remove Agent- or Manager-specific fields, we’ll have to change that CHECK constraint; what if there were 100 fields? The constraint could get pretty unwieldy. Not to mention the wasted column space: Agent fields will always be NULL for Managers, and vice versa.

Drop the CHECK constraint, the TerminalCode and CarReg fields in Employee; and then we’ll create two new tables to hold the type-specific data:

That’s better! Except how do we enforce that AgentData only holds EmployeeIDs that relate to Agents, and that ManagerData only holds EmployeeIDs that relate to Managers? We could use a trigger, absolutely. But for the purposes of this post, we have an irrational fear of triggers and don’t want to use them! Can we enforce our rules with just keys?

(And here’s where the quote at the beginning becomes relevant…) Yes, I think we can, but I don’t know that we should. The following solution seems to work, but I don’t know if I’d ever use it in practise, it feels over-the-top. (I should also add, I’ve not tested it in a production-like environment, it might be unsuitable for some reason I’ve not yet discerned.) Anyway, here it is.

We’ve given the Employee table a composite PRIMARY KEY, comprised of the EmployeeID, and the EmployeeTypeID. The EmployeeID also has a UNIQUE index on it, for two reasons: (1) obviously, to stop the same EmployeeID having multiple types, and (2) so that other tables can reference the EmployeeID in a FOREIGN KEY, without having to include the EmployeeTypeID as well. Without the UNIQUE constraint, we’d get the following error:

There are no primary or candidate keys in the referenced table 'dbo.Employee'
that match the referencing column list in the foreign key

, but with it, we’re ok. We’ll INSERT the same employee details as before:

Where both tables have a FOREIGN KEY onto the Employee table that includes the EmployeeTypeID, there’s no way to get the types mixed up: an Agent has to be an Agent in both tables, likewise for Managers.