4 ms·
I agree with the general sentiment that this is a better stack overflow question. But, it's made it to the HN front page, so it deserves some discussion. In ge
by gwbas1c 7y ago
I agree with the general sentiment that this is a better stack overflow question. But, it's made it to the HN front page, so it deserves some discussion.
In general, there is no one-size-fits-all answer, because the answer is really driven by your data. Which brings up the most important point:
Your database is your application's source of truth. It must have the best data model, and will most likely outlast all source code that you write.
So don't try to model your data as if you're writing an object oriented application. Model your data at the database first, and then write your application to fit the model of your data. (Don't put the cart before the horse.)
Anyway, the general patterns in this case are:
- One table with lots of null columns
- Lots of tables that join: (Animal joins pet joins Dog/Cat/Fish)
- Separate tables (Dog/Cat/Fish, no joins)
All approaches have tradeoffs. The one big table approach will have "waste" with the null columns, but remember that joining pet to animal to dog has a penalty on read, and that separate tables has implementation complexity.
IMO: If your entities are known, (IE, you won't add a new type of animal every month,) and your database is read-heavy, go with a single table with null columns. Otherwise, you need to justify the complexity of multiple tables.
- cjblomqvist 7y agoOne "trick" applicable to the mega table solution (one table with all data) to preserve performance (kind of) is to do indicies that spans each type, or do views (which technically will be almost the same as indicies to the DB internally). I'm by no means a DB pro, but I've looked up the same answer and been working with these cases (with these twists).
- foota 7y agoThis would almost certainly be removed on stackoverflow.