5 ms·

I think a lot of the frustration with relational databases in general comes from a misunderstanding. People conflate "relational" with "SQL", because of the hi

• ap22213 14y ago
  In some ways, I agree with you. SQL is part of the problem. But, correctly structuring data is generally very hard, time consuming, has diminishing returns, and adds maintenance issues that never go away. I've seen teams spend months up front trying to craft a perfect schema, only to see it demolished when a non-obvious edge case appears. I've also seen teams become over burdened with schema and data maintenance, that it takes up 20-30% of their time. That's not to say that you're not right, but sometimes ORMs are just the more pragmatic answer to get a team through the first 4-5 iterations (or even longer). And, sometimes path-independence is important. But, often, the database just ends up being coupled with the application anyway. At least that's my experience.
• xi 14y ago
  People conflate "relational" with "SQL", because of the historical accident that SQL is the most popular way to query relational data. Then when SQL isn't a good fit for their problem, they think relational is not a good fit for their problem, which is almost certainly not true. For most practical purposes, SQL is the only way to query relational data. In the absence of alternatives, it's natural to conflate the notions of SQL databases and relational databases. I agree that SQL is a mess, but I don't think an approach based on pure relational primitives would make it better; in fact, I think SQL is a mess specifically because of lack of expressiveness in the pure relational primitives. NULL, ORDER BY, LIMIT/OFFSET, opaque keys, windowing functions, transitive closures, etc fit poorly into the relational model. The original motivation for relational databases is to have path-independent access to data. This is a really powerful idea. I agree with both assertions, but in many applications, path-based access makes the total majority of queries, and SQL or relational model provides little means to distinguish them from other equi-joins or arbitrary join conditions. In my opinion, it would be better to start with a navigational path-based database model, and extend it to allow constructing new paths dynamically.
  • cico71 14y ago
    I'm not sure what you mean by fit poorly in the relational model. ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. As for opaque keys, I thought the old debate about surrogate/artificial and natural keys was over years ago. The relational model has nothing to say about it, that would be like trying to make a model understand if facts are true in the real world or not. A key is a key. Moreover, last time I've checked transitive closure operators were defined for the relation model (people should not stop ad Codd papers, Date and Darwen wrote a lot of books, e.g. The Third Manifesto, extending on the original ideas). NULL is a completely different beast and this is the only real thing one can consider problematic. In some cases NULL just means the predicate for the relation is different because values for an attribute don't apply, so this is not really a problem, in some other cases we simply don't know the value when we are collecting information, and this is indeed a problem. Date follows Wittgestein that said we should remain silent about things we can't speak about i.e. we shouldn't collect incomplete information, Codd came up with I-marks, A-marks and n-valued logic, SQL collapsed everything into NULLs and 3-valued logic.
    • xi 14y ago
      ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. By fit poorly I mean that you cannot express most real-world business inquiries using pure relational primitives without ORDER BY or LIMIT/OFFSET and that's why I think relational algebra is not usable per se. SQL fixed this problem by adding many non-relational constructs, but but without any sense of consistency or direction. I also strongly disagree that ORDER BY and LIMIT/OFFSET are presentational operations since I often use them not only for wrapping the outer SELECT, but also within correlated subqueries. To show some proof, here are a few queries which are hard or impossible to express in relational algebra: 1. Show the blog post with the largest number of comments [^]. 2. Show the tags associated with the blog post with the largest number of comments. 3. For each blog category, show the 3 top blog posts by the number of comments. [^] If more than one exist, pick the latest. NULL is a completely different beast and this is the only real thing one can consider problematic. I think NULL is only hard because relational model is a wrong way to look at the data. If you see an entity attribute not as a column of a tuple, but as a function from an entity set to some value domain, the fact that the attribute is nullable just means that the function is not total. There is a well developed mathematical apparatus for partial functions, in which NULL becomes a bottom value injected to the value domain, and tri-valued logic is simply a monotonic extension of regular Boolean operators.
• alex_stoddard 14y ago
  Can you suggest any good references for alternatives to SQL or a more general discussion of path independent data access? My background is such that practically "relational" and "uses SQL" are wholly conflated. I would be very interested to get a broader view. Does datalog fit somewhere in the discussion?
  • EdwardCoffin 14y ago
    Tutorial D: http://en.wikipedia.org/wiki/D_(data_language_specification) http://en.wikipedia.org/wiki/D_(data_language_specification)
• MattRogish 14y ago
  This is correct! A relational DBMS is simply predicate logic applied to set theory[0]. The DBMS part offers logical/physical data independence, which is amazing, and no one has actually implemented that (NoSQL, neither!). SQL has never been particularly awesome way to interact with relational database management systems; SQL isn't even strictly relational: SQL tables are bags (multisets), not sets[1]. The Rel project (http://dbappbuilder.sourceforge.net/ http://dbappbuilder.sourceforge.net/) is probably the closest thing to a "real" RDBMS. It also implements CJ Date & Hugh Darwin's "Tutorial D" language, a truly relational language used to work with a RDBMS. What I find most ironic with the whole "Relational / Object" mismatch is nothing in Relational Theory prohibits objects. As a matter of fact, a RELATION[2] consists of NAMED ATTRIBUTES and DOMAINS. Domains are basically DATATYPES and OPERATIONS that can be performed on them[3]. Although the notion of CLASSES (in OO land) wouldn't map 100%, much of what you're interested in doing with Objects can and should be directly represented in a truly relational DBMS[4]. In a traditional MVC style framework the RDBMS would consist of the entire Model layer; thus the "Object/Relational Mismatch" disappears! -- Matt Refs: 0. http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf 1. A bag can have duplicate rows SQL does not require that a table have keys NULL introduces three-valued logic 2. http://en.wikipedia.org/wiki/Relation_(database) http://en.wikipedia.org/wiki/Relation_(database) 3. http://www.dbdebunk.com/page/page/627014.htm http://www.dbdebunk.com/page/page/627014.htm 4. http://www.dbdebunk.com/page/page/1717959.htm http://www.dbdebunk.com/page/page/1717959.htm