[thesite] relational integrity and the application layer
rudy
r937 at interlog.com
Tue Nov 28 21:12:50 CST 2000
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
More information about the thesite
mailing list