SQL : one foreign key references primary key in one of several tables

Question

I am working on an application that will be used as an extensible framework for other applications.

One of the fundamental classes is called Node, and Nodes have Content. The SQL tables look like this:

TABLE Node ( NodeId int, .... etc )

TABLE NodeContentRelationship ( NodeId int, ContentType string, ContentId int)

It will be up to the developers extending the application to create their own content types.

Clearly this is bad from a relationship-database point of view as it is not possible to add a foreign key relationship to NodeContentRelationship.ContentId, even though it is a foreign key column.

However, the solution is quite simple and powerful so I am reluctant to change it.

What do you think - am I in for a world of pain down the track?

Answer 1

Beware the Inner Platform Effect.

If you're trying to build an 'extensible framework' which allows developers to store data of different 'content types' and relate them to each other in a generic fashion, you may find that others have already solved this problem.

Answer 2

Why is it impossible to set NoteContentRelationship.ContentId as a foreign key? You can easily use a relational inheritance model with a table Content representing an abstract base class, and various tables AnimalContent, CarContent, etc. representing derived classes.

Answer 3

This looks to me like a variation on EAV (Entity, Attribute, Value) design.

The benefits and drawbacks of EAV design have been extensively documented.

Here is a description of EAV from a sympathetic point of view:

http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm

And here is one from a hostile point of view:

http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

Be aware of the downside of EAV before you commit thousands of man-hours into pouring data into it. It's enormously seductive to programmers, but it can become a data manager's nightmare.

Answer 4

You're in for a world of hurt down the road if you ever want to actually report on this data. You just made it much much harder to write joins and such. The lack of constraints is bad, but the extra work needed on queries is (IMHO) worse.

However, if you want other developers to be able to extend the system and store data in the database but not be able to change the database schema, there might not be a choice. In that case, the answer is to minimize how much gets stored this way. You might also speed it up slightly by replacing ContentType with a ContentTypeId defined in another table.