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