[thesite] relational integrity and the application layer

Dean Mah dsmah at home.com
Thu Nov 30 09:53:53 CST 2000

rudy writes:

> real-life example from the evolt database:
>     ratingvalue number(1)
>             constraint checkrating not null
>                  check (ratingvalue between 1 and 5)

These were the type of simple check constraints that I was referring
to before.  To me, these contain as much business logic as primary and
foreign key constraints and should be emulated in the application

> 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

Maybe it's because I come from a CGI with Perl background where you
should be de-tainting and checking every bit of external input for
robustness and security but I still like to do this in the application
layer as well as in the database layer (check constraints) and the
presentation layer (e.g. restricting input to certain values).

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

I'd agree.  And this is why I've argued for check constraints in my
current environment.  Although, none of our non-IT users would use
anything like SQL*Plus against the database.  We develop internal
tools to let them manipulate restricted sets of data.

> 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

Perhaps to you, but some of our users do not even have a high school
education.  My guess is that they wouldn't understand this or care.
We have different levels of error messages in our system.  For
example, when an error occurs, one message is set to the user and
depending on the severity, messages are sent to the bug-fix team, our
client services teams, network administrator, and/or logged to the
database or log file.

> 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

Check out Extreme Programming.

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

Actually, I was wondering what the database looks like but also where
all the application code lives and what it does and how it interacts.
For example, where is the static caching of the front page documented?
How is it done?  What code actually does it?  What is the caching
frequency?  Does it cache automatically when new content is submitted?
For a new person coming on, they'll have these questions and I don't
think that they should have to bug the developer to find the answer.
The developer may have since moved on and nobody has the knowledge of
how it works without having to dig into the system.  (That's the
problem we face at my current job.)

> 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

This kind of scares me.  Allowing the database to grow on its own.
Take on a like of its own.  Ewww... :)  I'd be worried about creating
intelligent defaults in the parent table.  If the tables were used for
any kind of reporting before the parent table was filled in
appropriately, it could lead to confusion.  Just a gut feel though.

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

That's where we differ.  Sounds like a library to me.  Separation and
common code.  I know that you are arguing that triggers and stored
procs can be seen as part of the application layer but I'd like to see
a greater separation.  A difference in philosphy.  FWIW, I never
though this way in my previous life as an Oracle DBA but now that I am
a developer....  Maybe I'm just a control freak.


More information about the thesite mailing list