Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^3: OO concepts and relational databases

by adrianh (Chancellor)
on Aug 02, 2004 at 20:46 UTC ( #379427=note: print w/ replies, xml ) Need Help??


in reply to Re^2: OO concepts and relational databases
in thread OO concepts and relational databases

Data integrity is orthogonal to stored procedures and triggers

Depends on how you define data integrity. If I have a banking application where it should be impossible to update the value of the balance column of the current_account table without there being an appropriate entry in the debit or credit tables I would call it a data integrity issue, and enforcing it with stored procedures and/or triggers would seem a sensible option.

The table doesn't have a behavior that is associated with it. If anything, the behavior is associated with the action that triggers it, not the table the action was performed on.

I think I'll just have to say to-may-to / to-mah-to on this one. As far as I'm concerned when I do something like:

CREATE OR REPLACE TRIGGER enforce_business_rules AFTER UPDATE ON current_account ... etc ...

I'm doing something to the current_account table. YMMV :-)


Comment on Re^3: OO concepts and relational databases
Download Code
Re^4: OO concepts and relational databases
by dragonchild (Archbishop) on Aug 03, 2004 at 02:39 UTC
    Triggers do not have to affect the table that they trigger upon. For example, we have a trigger that will add rows to other tables when the given table is inserted into. That has nothing to do with data integrity and everything to do with business logic. (You even named your trigger accordingly.)

    In my opinion, you are confusing business rules and data integrity. Data integrity is a set of rules about the form of the data, based on how the data moves. Business rules have to do with things like you discuss. Credits, debits, and balances are a business rule. Data integrity would require that every credit and every debit have a valid FK to the current_account table. Whether or not they balance is an application issue.

    This is actually a pet peeve of mine. Putting business rules into the datastore limits your options. You end up not being able to do the following:

    • change datastores (Oracle -> MySQL, for example)
    • safely upgrade versions of your datastore. (Oracle, for example, has had issues between point releases of 9i.)
    • support more than one type of datastore (should this be needed)
    • allow more than one application to use your datastore
    Not to mention that it's a huge maintenance headache, having to support more than one language and/or needing to have a DBA do development.

    That last item may be a bit confusing, so I'll explain the reasoning behind it. Most databases begin life supporting one application, so there's a bit of myopia involved in the design, especially of the schema and supporting triggers / stored procedures. Some places put a lot of the business rules into the database.

    So, let's say you do that and your database has been very successful. Now, your manager / VP / whomever wants you to add another application. Say, a reporting application for the clients, over the Web. Not a problem. But, now they want you to add the capability to have the clients update the data. Oops!

    You now need to scour every single bit of code within the database to make sure that seemingly simple data changes that the web application wants to make won't adversely affect your database because every time you made that data change within the internal app, you always wanted to do XYZ as well. It's much simpler to have a webservices-type design with N tiers. That way, each tier does one thing, one thing only, and does that one thing well.

    ------
    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

      Triggers do not have to affect the table that they trigger upon. For example, we have a trigger that will add rows to other tables when the given table is inserted into.

      True, but I'm afraid I don't understand the point that you're trying to make.

      In my opinion, you are confusing business rules and data integrity. Data integrity is a set of rules about the form of the data, based on how the data moves. Business rules have to do with things like you discuss.

      I've failing to see the clear distinction that you appear to be drawing between business rules and data integrity. I've certainly been using the latter term in the sort of contexts I discussed previously for over ten years with nobody apparently getting confused ;-)

      This is actually a pet peeve of mine. Putting business rules into the datastore limits your options.

      I agree. Putting business logic in the database layer is often a mistake. The most common problem I see is splitting the same 'layer' of business logic between the database and non-database code, which gets you the worse of both worlds.

      That said, you fail to list the advantages. Putting at least the basic business rules in the database allow you to present the relation model to the application and be certain that a poorly coded application isn't going to foul up your data. Database-side logic can also be a lot faster since you don't have to throw stuff over the wire.

      While I tend to keep my business logic out of the database (because most of the projects I work on aren't of a sufficient scale to make it useful) it comes down to the project and the team. There are no hard and fast rules either way.

      You now need to scour every single bit of code within the database to make sure that seemingly simple data changes that the web application wants to make won't adversely affect your database because every time you made that data change within the internal app, you always wanted to do XYZ as well. It's much simpler to have a webservices-type design with N tiers. That way, each tier does one thing, one thing only, and does that one thing well.

      I don't understand the point you are making here.

        The point with the trigger is that the trigger isn't a behavior of the table itself. You cannot ask the table to do something, simply because it has a trigger on it. To have the trigger code execute, you must perform an action upon the table. The database engine, upon seeing that action performed upon the table, executes the code in the trigger. It's not a behavior of the table, but a behavior of the engine.

        Another way to put it would be this - if it was a behavior of the table, you would be able to move the table from one schema to another and the behavior would move with it. But, you can't do that in any RDBMS I've ever worked with.

        Another way to think about it would be this - if it was an behavior analogous to the OO sense of the word, it would only be allowed to affect its state and request that the objects it contained affect their state. But, most non-trivial triggers I've seen are meant to affect the state of other tables, given the new values in this table.

        As for stored procedures ... I can see advantages to putting code in stored procedures ...... if you're remediating an existing application and the current developers are dunces. Otherwise, why would you put the code in the database as opposed to putting in a middle-tier? You could even restrict access to the database to that middle-tier, forcing all access to be moderated the same way it would be moderated through the stored procedures. But, instead of having to program in two languages (one of which is going to be substandard), you would be able to keep all your code in one language (Perl, for example) and would not be tied to one datastore vendor.

        ------
        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

      Triggers are often used for data integrity to enforce 1 to 1 and 1 to 0 or 1 relationships between more than 2 tables.

      --Solo
      --
      You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (8)
As of 2014-09-19 10:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (135 votes), past polls