[thesite] relational integrity and the application layer

[rudy]

i wanted to comment on something that was part of another thread, so i've
changed the subject line...

>> i can agree with constraints.  once the application is built, the
>> constraints don't really play much of a role as the application code
>> must be written to shield the user from the constraint errors
>> anyway.
>
>Right.  So to me, constraints don't really contain business logic.  At
>least not business logic that exists elsewhere.

i know i'm jumping in late here, but with several decades of database
experience, i just want to weigh in on the side that says constraints *are*
part of the application layer


real-life example from the evolt database:

    ratingvalue number(1)
            constraint checkrating not null
                 check (ratingvalue between 1 and 5)

another:

   alter table rating
       add (constraint oneratingperuserperarticle
             unique (userid,contentid))


i strongly disagree with the concept that "the application code must be
written to shield the user from the constraint errors anyway"

i think what happens is that in most cases, the application is written in a
manner that is *aware* of the constraints, but does not necessarily attempt
to *enforce* them in application code

for example, the way we take ratings on articles, there's a set of radio
buttons, and they correspond to values 1 through 5

note that the cf template that processes the form doesn't have to actually
check to make sure that the form field has those values (if it doesn't, the
form has been spoofed) -- that's done by the database

this also protects the database from invalid values being entered through
raw sql outside the application -- thus contradicting the idea that "once
the application is built, the constraints don't really play much of a role"


another consideration for letting the database do all the work (and not
repeating it in application code) is that if you name your constraints
well, and have a reasonable database error handling strategy in the
application code, the application will merely inform the user as to why the
action failed, using the database error message

okay, some databases just return a code (like db2, where the most common
error used to be -900, which meant "a database error occurred"), which
makes it a bit more challenging to tell the user

but why would you allow your programmers to reprogram a database error such
as

    attempted insert would violate
    constraint "oneratingperuserperarticle" --
    insert rejected

seems pretty clear to me


> Ideally, everything is documented.

tee hee    ;o)

my philosophy on documentation is another (large) thread altogether, but
basically, you should never write documentation that isn't stored inside
the thing it documents

so, if you want to find out what the database constraints are, just print
them   ;o)


> As an example, what kind of design documents do we have
> for the evolt structure?

i had a data model at http://r937.com/oracle4.gif

however, this is *not* current and i haven't done the new one yet

oh yeah, you're saying, how come this isn't documented inside the database?

well, it is, sorta -- but you need a $6000 piece of software like ERwin to
reverse-engineer the diagram

aside:  sql/server has a diagramming option that is really very good


> 2) Do not rely on Oracle as a database.  In theory our product can use
>   any database in the back without having to worry about converting
>   triggers and stored procedures to the database's specific language
>   and which features are supported.  For example, MySQL doesn't
>   support triggers at this point.

very good point, and an admirable criterion

i like your pragmatism


hey, even foreign key constraints are application layer, if you think about
it

suppose you have a parent table and a child table

most people are familiar with the "on delete" relational constraint -- if
you delete a parent row, you can designate what you want to happen to the
child rows, or whether you want the delete of the parent row to succeed

one option is to delete all child rows whenever the parent is deleted (the
"cascade"), and another option is to allow the delete of the parent *only
if* there are no child rows that refer to it, otherwise the delete is
disallowed (the "restrict")

but now here's another view of relational integrity

suppose you want to insert a child row

if you don't have a the parent for the child row, you can designate the
foreign key as nullable, in effect you allow the insert of the child with a
null foreign key to the parent

hypothetical example:  insert an article without designating a
centre/topic, which is added later by an editor

alternatively, suppose you do have a value for the foreign key, but let's
just say that it's for a parent key that is currently not in the parent
table

oh, this wouldn't be allowed to happen, you might think, but that's not
necessarily so -- it depends on the application!

one of the relational constraint options (in theory anyway, i don't see
this implemented in too many databases) is to have a parent row
automatically inserted if the child being inserted has a new value for the
foreign key, i.e. parent's primary key that doesn't exist yet

so the database just goes ahead and creates the parent row first, using the
value of the to-be-inserted child's foreign key, and then proceeds with the
insert of the child row

aside:  this strategy requires that all the parent's non-key fields are
either nullable or have a default value, but that's pretty normal

needless to say, you don't see this option in too many commercial
databases, yet it's just as much a part of relational integrity as "on
delete restrict" -- and if you need it, you have to build it

now, would you rather build it using triggers or force the application code
to do so?  and what if there are multiple application code modules that
insert child rows?  wouldn't they all have to have the same logic?  then
wouldn't you want to split that off into a called module or something?
sounds like a perfect place for a stored procedure or trigger to me...


when you consider all the options that relational integrity offers, despite
the fact that you do have to implement some of them in contemporary
databases using triggers or stored procedures, it's pretty clear that these
constraints depend very much on which child and parent tables you are
talking about

in other words, they are application layer


rudy.ca