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 makes1 - 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 have2. 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 relationships3. 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 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.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

In reply to Re: object-relational Perl programming: best practice or compromise? by dragonchild
in thread object-relational Perl programming: best practice or compromise? by metaperl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.