Fantasy SQL Feature – T-SQL Tuesday

Fantasy SQL Feature – T-SQL Tuesday

This month for T-SQL Tuesday, Kevin Chant has asked the question “What is your Fantasy SQL Feature?”

There are a few that I would love to have, but the one that keeps popping up is having a foreign key that can reference more than one table. I know this seems like an odd request, but hear me out.

I have worked for a number of companies where the business wants to normalise a common part of many different types of objects in to one child table. The problem is maintaining a one-to-many relationship with that child table, forcing referential integrity and all the other good stuff without adding a whole heap of other tables or columns to achieve it.

To illustrate this problem a bit, let’s make a few small schema changes to the Stack Overflow database. Imagine that we wanted to split the Wiki post type out of the posts table – maybe the meta data we need to store is changing so much that it doesn’t really fit with Questions and Answers anymore, maybe we want to put the table on a different file group, maybe…it doesn’t matter why, let’s just do it.

Currently the Posts and Comments table can have a simple foreign key between them on Comment.PostID = Post.ID

But now we are going to split Wiki out in to it’s own table:

For whatever reason, Stack Overflow still wants to store all of their comments in the comment table – maybe it makes sense for a technical/reporting reason, maybe it makes sense because the business has just decreed that it’s going to happen (I know it’s not a technical reason, but sometimes you can’t win against managers). So a comment can be made on a Wiki, and I want to build a foreign key to Wiki as well…but you can’t have Comment.PostID pointing to both tables. You can index it, you can try and enforce the integrity in your database, but in the end you can’t easily enforce the referential integrity in the database.

The feature that I’d love to see is a way to have PostID still be a foreign key, but with a bit of extra metadata on the row to tell the engine which table this row targets. Maybe you have a PostType column on Comments that tells you that this row either points to the Posts or the Wiki table. Then you could have a foreign key to Posts and to Wiki, ensuring that Comment maintains it’s referential integrity, preventing the owner of the comment being deleted and so on.

To keep performance running, you would need to rethink the foreign key index, so that the new PostType and PostID column are indexed together. I’m sure there’ll be other performance problems as well, but I think that’s the easy part.

I know that there are ways around this problem, and as the table grows it’s probably worth considering breaking that table in to 2 different tables, but I keep finding times where the business just wants to shove the data in together, and this would be a nice feature to help manage that.