I'm sure you'll get lots of replies to this, but here goes anyway.
Make it two tables. Your assumptions about why 2 tables would be bad are
mis-informed. You can still do one query (with a join clause). And, if you
index on the foreign key, the database will be able to retrieve the related
records without doing a full table scan.
Sample query:
SELECT a.word, b.link
FROM wordtable a, linktable b
WHERE a.primarykey = b.foreignkey
AND a.word = 'myword'
----- Original Message -----
From: "Roberto Perez" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, November 10, 2004 1:38 PM
Subject: Pros and cons of separate tables
> Hi everyone,
>
> I'd like to know your opinion on best practices regarding the use of
> separate tables/separate queries. Here's my situation:
>
> - I'm currently working on an online dictionary with CFML and an Access
> database. The dictionary is contained in 1 table called "words_tb". For
> each word/entry, there are 25 fields in that table ("url-1" through
> "url-25") that contain URL's that exemplify that word/entry. Thus, when
> the
> definition of a word is displayed, there are also a number of links
> displayed that take the user to websites dealing with that word in detail.
> In this implementation (only 1 integrated table), there's only 1 query,
> and
> only 25 links maximum returned, so results are displayed fairly quickly.
>
> - Now, with this scenario, I'm limited by the number of fields in that
> database: 25 links right now. I can always add more fields, but they will
> always be finite in number (and they would make the table bulkier and
> bulkier).
>
> - However, if I use a second table called "links_tb", with a primary
> key/unique ID that matches each word in the dictionary, they I could have
> virtually unlimited links for each word/entry, identified by a
> shared/common ID between the word and the link. The drawback is that now
> the search for links, instead of being applied to 25 fields, would be
> applied to potentially thousands of links to identify those which ID
> correspond to the word being consulted. And, now I would need 2 queries
> instead of 1.
>
> - My question: in this second scenario, each query to the dictionary would
> involve 2 queries to 2 different tables, the second of which (the
> "links_tb") could have potentially thousands of links to sort through.
> Would this unnecessarily delay the results? In other words, the word
> itself
> would be found fairly quickly, but the query to the "links_tb" table would
> be sifting through thousands of fields to find possible matches (as
> opposed
> to the 25 maximum fields currently used). Would this be a good tradeoff
> between speed/functionality? Or, would the delay in the "links_tb" query
> be
> minimal, so that performance wouldn't be an issue?
>
> Any suggestions, comments, ideas, and recommendations will be appreciated.
>
> Thanks in advance,
>
> Roberto Perez
> [EMAIL PROTECTED]
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183987
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
