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

Yes. You’re looking at a “has and belongs to many” (HABTM) relationship. — Martin Bean, Aug 02 '13 at 00:21

Filipe Silva · Accepted Answer · 2015-06-16T08:40:55.437

8

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.

edited Jun 16 '15 at 08:40

answered Aug 02 '13 at 00:16

Filipe Silva

21,189
5
53
68

This looks like exactly the thing I need to do. Thank you for being so detailed. I was just expecting someone to link to a tutorial! I'll have a play around with this and see if I can get it working. Thanks – user2574794 Aug 02 '13 at 00:27
No problem. If you make it work and feel that this answered your question, don't forget to mark it as accepted. Cheers. – Filipe Silva Aug 02 '13 at 00:30
Great! Glad it worked out and even better that you understood how it works – Filipe Silva Aug 02 '13 at 23:30
Just so you know, this type of table can go by several names: Associative table, Junction table, Linking table, Cross-Reference Table. – Sam Sep 05 '13 at 16:59
Shouldn't the second foreign key be 'park_id'? – Radu Andrei Jun 16 '15 at 08:09
@RaduAndrei. yep. you're right. i fixed that. thanks – Filipe Silva Jun 16 '15 at 08:41

pulldownc · Answer 2 · 2013-08-02T00:32:29.897

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

Thank you, this has helped explain it. I will play around and see if I can make it all work! Thanks — user2574794, Aug 02 '13 at 00:28

Many to many database design, third table?

2 Answers2

Linked