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