Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Re: OO concepts and relational databases

by BrowserUk (Pope)
on Aug 04, 2004 at 00:30 UTC ( #379865=note: print w/replies, xml ) Need Help??

in reply to OO concepts and relational databases

I've been following along on this thread and (mostly)reserving comment but..

What you are advocating, is to design your database schema to reflect the way your programs choose to model the underlying data.

This is soo wrong.

DB schema's should (can only) reflect the structure inherent in the data; not how one particular application chooses to model (some subset of) that data.

What if a new application needs to model (a different subset of) that data in a different way?

You then have two choices.

  1. Adapt the schema to the new application and re-write the first application to suit.

    This will inevitably leave the schema a compromise between the requirements of both applications, throw away any DB (index) tuning that had been done for the first application.

    It can also lead to the situation where the needs of one application are irreconcilable with the other.

  2. Duplicated the data into two databases, each schema'd for the particular application.

    Hopefully this choice needs no annotation.

The advantage that using stored procedures, writable views and triggers to represent the business logic is:

  1. You can leave the database schema to the DBA. He can normalise the data, setup the referential integrity according to the structural needs of the data itself.

    He can the tune the DB (indexes etc) over time in accordance with the actual loads placed upon the DB in use.

  2. The applications get access to their preferred model of the data through stored procedures and views.

    This places most of the heavy hitting directly in the DB itself, where is is best handled and best tuned.

    1. Each application can have what ever view of the data it requires.
    2. Multiple applications can share the same data, through different views without compromising the integrity or perfromance of the underlying schema.
    3. The applications access to the data is controlled at the business level. Changes in business rules can be tested, proven and instigated at the DB level and (can be) invisible to the application code.
    4. As applications evolve, their view of the data, and the makeup of their subset of the total dataset, can change and be accomodated without necessarily modifying the underlying schema.
    5. New fields and relationships can be added to the schema transparently to existing applications.

      Achieving this level of isolation is hard.

      That's why schema design lies in the province of the specialist DBA not the generalist application programmer.

      It's also why good DBA's are worth their weight in gold (and usually know it!), and bad ones can bring whole departments and companies to their knees.

  3. The business retains control over their data.

    The data for individual applications all lives in one large business database rather than lots of little databases.

    Even if those small databases all live within the same RDBMS, writing new applications that draws together relationships across the boundaries of those small DBs becomes impossible.

    Attempting to correct this by making applications use one or more tables within a single DB results in duplication of data, and/or compromising one application's needs for another.

The solution, invented long ago by someone far clever than myself, is writable views and stored procedures (and triggers to ease the use of SPs).

The only cogent argument I've seen against the use of SPs and views to implement business rules and provide the isolation layer between applications and the schema is the difficulty that arises if the RDBMS vendor is changed.

This is only a major problem if:

  • The new RDBMS doesn't provide for the same functionality as the old.

    Moving from PgSQL to MySQL would be a problem currently.

  • If too great a reliance is made of the extended features provided by one RDBMS.

    Avoiding 'lock-in' by avoiding 'extended features' is a problem in all fields.

    Weighing the decision to use such facilities and risk lock-in is a difficult compromise to make and depends entirely upon the long term benefits of using such facilities.

    As much as any other reason, that is why such extended features are evolved & marketed, and why it takes so long for such extensions to be unified into standards.

The truth is that changing DB vendor is always going to be traumatic if the facilities of the RDBMS are being used in a cross-application, joined-up way.

There are several companies out there that will try to sell you on gaining independance from your RDBMS vendor by selling you a middleware layer in which to encapsulate your business rules.

What they don't tell you is, that independance comes at the cost of restricting you to the lowest common denominator across all the RDBMSs that they support. Just as with all cross-platform libraries.

And, you just moved your lock-in from the RDBMS vendor to the middleware vendor. In practice, the RDBMS vendors are generally better.

Examine what is said, not who speaks.
"Efficiency is intelligent laziness." -David Dunham
"Think for yourself!" - Abigail
"Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon
  • Comment on Re: OO concepts and relational databases

Replies are listed 'Best First'.
Re^2: OO concepts and relational databases
by dragonchild (Archbishop) on Aug 07, 2004 at 03:28 UTC
    I believe that we are talking in the same direction, but in different ways. My OP was discussing the fact that many OO developers choose to use tools that design and use schemas that are very inefficient.

    I am saying the same things you are:

    • Schema design should be left in the hands of those who know how to do it
    • A well-designed schema is so because it's focused on how the data relates to itself, not how any given application might use the data
    • Access to a well-designed schema needs to be controlled, through any number of mechanisms. These might include:
      • Views
      • Stored Procedures / Triggers
      • Some middle-tier in code, such as XML-RPC and/or other web services
    • Updates to that schema should be controlled by those who designed it

    Now, I don't go as far as to say that one method is preferable to another. The primary goal is to ensure the integrity, structure, and safety of the data. How that happens can almost be viewed as a matter of personal taste.

    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

      I apologise. I lost sight of the purpose and content of your OP as the thread progressed over time.

      There are one or two sub-threads where you are counter-arguing assertions made by others in which you appeared to be arguing the case for things like Class::DBI and Class::Tables etc., and condeming the use of SPs, triggers and views.

      Re-reading the entire thread in sequence and context, I now realise that whilst you did argue the case against the latter, it was not in support of the former.

      My accusation was unfounded, based on my memory of what I thought you had said over several posts over several days, instead of what you actually said and the context in which you said it.

      For allowing my own prejudices on the subject, to cause me to confuse the leaves of thread, for the trunk of the argument. Again, I apologise.

      Examine what is said, not who speaks.
      "Efficiency is intelligent laziness." -David Dunham
      "Think for yourself!" - Abigail
      "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://379865]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2021-01-23 11:09 GMT
Find Nodes?
    Voting Booth?