Example of intermediate table in many to many relationship in MySQL

Опубликовано: 13 Апрель 2014
на канале: Jiansen Lu

13,377

Using pubmed database as an example.
Using Google Docs drawing to draw a UML diagram to demonstrate.

An article can have one or more authors and an author can be listed for one or more papers (many-to-many). The intermediate table "author_article" table is used to establish this relationship.

Command for Creating `author` Table
CREATE TABLE `author` (
`author_id` int NOT NULL AUTO_INCREMENT,
`author_name` varchar(100) NOT NULL,
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Command for Creating `article` Table
CREATE TABLE `article` (
`pubmed_id` int NOT NULL,
`article_title` varchar(100) NOT NULL,
`journal_title` varchar(100) NOT NULL,
`publication_year` date,
`article_abstract` text,
PRIMARY KEY (`pubmed_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Command for creating intermediate table `author_article`
CREATE TABLE `author_article` (
`author_id` int NOT NULL,
`pubmed_id` tinyint NOT NULL,
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Once relationships are stored, you can fetch data like below - See more at: Once relationships are stored, you can fetch data like below.
SELECT a.author_name, c.article_title FROM `author`
AS a LEFT JOIN `author_article`
AS b ON a.author_id = b.author_id LEFT JOIN `article`
AS c ON b.pubmed_id = c.pubmed_id;

More about this video:
http://jiansenlu.blogspot.ca/2014/04/...