Best practices when dealing with lots of empty table columns?

Question

I have a schema that allows for multiple post types in my Django project (kinda like Tumblr). Users can make Posts of different types. One of those types is a photo, which I'll want the EXIF data for.

If I have a super generalized table, does it make sense to create additional columns for the exif data? This would result in a ton of empty columns for every post type that isn't Photo.

Alternatively, would it make sense to create a separate model for photos completely and create a foreign key to the Posts model instead?

Answer 1

What you are basically referring to is modelling inheritance in a database. You might find this answer on Stack Overflow useful. There are a few other similar questions answered in similar ways there too.

In terms of choosing which of the 3 options to follow, there's a couple of factors which I think you would find important. I'd say the first consideration is just how many properties your post types have in common. If they are many types, all 90% the same, you probably don't want a table per concrete (TPC).

Next, I'd consider which of the models are supported nicely by the django ORM. You don't want to pick the most pure solution from a relational database perspective, that becomes awful to use because of the limitations of your data layer.

My tendency would be to go for table per type, but most of my time is spent working without a "modern" fancy ORM, and optimization of my schema is important. It will likely have the result of making queries more complex. This approach implies your suggestion of creating a foreign key to an additional table.