Re^2: OO concepts and relational databases

[dragonchild]

Maintaining data integrity can hardly be called "syntactic sugar"! (and what else are we to call stored procedures, triggers, etc. if not behaviour?)

Data integrity is orthogonal to stored procedures and triggers. Data integrity is maintained by normalization and foreign keys¹. Triggers and stored procedures are the ability to use a programming language that is supported by the database engine. They are not necessary for data storage or to maintain data integrity. In fact, the entire idea of stored procedures is marked as "implementation-specific" in the ANSI-92 standard.

Now, triggers can be used to help maintain data integrity. The most obvious case is if you have a schema that has been deliberately denormalized for performance reasons. If you make a modification to table A, table B might need to be updated in an atomic fashion, to maintain the denormalization. However, that is outside the basic functions of an RDBMS.

As for triggers and stored procedures being behaviors ... I still maintain they are not truly behaviors, in the OO sense. 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.

Another item to note is that certain things that require triggers and sequences in one RDBMS (Oracle) can be done with a set of keywords in another (MySQL). I'm speaking of the AUTO_INCREMENT keyword in MySQL that can only be duplicated by using a trigger and a sequence in Oracle. The keyword may be implemented as such under the hood, but it also may not. Regardless, it's still syntactic sugar.

1. Granted, most RDBMSes implement foreign keys as triggers, but that doesn't mean that the triggers are necessary, or even sufficient, for foreign keys. MySQL, for example, requires that the referenced column have an explicit index on it (in the InnoDB tabletype), while Oracle does not.

[adrianh]

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

[dragonchild]

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.

[adrianh]

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.

[Solo]

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