Web Development - Object db vs Relational db

Question

Whats the cons and pros of using a object database or relational database for regular web development which involves a lot of CRUD?

UPDATE: I reopened the bounty reward in order to give Neville it.

Answer 1

The concept of an OODBMS is quite broken, and the various commercial and free offerings that have emerged over the last few decades have barely made a dint in the marketplace.

The relational model is more powerful than object models in terms of the kinds of questions you can ask of your data. Unfortunately, SQL threw out much of the expressive power that the relational model is capable of, but even in this diluted form, it is still easier to express queries in SQL than in a typical OO database (be it ORM or OODBMS).

Queries in an OODBMS are predominantly driven by navigational operators, which means that if your sales database has sales people owning their sales, then querying for the monthly sales for a given SKU is not only likely to be cripplingly slow, but very awkward to express. Consider also a security model that grants employees access to buildings. Which is the correct way to express this? Should employees hold a collection of buildings they can access, or should buildings hold a collection of employees that have access to them? More to the point, why should either class have to have a collection of the other baked into its design? And, whichever one you choose, how would you ask which pairs of employees have more than one building they can access in common? There is no straightforward navigational pattern that can answer such a question. The sensible solution — an "Access" object — is essentially a reversion back to a properly normalised relational schema, and it requires some kind of query language that borrows heavily from the relational algebra in order to answer the question without a massive over-the-wire data transfer.

Also consider another major strength touted for the OODBMS: methods, especially inheritance with virtual methods. A sports clinic might have different risk-of-injury metrics for different kinds of athlete. In the ORM world, this would be automatically expressed as a class hierarchy, with Athlete at the root, and a virtual method, int InjuryRiskScore() implemented by each derived class. The problem is that this method is invariably implemented on the client, not at the back end, so if you want to find the 10 highest risk athletes across all sports at your clinic, the only way to do it is to fetch all athletes across the wire and pass them through a client-side priority queue. I don't know the OODBMS world as well, but I think the same problem occurs, since the storage engines generally only store enough data to rehydrate objects in the client's programming language. In the relational model or SQL, you would express risk-of-injury scoring as a view, which could be simply the union of per-athlete-type views. Then, you just ask the question. Or you can ask more complicated questions like, "Who had the greatest increase in their risk-of-injury since last month's checkup?" or even, "Which risk score has proven to be the best predictor of injury over the last year?". Most importantly, these questions can all be answered inside the DBMS with nothing more than the question and the answer travelling across the wire.

The relational model allows the DBMS to express knowledge in a highly distilled manner based on predicate logic, which allows the various dimensions of the facts you store therein to be joined, projected, filtered, grouped, summarised, and otherwise rearranged in a completely ad hoc manner. It allows you to easily cook up the data in ways that weren't anticipated when the system was originally designed. The relational model thus permits the purest expression of knowledge that we know of. In short, the relational model holds pure facts — nothing more, nothing less (and certainly not objects, or proxies thereof).

---

On a historical note, the relational model emerged in response to a disastrous state of affairs with the existing network and hierarchical DBMSs of the time, and largely (and rightly) displaced them for all but a small niche of application areas (and even these probably remained largely because SQL failed to deliver on the RMs power). It is deeply ironic that much of the industry is now essentially yearning for the "good old days" of network-theoretical databases, which is essentially what OODBMSs and the current crop of NoSQL databases are going back to. These efforts rightly criticise SQL for its failure to deliver on today's needs, but unfortunately they assumed (wrongly, and probably out of pure ignorance) that SQL is a high-fidelity expression of the relational model. Hence they neglected to even consider the relational model itself, which has virtually none of the limitations that has driven so many away from SQL, often towards OODBMSs.