[thelist] This ISP has got the idea!!

[rudy]

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