Many to many database design, third table?

Question

I am a beginner with MySQL and so far I have only had to work with 'many to one' table links. I now have a need for a 'many to many' link but am unsure how to do it. As far as I understand I would need a third table.

Basically I have a table full of parks and a table full of links to articles. On the webpage that shows an individual parks details I need a query looking for any attached articles to that park. Usually I would use a park_id column in the other table and link using that but the problem here is each article could be linked to many parks.

Example query would be:

SELECT * FROM tpf_features  
Where park_id = 7

But an article may have park_id's of 3, 7, 13, 23.

Can someone point me in the right direction to build this relationship and query up correctly.

Thank you

Answer 1

You should use a third table and associate their id's: (created the tables only with id's)

Create table parks( 
   park_id integer,
   primary key (park_id)
);

Create table articles(
   article_id integer,
   primary key (article_id)
);

CREATE TABLE cross_table(
   article_id integer,
   park_id    integer,
   Primary Key (article_id,park_id), 
   Foreign Key (article_id) REFERENCES articles(article_id),
   Foreign Key (park_id) REFERENCES parks(park_id)
);

Then when you want to find out information about the articles associated with the parks, you'd do:

SELECT a.* 
FROM cross_table c, articles a
WHERE c.article_id = a.article_id
AND c.park_id = 7;

This will return all the information about all the articles related to park_id = 7.

The primary keys, insure that every article has a unique id, every park has a unique id and they only relate once in the cross_table.

Answer 2

Indeed, you will need a third "linking" table that should contain only two columns: park ids in one, and article ids in the other. Although these ids are likely unique primary keys in their respective original tables, they need not be unique in the linking table.

This third linking table allows many occurrences of ids from each original table without conflicting with their uniqueness in those original tables. While each individual id many occur many times in the linking table, each combination of park/article will only occur once.

Then, in order to select all of the articles that relate to park 7, you need only select all of the rows from the linking table where park_id = 7

SELECT * FROM linking_tbl
WHERE park_id = 7