I like method 2 myself...but you have to be sure you index the lookup types properly, and rather than having text values for the lookup types, I prefer to have another table for lookup types with an identity for the foreign key relationship in the lookup table. Obviously, some will argue that doing it this way could cause performance issues if the table grows too large, but on the other hand, I think cod writing is much easier, especially if you've got a lot of lookup tables.
Allen -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Phillip Senn Sent: Wednesday, February 08, 2006 10:19 AM To: [email protected] Subject: [CFCDev] So many lookup tables, so little time I consider this to be a classic question. Q: Should I have: 1. A bunch a little bitty lookup tables (averaging 2-10 dropdown values), or 2. One big Control table that has a LookupType field? The Argument for #2 is there are fewer tables in SQL Server, ergo a lot less code needed form maintenance. The Argument for #1 is that if you have one large Control table, then you are in effect writing your own administrative facility, which is the whole purpose of Enterprise Manager/Query Analyzer. I pose this question to the group because to me, the structure of the tables probably maps directly to the object model. After all, the purpose of the database is to simply provide persistence for the objects, no? So which do you prefer: 1. SELECT * FROM TableA SELECT * FROM TableB SELECT * FROM TableC 2. SELECT * FROM Control WHERE LookupType="TableA" SELECT * FROM Control WHERE LookupType="TableB" SELECT * FROM Control WHERE LookupType="TableC" ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected]
