Re: Just one more anecdote

From: Kenneth Downs <knode.wants.this_at_see.sigblock>
Date: Thu, 04 Aug 2005 18:24:42 -0400
Message-Id: <m3has2-o96.ln1@pluto.downsfam.net>

Hugo Kornelis wrote:

> On Thu, 04 Aug 2005 17:08:02 -0400, Kenneth Downs wrote:
>
> (snip)

>>> ORM is an analysis method. But the completed ORM model holds *MORE*
>>> information than can be mapped into tables. An ORM diagram maps to
>>> tables, primary key, unique, check and foreign key constraints, plus a
>>> bunch of constraints that can't be directly implemented in any RDBMS
>>> that I know of.
>>
>>If it is true that they cannot be implemented in the the DMBS then I
>>reply:
>>
>>1)  not interested, as that creates a DB that cannot defend itself from a
>>misbehaving client, and

>
> Hi Kenneth,
>
> Note that I typed "can't be DIRECTLY implemented". With triggers and
> other vendor-specific extensions, anything is possible.

OK, then I missed your point. I myself use triggers mostly to implement biz rules, but i try to avoid vendor-specific extensions unless they have functionally equivalent analogs in the other big products.

So if you mean that the final built database will express all constraints from the ORM, we probably are in agreement on the goal here and can drop this line.

>

>>2)  overly complex constraints are usually the sign of tables that are too
>>sparse.

>
> I wasn't referig to overly complex constraints. Unless you think that
> equality constraints, exclusion constraints and disjunctive mandatory
> role constraints are overly complex.
>
> These constraints can all be represented in SQL by a CHECK constraint,
> but only if the fact types the connect to will all be grouped into the
> same table. If they are spread out over different tables, you'll need
> CREATE ASSERTION (defined in SQL-99, if I recall correctly, and not
> avaialble in all major RDBMS's). For the equality constraint, you'll
> need deferred assertion checking as well.

see my next note below.

>

>>Also, if it is true that there are elements beyond tables and primary and
>>foreign keys, then I would suggest that systematic treatment of automation
>>would finish the picture and allow the entire application to be specified
>>in terms of tables.

>
> I'm not sure if I interpret you correctly. If you propose to add some
> symbols, notes, or whatever to the tables you've drawn to show the
> constraints that are not available as standard constraints in SQL, then
> this would indeed allow the entire application to be specified in terms
> of tables (plus extra symbols and notes).

Actually I mean calculated values or automated cascades into other tables.

Many people reject this approach because it is denormalized, but I respond that the purpose of normalization is to enforce integrity, and if you divide the world into externally supplied data and calculated data, then normalization is for externally supplied data and if calculated data cannot be updated from external sources then it will be correct.

Several kinds of automations copy values from one table to another, so that I always express my constraints as comparisons of columns in a single table. (Hence no need to express constraints in terms of other tables).

When automation is taken into account, it is not so much that we draw symbols on the chart, but that we add more columns and then use them to express constraints. This has the really cool effect of making the constraints understandable to mere mortals, who can read the data dictionary, look at the columns, and then work out why the data was rejected.

Also this is why I say automation allows me to specify the entire application in terms of what goes into the tables, and as i find myself saying quite a bit lately, if I can do that, why do anything else?

>

>>And finally of course is the general rule that one should never design
>>tables on object-oriented principles, anymore than one designs women's
>>clothing on male models.

>
> Despite it's name, I wouldn't qualify ORM as being object-oriented.

As you've described it it sounds more like the UML class diagrams. In fact, it is getting dangerously close to mere table design :)

But seriously, when the class diagrams or the ORM pictures can completely express the situation in terms of columns and tables, one wonders why we don't just specify the columns and tables.

>

>>In my experience when a programmer is doing the job it is the table
>>definitions that are incomplete, usually because the programmer does not
>>think in terms of tables and can't wait to start coding.  Such programmers

> (snip)
>
> I know. One of my jobs has had me standing in front of a class of such
> programmers, trying to teach them to first create a complete data model
> in (a variant of) ORM, then map that to the corresponding datamodel in
> RM (but including the extra symbols and notes, to ensure no data from
> the previous step is lost) and only then start building.

Good luck with those students :)

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

Received on Thu Aug 04 2005 - 17:24:42 CDT