1

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?

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.

| improve this answer | |
  • 1
    I HATE generic entities (how do you enforce that certain LearningContext values do not apply to some entities? what is the natural key for 'entity'), but if you insist on one this is the way to do it. – Jon Boulineau Oct 30 '13 at 17:26
1

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.

| improve this answer | |
  • +1 Good points; this is a valid alternative. The abstraction approach may be more appropriate when the system will tend to be extensible vs. fixed. This approach doesn't allow the opportunity for common fields, which may be important. In my experience, I've found that most of the time, business rules related to the abstraction structure can be applied within the database, so while I don't completely agree with what you said about that part, it's good that you pointed it out because it may also be important. – Jon Seigel Oct 30 '13 at 17:40
  • You can enforce business rules within the database schema or within additional code that has to be written that just happens to be in SQL database objects (i.e. stored procedures and triggers)? Making sure the schema accurately describes the truth and enforces rules is superior to embedding rules in code, IMO. – Jon Boulineau Oct 30 '13 at 17:46
  • I completely agree with keeping constraints as close to the schema as possible. I've never had to go farther than using triggers -- which really are halfway between "schema" and "database objects," I personally consider them closer to "schema" -- but obviously YMMV. – Jon Seigel Oct 30 '13 at 17:51
  • The problem with triggers, from this perspective, is that it 'hides' the model. The actual description of what the model really is is all tied up in code that you just have to know is there. There are also numerous issues with the way RDBMS have implemented triggers that make them ... problematic. Anyway, this could be come a 'religious' argument so I'll leave it at that ;) – Jon Boulineau Oct 30 '13 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.