Re: Remote Key Question

On Fri, Apr 23, 2010 at 12:03 PM, tedd <tedd@sperling.com> 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 > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Well, because each author can have multiple articles and each article can have multiple authors, the many-to-many relationship can use a junction table: http://en.wikipedia.org/wiki/Junction_table In this case articles_authors. Adam -- Nephtali: PHP web framework that functions beautifully http://nephtaliproject.com