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