Remote Key Question
in [General]
|
From: tedd on 23 Apr 2010 12:03 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
From: Adam Richardson on 23 Apr 2010 12:08 On Fri, Apr 23, 2010 at 12:03 PM, tedd <tedd(a)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
From: Ashley Sheridan on 23 Apr 2010 12:16 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
From: "David Murphy" on 23 Apr 2010 12:26 Personally I would make -----Original Message----- From: Adam Richardson [mailto:simpleshot(a)gmail.com] Sent: Friday, April 23, 2010 11:09 AM To: tedd Cc: PHP eMail List Subject: Re: [PHP] Remote Key Question On Fri, Apr 23, 2010 at 12:03 PM, tedd <tedd(a)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
From: Ashley Sheridan on 23 Apr 2010 12:33
On Fri, 2010-04-23 at 11:36 -0500, David Murphy wrote: > GRR I hate outlook veruses Trillian One's an email client and one's a messenger client, I don't get where your vs problems are? :p Thanks, Ash http://www.ashleysheridan.co.uk |