On Fri, Apr 23, 2010 at 12:36 PM, David Murphy <david@xxxxxxxxxxxxxxxxx>wrote:
> GRR I hate outlook veruses Trillian
>
>
>
>
> Personally I would make
>
>
>
> Author:
> Id|Name|Bio
>
> Article:
> Id,title,desc,authordata
>
>
> So then I can do things like
>
> Select Articles.Title, article.Description,(select GROUP_CONCAT(Name) from
> authors where authors.ID IN Articles.AuthorData) as Authors from Articles
> where Articles.ID=XXX
>
>
> Then php could $tAuthors=explode(",",$row['Authors']); and pass that into
> smarty or whatever for the view portion of the app.
>
> I just say this because Junction tables really don't save you much and
> infact this it's very clear what your doing.
>
> David
>
> -----Original Message-----
> From: Adam Richardson [mailto:simpleshot@xxxxxxxxx]
> Sent: Friday, April 23, 2010 11:09 AM
> To: tedd
> Cc: PHP eMail List
> Subject: Re: Remote Key Question
>
> On Fri, Apr 23, 2010 at 12:03 PM, tedd <tedd@xxxxxxxxxxxx> 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
>
>
There is a trend towards denormalization (see BigTable, SimpleDB, etc), and
generally, I'm an advocate of the phrase "Normalize until it hurts,
denormalize until it works."
However, part of the question is how does Tedd want to be able to query the
data. If it's possible that he wants to query from the authors table to the
articles table (e.g., select all of the articles that Richard wrote), a
junction table gets much more useful (and easy relative to the denormalized
approach), especially as the queries increase in complexity.
So, Tedd, I'd carefully evaluate your expected query needs, and if they're
basic, your current scheme would work, but I suspect a junction table would
be better in this particular case.
Happy coding,
Adam
--
Nephtali: PHP web framework that functions beautifully
http://nephtaliproject.com
