Hi,
----- Original Message -----
From: "Hoffman, Geoffrey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 17, 2003 7:01 PM
Subject: Whats the best way to manage 'generic two-way relationships'?
> I couldn't think of a better term for what I want to do...
>
> I am building a CMS for news stories. Often news stories are related to
each
> other, or need to be grouped by content. I need a simple way to manage
> storyId relationships - in both directions.
>
> Thinking about the business logic to implement this is a bit confusing,
> because if you say story f is related to story b, and b is already related
> to c,d,e, then do I need aditional records for f-c, f-d, f-e? It gets
worse
> if story f is related to g,h,i, and k is related to l,m,n and then you
> relate f to k... then you'd have to insert additional 9 (or would it be
12)
> records for the cross-product of all combinations of all the
relationships.
> Or would you? And I can't think of how you'd handle this at the DB level
> (I'd have to loop in PHP and have 9 inserts?)
>
> My first thought was:
>
> CREATE TABLE storyRelationship(
> relId INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
> storyId1 INT UNSIGNED NOT NULL INDEX,
> storyId2 INT UNSIGNED NOT NULL INDEX
In my opinion, is not too well(i don't wish to said "it's a big mistake").In
this way you will not be able to have data flexibility.The cardinality of
your related stories will be 2 for ever.
I think you can solve your situation with 5 tables.
1 table for data attribute(category,....etc).CATEGORY
2 table for stories - i think in this way because you have 2 entities of
relations : TO_RELATION and FROM_RELATION
2 index tables : one table for every relation table which can contain
interchangeable ID's of stories:IDX_TO_RELATION which that contain ID's
from FROM_RELATION and IDX_FROM_RELATION which that contain ID's from
TO_RELATION
A little schema (please maximize):
FROM_RELATION(FIELDS:
IDFROM,IDCAT......)----> IDX_FROM_RELATION(FIELDS: IDFROM,IDRELATED....)
CATEGORY(FIELDS: IDCAT...)
TO_RELATION(FIELDS:
IDTO,IDCAT,.........)------>IDX_TO_RELATION(FIELDS: IDTO,IDRELATED)
relation schema for to_relation can be : CATEGORY.IDCAT->TO_RELATION.IDCAT
TO_RELATION->IDX_TO_RELATION.IDTO
IDX_TO_RELATION.IDRELATED->FROM_RELATIONS.IDFROM
..and for from_relation can be: CATEGORY.IDCAT->FROM_RELATION.IDCAT
FROM_RELATION.IDFROM->IDX_FROM_RELATION.IDFROM
IDX_FROM_RELATION.IDRELATED->TO_RELATION.IDTO
...it's very untested what i "drawing" here but i think it's a good start.
I hope it help
Best regards,
Gelu
> );
>
> so, for a given target story ID I would need a query that has something
> like...
> ... WHERE storyId1 = targetId OR storyId2 = targetId
>
>
> Another way I thought of has its own problems -
>
> CREATE TABLE storyRelationship (
> relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
> storyIdList TEXT
> };
>
> of which a record would look something like:
> relId 5
> storyIdList '46,59,89,94,213'
>
> but then I would need to do a lot of string parsing to get related
stories,
> and I have to believe that a query with a 'LIKE %id%' clause is going to
be
> a lot slower than one with an OR looking on two indexed rows.
>
> Any suggestions?
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
