Version two is what I'm describing. But, as I was saying, it can be complicated depending on whether or not you need reciprocal relationships.
As Claude said, in this case if your list of related ids is relatively small, and based on your comfort level with DBs, I'd say just do it the way you're doing it. ----- Original Message ----- From: "Stuart Kidd" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, November 16, 2004 9:28 AM Subject: Re: List Contains > My knowledge of databases isn't too deep. Am I right to say that i would > have a table called articleRelationship and it would contain something > like this: > > articleID - articleRelations > 1 3,14,33 > 2 3,4,12 > 3 11,1,2 > 4 7,8,5 > 5 7,8 > > OR... would there be a separate row for every relation. So if my > articleID was 4, it would search all records below and pull out > relationsIDs 1,2 and 5 and display all the articleRelations accept for the > number 4's? > > RelationID - articleRelations > 1 2,4 > 2 3,4 > 3 11,2 > 4 7,5 > 5 7,4 > > If i'm wrong on both of these I may just stick to the way i'm doing it so > i can be comfortable with the simple stuff first. > > At the point i am right now I have everything working with the way i am > doing it but i am having problems with the LIKE syntax of SQL. > > <cfquery name="relatedArticles" datasource="user020"> > SELECT articleID, articleTitle > FROM tbl_020articleDetails > WHERE articleID LIKE '#GetArticle.articleRelatedLinks#%' > ORDER BY articleDate DESC > </cfquery> > <cfoutput>Related: #GetArticle.articleRelatedLinks#</cfoutput> > <tr> > <td><cfif relatedArticles.RecordCount GT 0>Other articles that may > relate to this story are:<br> > <cfoutput query="relatedArticles">#articleTitle#</cfoutput> > <cfelse>NOTHING</cfif></td> > </tr> > > Thanks! :) > > > ---------- Original Message ---------------------------------- > From: "Deanna Schneider" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Tue, 16 Nov 2004 08:41:52 -0600 > >>Hm... I'd explore a recursive network data model. Essentialy, you'd have >>your articles table and another associated table (articlerelationship). In >>it, you'd store the pairs of related articles. The complicating factor >>here >>is that I don't actually know your business rules. The recusive network is >>generally used to show "parent-child" type relationships between two rows >>of >>data in the same table, but allows for multiple instances (unlike a simple >>recursive join). Your application code (and DB constraints) would be >>different depending on whether or not it's true that if author X says that >>Aricle A is related to Article B, then all other authors must agree that >>Article B is related to Article A. >> >>I tried to find a website that explains this concept, but failed. Sorry. >> >>Here's the gist of the table structures: >> >>ARTICLE >>articleid (pk) >>title >> >>ARTICLE_RELATIONSHIP >>parentid (pk references article.articleid) >>childid (pk references article.articleid) >><!--- You could have something here like "assigning_authorid" to let you >>know who ID'd the relationship if they're not reciprocal ---> >> >>Sample query to get article and related articles, if not reciprocal >>(Oracle >>syntax) >> >>SELECT a.title, a.articleid, r.articleid as relatedarticleid, r.title as >>relatedtitle >>FROM article a, article r, article_relationship x >>WHERE a.articleid = x.parentid (+) >>AND x.childid = r.articleid (+) >> >>This would get all the articles in the article table, and their associated >>articles, whether or not they had any associated articles. >> >> >> >>----- Original Message ----- >>From: "Stuart Kidd" <[EMAIL PROTECTED]> >>To: "CF-Talk" <[EMAIL PROTECTED]> >>Sent: Tuesday, November 16, 2004 7:32 AM >>Subject: Re: List Contains >> >> >>> Hi Deanna, >>> >>> This morning I just worked out what i had done wrong! It was that I had >>> been putting the <option value=#articleID#> and not <option >>> value=#authorID#> with my initial form. >>> >>> Each article when CREATED the user gets a chance to link to other >>> related >>> articles. Once these articles are selected (in the form) they get >>> inserted into a field called articleRelatedLinks as a list of the >>> articleIDs. When an article is displayed it checks to see which >>> articleIDs are related to the field articleReatedLinks and adds the >>> links >>> to the page. >>> >>> What is the normalised way of doing this? By a dynamic search? Excuse >>> my >>> limited knowledge. >>> >>> Thanks! :) >>> >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:184394 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
