How and why the Relational Model works for databases

-- Fred Brooks, The Mythical Man Month (1975)

It completely ties you to one implementation and precludes using the data for other things such as reporting and alerts or even ad hoc queries.

Exactly. Data is far more valuable than code. We still work with data from literally centuries ago. Most programs written even 10 years ago are probably no longer in use. I would be surprised if the oldest code still doing something useful was more than 20 or so years old.

https://en.m.wikipedia.org/wiki/QUEL_query_languages https://en.m.wikipedia.org/wiki/PostgreSQL#

I would like a better alternative, but it would need some very significant benefits compared to SQL to gain any traction. SQL is just so entrenched at this point that even NoSql database engines are adding support for pseudo-SQL query languages (which is really the worst of both worlds - the clunkyness of SQL syntax without the power of the relational model).

Apart from being subjectively displeased at SQL syntax, most problems with SQL are actually query language independent issues in the RDBMS: maddening proprietary extensions, implementation limits, nonportable details, library and system issues (e.g. character encoding and default configurations).

The best alternative query languages can do is making certain queries (advanced, rare ones) easier to express.

Working in Fox dispel many of the weird limitations of sql (and BTW, you can use SQL on Fox alike linq, is first-class).

The #1? You can build ALL using the relational model. After MS kill Fox my career can be summarized as: Trying N workarounds because I don't have Fox anymore.

