I think the ideal situation would be to separate the linkage between the
sites, and their multiple descriptions.
For Example:
Here is your site master table:
SITE
====================
ST_ID ST_TITLE
----- --------
1000 Site 1
2000 Site 2
3000 Site 3
4000 Site 4
Now, your lookup table
OCCUPATION
==========================
0C_ID OC_DESCRIPTION
----- --------------
1 CARPENTER
2 GOVERNMENT
3 CONTRACTOR
4 CRAFTSMEN
Now, instead of having the site id column in the occupation table, you could
have a linkage table to associate "n" number of OCCUPATION references to a
specific site.
Here is the linkage table
SITE_OCCUPATION
==========================
ST_ID OC_ID
----- -----
1000 1
1000 2
1000 4
Now, to find out the occupations linked to a specific site, you could
SELECT OC_DESCRIPTION
FROM OCCUPATION
WHERE OC_ID IN (SELECT OC_ID
FROM SITE_OCCUPATION
WHERE ST_ID = #TheSiteInQuestion#)
I don't know if this is what you are looking for, but I hope it helps!
--
SCOTT VAN VLIET
SENIOR ANALYST
SBC SERVICES, INC
Tel: 858.886.3878
Fax: 858.653.6763
Email: [EMAIL PROTECTED]
-----Original Message-----
From: Tracy Smith [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 18, 2002 12:06 PM
To: CF-Talk
Subject: Lookup Tables
I am looking for suggestions/ideas on the best way to implement a set of
lookup tables. I've considered various options (putting all lookup
values in one table, managing multiple lookup tables). I'll try to
describe my scenario below. If you have any fresh ideas or suggestions
that have worked for you in the past, please either email directly at
[EMAIL PROTECTED] or send a reply to the list.
Scenario:
I have several lookup tables that will be shared between different
sites. I am using a site-id to identify each site. For some tables all
the sites will share the same lookup data. However, on some sites one
or two of the lookup entries might be different. Here is an example.
Occupation
ID SITEID DESCRIPTION
--- ------ -----------
1 1000 CARPENTER
2 1000 GOVERNMENT
3 1000 CONTRACTOR
Now, site 1000 uses Carpenter for the Description, but Site 2000 might
want it to say Craftsmen. So I would add another entry
4 2000 Craftsmen
The question is do I add additional entries to repeat Government and
Contractor for Site 2000 or do I store data for siteID in a list format
and use an IN query as listed here:
ID SITEID DESCRIPTION
--- --------- -----------
1 1000 CARPENTER
2 1000,2000 GOVERNMENT
3 1000,2000 CONTRACTOR
I have several lookup tables that that are very similar to this example.
Thoughts? Ideas?
Thanks -
Tracy
*******************************
Tracy Smith
TCS Technologies, LLC
Web Development & Programming
President
http://www.tcstech.net
Phone: (888) 318-9840
Fax: (202) 478-0897
Email: [EMAIL PROTECTED]
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
