Opinion: And One Column to Rule Them All

Opinion: And One Column to Rule Them All

I work with a lot of SQL Server databases that are poorly normalized. One of my pet dislikes is the column to rule them all.

Here are simple tests:

If I ask you what’s stored in a column and you can’t tell me a single answer , then you’ve got a problem.

If you need to refer to another column to work out what’s in the first column, then you’ve got a problem.

Here are some examples:

  • If you have a column (let’s call it ObjectID) that sometimes holds a TeamMemberID, sometimes it’s a CoachID, sometimes it’s a TeamID, etc. then you have a design problem.
  • If you must refer to another column (let’s call is ObjectType), to work out what’s in the ObjectID column, then you have a design problem.

Instead of a combination of ObjectType and ObjectID, I’d rather see you have a TeamMemberID column that’s nullable, a CoachID column that’s nullable, a TeamID column that’s nullable, etc. And at least there’s a chance that you could one day even have foreign keys in the database, and some chance of integrity. (But that’s a topic for another day).

One of the strangest reasons that I’ve heard for this was to “try to minimize the number of columns in the table”. Please don’t say that. No sensible person is going to ever exceed the limits.

Prior to SQL Server 2008, the limit for the number of columns per table was 1024.

It’s hard to imagine what you’d use more than that for, but the SharePoint team asked to have that increased. Apparently, 10,000 columns wasn’t enough, so we ended up with 30,000 columns per table now. I struggle to think about what type of design leads to that many columns but it’s also why we got SPARSE columns and filtered indexes in that version. (Mind you, filtered indexes were a great addition to the product on their own, unrelated to SPARSE columns). Let’s just leave that reason as “oh, SharePoint”. Can’t say I love their database design, at all.

But for the rest of us, limiting the number of columns in a table isn’t a valid reason for messing up normalization, particularly when those columns are keys from other tables.

2017-12-26