Roberto Perez wrote: > At 02:11 PM 11/11/04, Jonathan Bigelow wrote: > > >> Building relational databases is a best practice [snipped] >> >>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 > > > Thanks for the answer, Jon. Just to confirm: > > - the two ID fields should be autonumber, right?
Taking Jonathan's schema, ID in both are autonumbered, and WordID is a numeric field. > - the relation should be between the "ID" field of the first table and the > "WordID" field of the second table, right? Yes, a one-to-many relation. > Just a thought: I understand how relations between fields are useful in > Access when you use Access exclusively to enter or search for data (e.g., > through an Access form). However, if you are accessing data only via SQL > statements with CFML, do the tables still need to be linked (relational) in > Access? You don't have to, but it's a good idea, as when you're working with Access Basic or VB. > Wouldn't you get the same results with two independent tables > (i.e., not relational) that are updated, inserted to, and read via CFML > using CFTRANSACTION (to do consecutive tasks like getting the word ID and > then updating/inserting in the second database)? On a simple schema like that, any UPDATE or INSERT actions that you'd want to perform are going to be naturally atomic anyway, so it's fine. The one place you might need to use a transaction is where you're deleting a word, but in that case, you can set up the relation so that when you delete a word, any related tuples in the links table are deleted. -- Keith Gaughan, Developer Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland http://digital-crew.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:184038 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
