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 ¡_{þÝ™9 > ÍÔ^U*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