pull down to refresh
I've always though that philosophically what should be aimed for is the most coherent and easily expressible data model that minimizes redunancy, but with a syntax and subroutine library that allows for quick retrieval of even highly nested relational structures.
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.
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.
I always wondered. Is this a downside of the data model or a downside of the available SQL syntax / subroutines?
As a semi-technical person who works with databases all the time, but has little idea about the underlying technicals of how they're implemented, I've always though that philosophically what should be aimed for is the most coherent and easily expressible data model that minimizes redunancy, but with a syntax and subroutine library that allows for quick retrieval of even highly nested relational structures.
I may be talking out of my butt and simply unaware of the many alternative database solutions out there. But this is my perspective as someone who works frequently with table-based relational data structures.