oops small mistake in my prev post..
I meant to say (f -> b b-> c,d,e, K k-> l,m) in my example
NOT f->b,k b->c,d,e
lol.. got a little confused with all those insert examples ;)
Friday, January 17, 2003, 2:01:22 PM, you wrote:
> Geoffrey,
> Friday, January 17, 2003, 12:01:20 PM, you wrote:
>> 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.
> Their is no easy answer..
>> 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?
> This would depend on your business needs... if f relates to b.. do
> you assume c, d, e relations to f are equal to b's?? if so.. then
> inserting the extra rows could be valid. Or you could look at it as
> f relates to b.. b relates to c,d,e and NOT add the inserts then
> you could have Levels of relation (degrees of separation) and have a
> script that looks throught the heirarchy. You would have to watch for
> infinate looks as you could have an e relates to f row somewhere
> which would go on forever.
>> 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?)
> Having the extra inserts in the DB can be a pain.. BUT at runtime it
> will be faster as all the combinations are made already.. and you done need to
> parse the tree heirarchy of relations (f -> b, b -> c,d,e)
> but again.. all depends on how you want to treat things
> * extra rows: will speed up runtime usage.. but extra storage and
> inserts
> * parse heirarchy (recursive code): slower at runtime.. but you would
> not have to worry about maintaining the entire heirarchy in the db
> table
> think of it like this.. 9+ extra inserts when a story is saved.. OR a
> bunch of different SQL selects at runtime EVERY TIME the story gets
> views (if you were displaying related story info that is)
> // to get related articles without storing values
> select stroyid2 from storyRelationship where storyid1 = f
> then have to loop throught that resultset for each storyid2 to get
> what it relates to.. then loop through those,, and so on (recursively)
> Personally, i like the extra storage of the rows.. sql is easier
> (selects) and faster at runtime usually
>> CREATE TABLE storyRelationship(
>> relId INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
>> storyId1 INT UNSIGNED NOT NULL INDEX,
>> storyId2 INT UNSIGNED NOT NULL INDEX
>> );
> an example:
> I would add a separation_level INT UNSIGNED NOT NULL INDEX to the
> table. This would allow you to query and get everything that relates
> to f.. Directly.. or at any separation level. Then you build all the
> relationships like And yes, to build it you would have to do some
> recursive calls possible.. but at least only once for each save (not
> for every view)
> in your example (f -> b,k b -> c,d,e k - > l,m)
FIX!!: I meant to say (f -> b b- > c,d,e, K k-> l,m)
> relid stroyid1 storyid2 separation_level
> 1 f b 0
> 2 b c 0
> 3 b d 0
> 4 b e 0
> 5 f c 1
> 6 f d 1
> 7 f e 1
> 8 k l 0
> 9 k m 0
> 10 f k 0
> 11 f l 2
> 12 f m 2
> to get direct relations to f
> Select storyid2 from storyRelationship where storyid1 = f and
> separation_level = 0
> or remove the separateion_level = 0 clause.. and it will return all
> related stories reguardless of how.
> I would use the separation_level to at least Rank how important a
> story is related to f.. just order by seperation_level so direct relations
> are first..
> The only thing you loose by having all the compinations in the table..
> is HOW the relation was made... no way to tell that the reason f
> relates to c,d,e was because b related to them... If you use the code
> heirarchy parser, you could maintain that kind of info.. but you
> probably done need it i think (depends on what your business needs
> are)
> Brian
>> 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
> --
> Brian mailto:[EMAIL PROTECTED]
> ---------------------------------------------------------------------
> 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
--
Brian
Email: <[EMAIL PROTECTED]>
---------------------------------------------------------------------
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
