insert multiple authors only if it exists in MySQL [closed]

Question

I have a problem . I am doing a PHP -My SQL project . I got to store author/s of a conference paper in a database . But sometimes its just 1 and sometimes it can be 2 or 3 or 10 . So how do i store this without creating rows like author1 ,author 2, ... author n . As for each conference papaer the number of authors will be different .How do i design the table , please help

conference paper table

authors table

Answer 1

As this is a m:n relationship you'd need an intermediate table that links authors to papers, e.g.:

CREATE TABLE paper_author (
  paper_id INT,
  author_id INT,
  PRIMARY KEY(paper_id, author_id)
);

Then you can check for all authors of a paper with:

SELECT paper.name, author_name 
  FROM paper
  JOIN paper_author ON paper.id = paper_author.author_id
  JOIN author       ON paper_authors.author_id = author.id
 WHERE paper.name = ...;

Same for all papers by a specific author with

 WHERE author.name = ...;

Or if you you want a list of authors for each paper on a single row you can use GROUP BY and the GROUP_CONCAT() aggregate function:

SELECT paper.name, GROUP_CONCAT(author_name)
  FROM paper
  JOIN paper_author ON paper.id = paper_author.author_id
  JOIN author       ON paper_authors.author_id = author.id
 GROUP BY paper.name;