Other langs (python, delphi, f#, rust) capture a little of the magic or make the workaround bearable, but none is as productive.

SQL is somewhat nice because its fairly well standardized.

This is kind of like a sticking effect. It got in at the right time and it was good enough and there wasn't enough interest in developing something to replace it that it just became standard.

But if you want to get down to it, any entity data model implemented is kind of an attempt at a replacement.

  > You could make a relational database that was managed with a language other than SQL, right?

But the idea of never writing SQL again remains compelling to a certain sort of mindset, one which I have been known to share when throwing myself against the proverbial wall.

SQL isn't a bad dialect with which to hand-roll queries against relational data. I'm quite sure there's room for improvement, especially when it comes to generating queries; I've dipped my toes in this and the compiler has to 'think in SQL' eventually, it's not composable in the way that it should be.

But "SQL is annoying and ORMs are terrible" is, from my recollection, the sentiment which gave us the meteoric (pun intended!) rise of Mongo, and "relational modeling isn't negotiable" is the iron fact of our profession which lead to its ignominious fall.

I have been hacking on a Clojure/Script library (https://github.com/wotbrew/relic) to experiment with exactly this kind of thing. / PLUG

For most problems I encounter I want to focus on the data and its relationships.

I cannot help feeling like programming languages and idioms have me deal with too much 'mechanism', the machine like structures and forms that are mostly incidental to the data domains I work in, and are entirely about performance, layout, access pattern etc - when a machine could do an ok job of making those decisions for me until optimisation becomes important.

    SELECT 
        CASE WHEN o.EmailAddress IS NULL OR o.PhoneNumber IS NULL
            THEN 1
            ELSE 0
        END AS [IsSubmitDisabled]
    FROM Customer c 
    INNER JOIN Order o ON c.Id = o.CustomerId
        AND o.IsActiveOrder = 1
    WHERE c.IsActiveCustomer = 1

I’m sure there is a time and place for it (Power Apps is a billion dollar business), like time keeping frontend logic. Apps with significant logic and complexity will always need developers.

I’m not critical of your app as I have no real understanding of what it does and why the design choices were made. I do not think embedding logic in SQL would be an ideal way for any app I’ve worked on to work.

Modeling all data on SQL is definitely an improvement on most ad-hoc data manipulations people do.

But say you want to stream updates from your SQL queries, this is something the relational model and SQL dbs are not great at doing efficiently and actually hinder.

Other than your first sentence (which is subjective), you are correct. The only question is whether you are going to mangle the database structure to fit your OO hierarchy or design your program in a non-OO way to fit the relational structure.

Since the database will live on long past the program, and will have multiple programs talking to it, it makes sense to design your program around the data, not design the database structure around your program.

[EDIT: See https://blogs.tedneward.com/post/the-vietnam-of-computer-sci... for why OO is a terrible design for persistent data]

Where you're wrong, and it may take more experience to realize, is that the problems in the mismatch lie far more on the OOP side than on the relational side.

Object oriented designs - the navigable graphs of objects, leaving aside polymorphism - usually privilege a particular perspective of the data, a perspective which is suited to a specific application. For example, a store with items, customers and orders: the application is focused on creating transactions. Relational models, on the other hand, support multiple perspectives "out of the box" and are expected to support multiple applications; for example, reports that roll up by item, by category and by customer; reusing the same customers for a different application; and so on.

Different applications and perspectives usually require different object models. The alternate application reusing the same customer entities won't want to couple with a store application and decorate every customer with an irrelevant list of orders. A reporting app is better off with a visual query builder that creates SQL to hand off to the database than trying to iterate over object graphs performing aggregations and sorts. And so on.

For applications with ambitions for a longer lifespan, start with the database schema. Keep application models fairly thin; focus on verbs; try to ensure only one piece of code owns mutating a logical database entity with a set of verbs; and keep entanglements like UI out of those verb implementations so that you reuse the verbs, rather than being too tempted to reimplement direct database modifications or push too much logic into models.

Object oriented models come and go, applications are rewritten, but database migrations happen at a much slower pace, and data lasts for a very long time.

E.g. you might have a Customer table and an Order table with FK from Order to Customer. This would correspond to a Customer object holding a collection of Order objects - it would not correspond to Order being a subclass of Customer.

In any case, the relational model is not supposed to map directly to any particular programming model or paradigm. You should be able to interact with relational data from OOP, imperative, functional and declarative languages.

So when Employee works for Company, Company is the parent class of Employee?

Lets say you have a superclass named Person, with primary key person_pk. You can then, in the data model, implement polymorphism by creating a sub-class-table, for instance CustomerPerson, by giving it the same person_pk as primary key, where the same person has the same value for the primary key.

In this case, every person should be in the Person table, but only customers should be in the CustomerPerson table.

If person is implemented in your OOP by inheritance, you can leave shared fields (variables) in the Person table, and only add additional fields to the CustomerPerson table. Your program can then instantiate Person objects by simply using the Person table, and CustomerPerson objects by joining by person_pk.

If Person is just an abstract interface, you may want to have it only contain the primary key, and have all fields in the subclass CustomerPerson table. In this case, you could join CustomerPerson directly with some address-table, for instance, by person_pk, without going through the superclass (Person).

(And if you need it to also generalize to companies, you could have both Company and Person be subclasses of a class named Subject, and use subject_pk as primary key in Subject, PersonSubject, CompanySubject, PersonCustomerSubject and CompanyCustomerSubject and even CustomerSubject)

The approach above works better in my experience, than having subclasses have their own primary keys, especially if you read more than write to the db, as it can drastically reduce the number of joins you need to do, and because it prevents one-to-many relationships between subclass and superclass. (A limitation would be if you are not able to recognize a shared primary key across subclasses when creating an object.)

There's your problem. No such thing as a sane class hierarchy, but thinking there is leads you down a dark path where relational modelling looks "difficult to grok". Rather the mental model you've trained on hierarchical OO code is the awkward one, and it's ultimately a dead end.

I've always felt that what we need is a way to maintain a logical schema (E-R diagram / graph schema), and then the physical schema is automatically generated along with denormalizations for perf as necessary. A graph db is simply denormalizing its joins using index-free adjacency.

Sorry, I am assuming here its my ignorance thats teh issue not knowing any real word examples of why they are a big deal.

Edit: I think I was actually thinking of WinFS which came out of Cairo later, around 2000.

Meant in jest; though I think the idea of a "one true way" to access files is a pipe dream. The hierarchical works better on my computer than whatever scheme we've cooked up for our phones.

A tree-based file-system is optimised for doing a search from the users perspective, finding a file takes log(files) steps and finding related files is trivially cheap. It is likely hard to outdo that with a relational model.

It doesn't map that well, the idea sounds intriguing, but in practice a filesystem seems like a better model for files.

How do you know it isn't?

working on master data and BI changed my whole perspective on how to value software and what i demand of any business application going even vaguely close to business critical processes (hint: unfettered db access to cut through your bullshit.. its my damn data thank you :D )