INNODB: Create a many to many relationship between mysql tables

Question

I have 2 tables, articles and entities table. An article can have many entities and an entity can belong to more than one category.

What would be the sql to create such an association in mysql such that I can insert an entity to the entities table via the articles table and be able to query for the article from the entities table.

The entity only has 2 fields, entity_id and entity while the articles table has 3 fields: article_id, url and category

Is this what I require to do

CREATE TABLE articles(
  article_id  INT NOT NULL AUTO_INCREMENT,
  url VARCHAR(255),
  category VARCHAR(255),
  PRIMARY KEY(article_id)
)ENGINE=INNODB;

CREATE TABLE entities(
  entity_id  INT NOT NULL AUTO_INCREMENT,
  entity VARCHAR(50),
  PRIMARY KEY(entity_id)
)

 CREATE TABLE relationships(
   relationship_id INT NOT NULL AUTO_INCREMENT,
   article_id INT,
   entity_id INT,
   PRIMARY KEY(relationship_id),
   FOREIGN KEY(article_id) references articles(article_id),
   FOREIGN KEY(entity_id) references entities(entity_id)
 )ENGINE=INNODB;

This is your answer: http://stackoverflow.com/questions/16323676/referencing-a-composite-primary-key — J.S. Orris, Mar 22 '14 at 05:50

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

You need a third table that tracks the relationships between entity_id and article_id. When you want to make or change a relationship, you update this table.

[edit] This stackoverflow question may help you to understand, especially the most upvoted answer.

edited May 23 '17 at 12:11

Community

1
1

answered Mar 21 '14 at 22:33

KathyA.

671
6
14

I'm a ruby on rails developer so I'm new to sql since in rails we are abstracted when working with the database. For example what would be the sql to create that table or what field should it have? – Mutuma Mar 21 '14 at 22:38
Check the link I posted. It shows an example of creating a similar table and what fields it needs. – KathyA. Mar 21 '14 at 22:43
I noticed according to that I have to have an entity_id field on my articles table too. Is that really necessary? – Mutuma Mar 21 '14 at 22:58
No. Leave your two tables the way they are, and add the third table with just the two fields I described. – KathyA. Mar 21 '14 at 23:01
I have done that but now what would be the sql for like inserting data into entities using an article_id via the relationships table – Mutuma Mar 21 '14 at 23:31
I believe you only need article_id INT and entity_id INT. These will be your FKs that will both make up the composite key (which is 2 FKs that make up one PK) – J.S. Orris Mar 21 '14 at 23:31
@Jeff ... check the comment above. How would you do an insert query into entities? – Mutuma Mar 21 '14 at 23:36

J.S. Orris · Answer 2 · 2014-03-22T06:08:04.157

0

First off, your bridge entity [relationships] is not scripted correctly...it should be the following:

CREATE TABLE relationships (
article_ID INT NOT NULL,
entity_ID INT NOT NULL,
PRIMARY KEY(article_ID, entity_ID),
FOREIGN KEY(article_ID) REFERENCES articles(article_ID):
FOREIGN KEY (entity_ID) REFERENCES entities(entity_ID);

However, I don't know exactly what you want to INSERT.

edited Mar 22 '14 at 06:08

answered Mar 22 '14 at 05:35

J.S. Orris

4,653
12
49
89

Check the following link for a good explanation: http://stackoverflow.com/questions/16323676/referencing-a-composite-primary-key – J.S. Orris Mar 22 '14 at 05:46
What is your dependent table so I can advise on what attributes to add to your bridge entity? – J.S. Orris Mar 22 '14 at 06:08

INNODB: Create a many to many relationship between mysql tables

2 Answers2