Re: [PHP] Remote Key Question
On Fri, Apr 23, 2010 at 12:36 PM, David Murphy 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:simples...@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 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
RE: [PHP] Remote Key Question
-Original Message-
From: Ashley Sheridan [mailto: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:
idauthor_idarticle_idtype(enum maybe?)
1 11 main
2 21 co
3 12 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Remote Key Question
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
RE: [PHP] Remote Key Question
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:simples...@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 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Remote Key Question
Personally I would make -Original Message- From: Adam Richardson [mailto:simples...@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 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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: idauthor_idarticle_idtype(enum maybe?) 1 11 main 2 21 co 3 12 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
Re: [PHP] Remote Key Question
On Fri, Apr 23, 2010 at 12:03 PM, 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 > > -- > 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
[PHP] Remote Key Question
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
