One way to solve this problem is this:
Have a reference table "States" with the abbreviations etc.
Have your Friends table but have the state field be a text field. Thus, the
state abbreviations will be inserted into the Friends table as text ie 'WA'
Washington secedes, your Friends table will still resolve and you can remove
Washington from the states table.
Also, instead of just a States reference table, you could have a ref table
with all of your fields that are referenced ie, states, menu items etc.
Create a table ref_tbl
field_name
description
field_value
table_name
That way you wont have a ton of reference tables in your db to confuse
things.
John Anderson
-----Original Message-----
From: DeWitt, Steve [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 20, 2001 12:09 PM
To: CF-Talk
Subject: RE: Database design woes
If a state secedes then we will have more to worry about than whether or not
to use a code table for states.
To answer your question a code table is good for other reasons. One being
you could then use those values elsewhere in the same application and you
don't have to duplicate data.
-----Original Message-----
From: Pooh Bear [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 20, 2001 11:26 AM
To: CF-Talk
Subject: Database design woes
hi all, it's me again, hehehe :). well, i've come across many moments where
i wonder whether i should use an ID, or the actual information. here's an
example. I have two tables, one called STATELIST, and one called Friends.
The statelist table contains the state abbreviations along with an autoid.
The friends table just contain a name field, and (this is where i dunno what
to do) a state_id field.
in the front end, a friend enters his name, and selects the states he lives
in from a droplist (populated from the statlist table). What I am wondering
is, should i use the state's id, instead of actual text? when do i use it,
and when dont i use it? my initial reason of using an id was that it would
search faster since it is an integer and not regular text. but i do come
across times like these and dont know whether to use text or id. i mean what
if one state secedes?? i will have to delete the state from the table, and
the state ids that my friend have would have no state to reference to. But,
if i use text instead, the information is still there.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
