Foreign key constraint based on conditional column

Question

I'm trying to figure out the best way to handle this situation:

I have a table set up as follows:

LEARNING CONTEXT
================
LearningContextID - INT - Surrogate Primary Key
EntityID          - INT - Foreign Key to an Entity Table
OutcomeID         - INT - Foreign Key to an Outcome Table
Context           - VARCHAR - Name of the Entity Table

I have 3 tables: University, Program, and Course which are all "entities".

How can I set up a foreign key constraint that will know which table to be linked to? I suppose it's a composite key of EntityID and Context, but how can I make that conditionally constrain against the given Context's table?

Answer 1

How can I set up a foreign key constraint that will know which table to be linked to?

Either you can't, or you shouldn't. :)

The standard way to structure this is to have a parent Entity table, and a table-per-child for each of University, Program, and Course. The Entity table optionally contains a denormalized column that specifies the type of entity -- this has a bunch of advantages, so I recommend it. Also, at least in the development stages, I would recommend creating triggers to validate that rows in this structure end up in the right place (i.e., for a given EntityID, there should be only 1 matching row in all the child tables).

Then all you do is create the foreign key to the primary key of Entity.

What I've described looks like this (SQL Server syntax):

CREATE TABLE dbo.EntityType
(
    EntityTypeID tinyint NOT NULL
        PRIMARY KEY
);

CREATE TABLE dbo.Entity
(
    EntityID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityTypeID tinyint NOT NULL
        FOREIGN KEY REFERENCES dbo.EntityType(EntityTypeID)
);

CREATE TABLE dbo.University
(
    EntityID int NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

CREATE TABLE dbo.LearningContext
(
    LearningContextID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

This is the equivalent of subclassing in a database, if you're familiar with client-side programming constructs.

Note how this structure easily allows adding a new derived Entity subtype without changing the existing schema objects.

Answer 2

An alternative to @Jon's answer gets you around some of the nastier referential integrity and database design issues with a meaningless abstraction like 'Entity':

You already have University, Program, and Course tables, so create tables called University_LearningContext, Program_LearningContext, and Course_LearningContext. That way you can enforce, at a database level, data and relationships that only apply to certain 'entities.' Abstracting everything to 'Entity' means you end up enforcing all kind of data rules in app logic (or, worse, you don't enforce them at all!), which is really not the way to go.

It appears more complex because you're creating additional tables. However, from an overall architecture perspective it very clearly describes and enforces data integrity rules at the proper layer.