3 ms·

SQL being notoriously poor at hierarchal data has been known for quite a while; he's obviously very correct there. Oracle goes so far as to offer a non-standar

• Retric 17y ago
  A databases job is to handle Data and a relational database can do that just fine. When you want to handle really complex relationships you need to write code because the rules are not abstract. You can create a horribly complex view / stored procedure and pretend it's the databases job, or you accept that some rules are stored on the database but not implemented using the database. Edit: None of the "database alternatives" help you solve the checkout problem. PS: It's like complaining that HS level Calculus does not let you solve a complex differential equation. Useful abstractions always have their limits because they are abstractions.
  • gchpaco 17y ago
    Data is not simple rows. Data is complex, human generated stuff that has both implicit and explicit structure and it does not collapse down well to any single abstraction, not even object graphs. Fine, so we have to pick some abstraction. That doesn't mean the abstraction we pick is necessarily appropriate. Memory is an abstraction; we could just dump all of memory out to a file and call that a database (and folks used to, and still do sometimes). It has issues for persistent databases, like brittleness and pointer swizzling, so we adopt a more high level one. The relational view of data is a higher level abstraction but that doesn't mean it's an especially useful one. Hierarchy is something that comes up constantly in the real world and that is something very awkward to represent in a relational database. You can try to use Codd's adjacency list, but reassembling the links is costly and drilling down a hierarchy requires a database query at every level. If you don't believe me, here's a use case: I want everything there is about a sub-part of a Bill of Materials with one query and without having to grope through the entire index. There's a reason why Oracle implemented CONNECT BY, which is abjectly non-relational. Also the sheer amount of research and work that goes into making even a simplistic relational database responsive is enormous; when Baker wrote this it was not uncommon to lose two orders of magnitude throughput by going to a relational database from a custom written one, and if you were very lucky be able to claw back another order of magnitude by indexes. People today are complaining greatly about the inefficiencies of using row-structured data when you're usually interested in the columns. Even today the way to get maximum throughput from a database is to denormalize and in doing so virtually set the schema in stone. A 1960s era heirarchial database could traverse a heirarchy as fast as the computer could request data from memory. The models were brittle and didn't always represent the real world well, but you could run an entire country's airlines in the amount of computing power your watch has now. The banks today rely on IBM's IMS, which is hierarchial.
    • Retric 17y ago
      As I said "Useful abstractions always have their limits" you can represent any tree using the relational model. The basic problem is recursive data structures need a higher level of abstraction. Consider the following situation: Bob is managed by Ted Ted is managed by Bob If that situation is allowed then all recursive queries need to deal with it. On the other hand if it is NOT allowed then every commit needs to deal with it. However, while the relational model does not understand those issues just like it does not understand HTML it still stores the data just fine. Generally it's a fairly moot point because it's a program talking to the database and not a person, so that program can provide that level of abstraction just fine, granted it's less efficient than a model that understood the abstraction. As to speed, Relational Databases are the x86 chips of the Database world. There are plenty of custom system that are faster doing specific things, but they sacrifice speed for adaptability and backwards comparability. For example: A core 2 chip's instruction decoder and an SQL databases query optimizer do basically the same thing. With a custom solution you can just skip that step and directly say what to do, but the abstraction is normally worth it so you can upgrade the hardware for years without changing the program. So the relational model makes the basic assumption that speed is not the primary concern or you would be writing at a more basic level, yet people want more speed so there are plenty of real world hacks to help things along like CONNECT BY and stored procedures, and all those new x86 instructions. So, 99.9% of the time in the real world an of the shelf database running on an x86 chip is plenty fast if used correctly and it's also fairly cheap compared to the total costs of the other solutions.
• neilc 17y ago
  SQL being notoriously poor at hierarchal data has been known for quite a while; he's obviously very correct there. While SQL's difficulty with handling hierarchical data is well-known, he asserts that it is somehow a fundamental problem with relational databases, which I think vastly overstates the magnitude of the problem. Oracle goes so far as to offer a non-standard CONNECT statement for tree traversal. Standard SQL added support for recursive queries via WITH RECURSIVE more than 10 years ago, and most modern databases support it; Oracle is just anachronistic here.