I would first point you to OO concepts and relational databases, and other nodes referenced in that thread. The object-relational mismatch is a big deal, and the gains are small and temporary. The issue is that you're trying to shove a square peg (objects) into a round hole (set theory), and I think I'll explain further in this node.

The fundamental error most developers make - and this is the root mistake that CDBI makes¹ - is that they don't understand databases. A database of one table doesn't add any value over having that table in a BerkleyDB or something similar. A database of a few, unrelated tables is equally useless. The main reason is that you will have a ton of overhead and no benefits.

It is only when you start defining relationships between your tables that you start to realize the benefits of relational databases. RDBMSes don't exist to store and retrieve data - that's not the point. They exist to enforce relationships between data. That is the sole piece of value that Oracle, Sybase, DB2, MySQL, and the whole lot of them have². Otherwise, we would just be using huge trillion-record indexed files with a few ultra-optimized lookup strategies.

Those relationships, as I've argued before, are solely HAS-A relationships³. Most OO code is about IS-A relationships, quibbles aside about whether this is a good idea. And, the first place everything breaks down is in the cross-reference (xref) table. This is where you have Foos and Bars and each Foo can have multiple Bars and each Bar can have multiple Foos. This kind of N-to-N relationship is extremely common in most RDBMS schemas. But, I have yet to see a good way of doing it in OO-land. In fact, I think I can argue that OO-land doesn't have a way to express N-to-N relationships and that xref tables are a purely relational concept.

Now, let's say you can express N-to-N relationships in OO-land. Are you going to be able to express the relationship as an object? Because, I will have information about the relationship itself in the xref table. Here's a perfect example:

I work in a medical claims company. Normally, a given insurer will bear 100% of the responsability of a claim. This is about 98% of all our business. So, we should put a branch_id column on the claims table, right?

Well, we have that 2% situation where insurer A will pay 80% of a claim and insurer B will pay 20% of that claim. So, we have to have an xref table. But, we have to track more than just which branch a claim is being paid through. We also have to track

• how much that branch is paying
• is there a maximum that branch will pay on that claim
• what claim number does that branch use to describe that claim

How would you solve that in OO-land?

1. CDBI is an excellent module. And, in fact, I will be using it for an upcoming project of mine. That doesn't mean it doesn't have its issues.
2. For those who would say that BDB and similar solutions don't scale like Oracle or DB2 do ... well, if you had a billion dollars for R&D into scalability, you'd scale just fine. In fact, it's arguable that spending that same money in non-relational solutions would have resulted in petabyte solutions that are cheaper than the terabyte solutions we have now. Having to maintain referential integrity is expensive, and not just in terms of CPU or RAM.
3. They're actually HAS-A relationships in reverse, because the containing object doesn't know what it contains. It is the contained object that actually keeps track of its container. This is reversed from how most OO solutions do it.