I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost
, WorldNewsPost
, TechnologyPost
, etc). All posts types are conceptually the same, but they share very few columns. For example, all posts have user_id
, description
, email_on_question
, and email_on_bid
columns, but on top of this will have 10 - 20 unique fields. All in all, I'd say about 8 out 60 columns are shared between post types.
Because of the vast number of unique columns, and because some post types have unique behavior and children, I initially decided to go with Concrete Table Inheritance - each object is represented by a separate table, with shared columns being duplicated on each table:
-- SportsPost -- | -- WorldNewsPost -- | -- TechnologyPost --
user_id user_id user_id
description description description
team location industry
The problem with this approach is that it's very difficult to query and aggregate all posts. I have a dashboard view which lists the top 20 most recently created posts, and in order to retrieve the data for this view I have to execute a separate query on 10 different tables! I execute all 10 queries inside of a transaction to save myself from doing multiple roundtrips, but I'm still concerned that the performance of the query will be very poor as the application scales and new post types are created.
I'm considering switching over to Single Table Inheritance at this point. Instead of executing 10+ queries for the dashboard view, I'll only need to execute one query. Does this sound like a reasonable approach? Are there any drawbacks of STI (other than NULL columns) that I'm not aware of?