pull down to refresh
I think the SQL standard should evolve to address polymorphism like this directly though. It's incredibly common and has no direct solution afaict.
Yeah that's what I think too. This seems like a common enough issue that it should be built in. You should be able to express your inheritance schema without having to hack together triggers to keep things normalized. Any denormalization that needs to happen for performance purposes should be kept "under the hood" and used in the background when needed.
But I may be underestimating the technical challenge in accomplishing that, or maybe there isn't an easily generalizable solution that works for everyone's data model or use cases.
reply
That is the aim. We call this database normalization. It's the right way to design these systems. In practice, you often denormalize data for performance reasons because there are limits to how fast a computer can access and process data especially when it is spread around.
When you're joining tables, which are each a collection of files on your harddrive, you are randomly accessing another collection of files for each join. Indices can help the database know exactly which file it needs to read to get a particular row, but any file read is slower than none.
I try to keep our data "normal," so we always know what is "true," whether I end up also denormalizing the data or not.
I think the SQL standard should evolve to address polymorphism like this directly though. It's incredibly common and has no direct solution afaict.