You want to use an id/reference table anytime the information is the same
repeated multiple times in your database. I've used them for
occupations/jobsites/teachers/salesperson/team names/School names/Company
names/Doctors list/etc. Generally they will be used when:
a. info that generally doesn't change very often (ie: the example you had
was states was very appropriate- CA isn't going to suddenly decide to use CL
as an abbreviation).
b. are used in multiple reports/web pages/etc. ie: If I keep my business
address/phone info in a database table I could use that id to show the
business address info on each page of my paper reports/web site pages. But
if the company moved I would only have to change it one time- on my table
and all the changes would be reflected everywhere.
c. Anytime you have multiple users that are going to be retyping the same
information.
d. When you need to make sure that the information is accurate/consistent.
(ie: don't want my luggage sent to Charlotte NC instead of Charlottesville
VA)
e. Anytime you have many people to hook to the same information (ie 100
people have the same doctor/25 people are on the same team/1000 people have
the same occupation)
f. when you want to limit the choices that a person can add. (ie: Can't buy
a train ticket to Timbuktu unless Timbuktu is listed as having a train
station)
you are exactly right you do not want to go back into the database and
change all those rows for one thing. Plus you'll rarely get them all
because of misspellings/periods/different data entry standards/etc. (I
inherited a db that had US Army listed over 12 different ways- there was
U.S. Army/U. S. Army/US Army/United States Army/etc...Not a pretty sight,
considering that was one of the easier pieces to make consistent!!)
It's impossible for me to say when/when not to use one, but those are the
general situations. I think it's more obvious when not to use them. (ie:
it'd be impossible to use one for FirstName) But I'll tell you this much.
The more you use the reference tables/hook primaryid to other tables the
more you'll know when it's appropriate. ( I use them all over the place- for
everything that I can-that it is one of the first questions I ask myself
when designing ("Could that be a reference table?") It does take some time
to become intuitive about what is appropriate. But play with the reference
tables idea. Using the reference table primary key as link gets easier each
time you use it and can save you many of hours of search/find/replace.
Hope this helps and good luck.
-----Original Message-----
From: Pooh Bear [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 20, 2001 3:35 PM
To: CF-Talk
Subject: RE: Database design woes (me again!)
nooooo!!! u guys dont understand. the state thing was an example! it was
a design issue in general, and i wanted a general answer. when is it good
to use and id, and when is it good not to. it's good to use an id if the
information changes a lot, but it could be sitll linked by ID, and can be
shared by many. but, if all of the many used textual of actual information,
instead of getting it from a common source, I'd have a lot of toruble going
through all of those rows just to change one thing. understand what i mean?
hehe
>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: RE: Database design woes
>Date: Fri, 20 Jul 2001 14:22:59 -0500
>
>
>am i missing something?? isn't there something inherently wrong with using
>a state abbrv. as a primary key? as an index ok but primary key?
>uniqueness?
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
