Try the following schema:
CREATE TABLE Reporter
(
reporter_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, first_name VARCHAR(20) NOT NULL
, last_name VARCHAR(20) NOT NULL
, PRIMARY KEY ( reporter_id )
);
CREATE TABLE Publication
(
publication_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(50) NOT NULL
, PRIMARY KEY ( publication_id )
, UNIQUE KEY ( name )
);
CREATE TABLE Publication2Reporter
(
publication SMALLINT UNSIGNED NOT NULL
, reporter SMALLINT UNSIGNED NOT NULL
, PRIMARY KEY (publication, reporter)
);
CREATE TABLE Article
(
article_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, reporter SMALLINT UNSIGNED NOT NULL
, publication SMALLINT UNSIGNED NOT NULL
, title VARCHAR(200) NOT NULL
, content MEDIUMTEXT NOT NULL
, PRIMARY KEY ( article_id )
, KEY ( reporter )
, KEY ( publication )
);
I've done a couple things of note:
1) I named the newspaper table Publication, so you can add magazines and other publications instead of just newspapers (really, just to keep the semantics accurate)
2) I created a many-to-many relationship between the Reporter and the Publication entity to account for situations where a reporter might (possibly simulataneously) write for more than one publication.
3) The Article table contains foreign keys to both the Publication and Reporter entities.
4) Example of finding all reporters who work for Publication 123:
SELECT
r.first_name
, r.last_name
FROM Reporter r
INNER JOIN Publication2Reporter p2r
ON r.reporter_id = p2r.reporter
WHERE p2r.publication = 123;
5) Example of finding all publications that a reporter with ID of 76 works for:
SELECT
p.name
FROM Publcation p
INNER JOIN Publication2Reporter p2r
ON p.publication_id = p2r.reporter
WHERE p2r.reporter = 76;
6) Example of finding all articles from all publications that report with ID of 76 works for:
SELECT
p.name AS 'Publication'
, a.title AS 'Article Title'
, a.content AS 'Article Content'
FROM Publcation p
INNER JOIN Publication2Reporter p2r
ON p.publication_id = p2r.reporter
INNER JOIN Article a
ON p2r.publisher = a.publisher
WHERE p2r.reporter = 76;
hope this gets you started...
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com