[thelist] This ISP has got the idea!!
rudy
Rudy_Limeback at maritimelife.ca
Wed Oct 18 14:15:46 CDT 2000
> ... Or all other common sense warnings on stuff
> that are there because some moron used it stupidly.
hi ron
right on -- see http://www.geocities.com/b120282/pictures/motorola.jpg
not my site, it's part of 3bp.com -- sorry, i can't help it, i think that
site is a scream, i'm having a hard time stifling my guffaws here in my
cubicle... slow day at work, i guess
okay, now i owe for sure
<tip type="data design" synopsis="use check constraints wherever
practical">
the database will *always* execute check constraints faster than if you
perform the same action in program code
example of a check constraint --
create table employee
( emplid integer
, emplname varchar(100)
, emplstatus char(1)
constraint valid_emplstatus
check (emplstatus in ('F','P','C','I')) )
if you name the constraint, the database will use the constraint name when
sending you an error message, along the lines of
"...constraint valid_emplstatus violated; UPDATE action cancelled"
note that there are THREE places where you can check for valid data --
most people are familiar with client-side and server-side, but there is
good server-side and better server-side...
1. client-side script (e.g. javascript)
if it's a dropdown select list option, the chance of it being invalid is
slim, so often you don't bother validating these types of fields
client-side, perhaps just fields where the user types in the values
2. server-side script (e.g. cf, asp, php)
for example, you often see logic like
<cfif form.emplstatus is "F">
or form.emplstatus is "P">
or form.emplstatus is "C">
or form.emplstatus is "I">
<!---process the transaction--->
<cfelse>
<!---send an error message--->
</cfif>
which is not bad (at least it ensures that bad data doesn't get into the
database) but it can be a pain to maintain -- okay, maybe not for fields
that will "never" change, but we've all worked on those, right?
3. server-side in the database
our emplstatus example now requires no "if" logic at all --
<!---process the transaction--->
this is guaranteed to run faster (it is "compiled" into the database
definition of the table) to say nothing of being easier to maintain --
just think of the code you would have to modify if you suddenly want to
allow another value of emplstatus and you had the values coded into CFIF
statements...
by using a constraint, you define and maintain the constraining values in
the database, and never have to touch the code again
did i mention it executes faster?
"but what about the error message, i don't want some butt ugly database
error message displayed on my web page," i can hear some of you
complaining
yes, that is why you need to use descriptive error messages, which you get
simply by naming your constraints properly
</tip>
[homer voice]: mmmmmmmmm, check contraints....
rudy
r937.com
More information about the thelist
mailing list