Hi Roberto,
Your main drawback here is going to be the use of Access, not the additional
time required to return the results. A database server (MS SQL, MySQL, etc.)
would improve your reponse time, but if you don't have hundreds or thousands of
people hitting your application, Access should be OK.
There are no drawbacks to separating the tables. Building relational databases
is a best practice and the definition of a relational database is "a collection
of data items organized as a set of formally-described tables from which data
can be accessed or reassembled in many different ways without having to
reorganize the database tables." Therefore, splitting your links out into a
separate table and joining the tables in your select statement is the way to go.
You wouldn't need two queries for this though.
Table One would look like:
ID | Word
Table Two would look like:
ID | WordID | URL
You'd then write a sql statement like:
select words_tb.word, links_tb.url
from words_tb, links_tb
where links_tb.wordid = words_tb.id
You'd get all the results you're looking for in a single query.
Hope this helps,
Jon
>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:183985
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
