If your DB allows constraints to be function calls, then how about setting
up a generic values table and using a stored procedure to validate, e.g.
CheckValue ('TABLE', 'COLUMN', mycolumn)
the stored procedure would access the table 'GENERIC_VALUES' which has a
structure like:
table_name,
column_name,
value
with multiple rows for each table_name/column_name combination, e.g.
Row 1
table_name => 'employee'
column_name => 'gender'
value => 'male'
Row 2
table_name => 'employee'
column_name => 'gender'
value => 'female'
Row 3
table_name => 'employee'
column_name => 'gender'
value => 'not given'
Could also fire this off a trigger if you can't make the call in the
constraint.
--
Paul Wakefield
All opinions expressed herein are those of the author and not of The Board
of Executors
> -----Original Message-----
> From: Ken M. Mevand [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 12, 2000 10:09 AM
> To: 02 cf-talk
> Subject: [cf-talk][OT] : Database Theory
>
>
> this is a question of db design.
>
> suppose i have a field in a table that can contain only 3 values, eg.
> Membership type - 1=Ordinary, 2=Associate and 3=Founding. I
> would like to
> check how you ensure that it contains only these values.
>
> i see that there are 3 methods:
> a. constraint it by foreign key. meaning there is a table
> with only 3 rows
> of data,
> b. stored as an integer value (1, 2 or 3), use check
> constraint to enforce
> it.
> c. stored as a string ("ordinary", "associate" or
> "founding"). use check
> constraint to enforce it.
>
> i prefer method (a) for this reason : there is no need for
> the CF templates
> to interprete the meaning of the values. however, it seems
> quite silly to
> have a table with 3 rows on data only. but there are also advantage of
> future expansion. but what about gender which has only 2
> possible value (at
> least in the foreseeable future).
>
> what's the general opinion on this? thanks.
>
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
