Prev: creating a PHP wrapper script?
Next: Problem with pg_prepare function
From: "David Murphy" on 23 Apr 2010 12:41
-----Original Message-----
From: Ashley Sheridan [mailto:ash(a)ashleysheridan.co.uk]
Sent: Friday, April 23, 2010 11:16 AM
To: tedd
Cc: PHP eMail List
Subject: Re: [PHP] 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

First  |  Prev  | 
Pages: 1 2
Prev: creating a PHP wrapper script?
Next: Problem with pg_prepare function