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:183981
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
