MySQL - set of all integers. . .

[Anon]

Hello.

I have a database of newspaper articles. How do I deal with more than one author per article? ("Authors" is a seperate table).

I want it to be able to handle an unlimited number of authors writing an article, eg. the set of all positive integers, since those are the primary keys of the Authors table.

OH NOOOOOOOOOOOOOOOOO. I saw the button which says "Please do a search BEFORE posting" and so did so, and got an answer.

Ewwwwwww. Have to make a new table which relates articles and authors.

It hurts my brain.

Like I said: ewww. Is there any way to do this a little more abstracted?

Thanks.

[hotspur]

I'm not sure what "Is there any way to do this a little more abstracted?" means???

However, the answer to your question is that you will need three tables
Article - probably keyed on Article_number
Author - again keyed on Author number

and a link table Article/Author with two columns Article_number and author_number

This is the only way to normalize a one to many relationship

Mark

[[deleted]]

I suppose there will be a few authors who write several articles too, so it's a many-to-many relationship.

What you'll end up with is a table containing the articles, a table containing the authors, and a table containing only the links between the articles and te authors.

So every time you want to add an author to an article, you add one record to the "xref" table, containing the ID of the article and the ID of the author.

When you want to get the data back out you can do a JOIN query like this:

SELECT *
FROM articles, authors, xref
WHERE articles.id=10
AND xref.authorid=xref.articles.id
AND authors.id=xref.authorid;