-----Original Message----- From: Ashley Sheridan [mailto:ash@xxxxxxxxxxxxxxxxxxxx] Sent: Friday, April 23, 2010 11:16 AM To: tedd Cc: PHP eMail List Subject: Re: Remote Key Question On Fri, 2010-04-23 at 12:03 -0400, tedd wrote: > Hi gang: > > A few times I've found myself confronted with a problem that might be > better solved than the way I currently solve it. I would like your > opinions/solutions as to how you might solve this. > > Here's the given (as an article/author example). > > I want to create a list of articles in a database. > > The articles are listed in a table with the fields "title", > "description", and "author". > > article table: > id - title - description - author > > The authors are listed in a table with the fields "name" and bio". > > author table: > id - name - bio > > Now here's the problem each articles will have one, but perhaps more > authors -- so how do I record the authors in the article table? > > As it is now, I use the remote key for each author and separate each > key by a comma in the author field of the article table. For example: > > author table: > id - name - bio > 1 - tedd - tedd's bio > 2 - Rob - Rob's bio > 3 - Daniel - Daniel's bio > > article table: > id - title - description - author > 1 - PHP Beginner - Beginner Topics - 1 > 2 - PHP Intermediate - Intermediate Topics - 1,2 > 3 - PHP Advanced - Advanced Topics - 1,2,3 > > As such, article with id=3 has a title of " PHP Advanced" and a > description of "Advanced Topics" with tedd, Rob, and Daniel as > authors. > > Is there a better way to link multiple authors to an article rather > than placing the remote keys in one field and separating them with > commas? > > Cheers, > > tedd > > -- > ------- > http://sperling.com http://ancientstones.com http://earthstones.com > If you can change the authors table couldn't you add a article_id field to it? If not, or if an author may belong to more than one article (many to many) then a third table is the way to go, and use a couple of joins. A third table does have the added advantage that you might specify the type of author they were. For example: id author_id article_id type(enum maybe?) 1 1 1 main 2 2 1 co 3 1 2 main The third table is obviously more complex, but offers a better relationship model to be built between authors and articles. Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Agreed, It really depended if this is a permission based system or more info based. If its just for listing authors of an article, not letting them edit it, the my solution is best but if it was was editing I would go with Ash's approach maybe even making is so its something like ArticleParts: ID|ORDER|ArticleID|UserID|ENUM("OWNER","CONTRIBUTOR") So that the owner can edit any ArticlePart in their article but the CONTRIBUTOR can only edit their specific part. And ordery would tell you how to order the parts for final output. David -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
