Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^

I am getting warmed up with MySQL and I try to understand the concepts of creating well structured databases. Currently I am reading about Entity-Relationship Diagrams (ERD) and I asked myself:

If I have three tables which are linked by a forth one, for example: A book written by one ore more authors, and each authors and was published in one ore more countries.

authors          books            countries
id | name        id | book        id | publishedInCountry
 1 | ...          1 | ...          1 | ...
 1 | ...          2 | ...          2 | ...
 1 | ...          2 | ...          3 | ...
 4 | ...          1 | ...          3 | ...

idOfAuthor | idOfBook   | idOfCountry
 1         | 1          | 1

How would an ERD look like for these ones?

Additional information

I thought of:

[authors] -- < is author of > -- [books] -- < was published in > [countries]

But how do I fit in the linkTable?

  • 1
    I'd advise you to start from Wikipedia's article on Database Normalization, continue to some on-line courses (like this one coming from University of Washington - Principles of Database Systems), then continue with some books on database design. And in the mean time don't forget to look here, at questions with 'database-design' tag :-).
    – Marian
    May 5, 2011 at 15:06
  • @Marian: Thank you for the links. They sound promising and I'll certainly go through them!
    – Aufwind
    May 10, 2011 at 14:43

1 Answer 1


as far as I can see,

you need two separate link tables as these are unrelated, one for linking many books to many authors, and one for linking many countries to many books.

linkTable Author-Book
idOfAuthor | idOfBook  
 1         | 1          

linkTable2 Book-Country
 idOfBook   | idOfCountry
 1          | 1
  • 4
    +1 @Aufwind - goorj is making two assumptions, both probably valid: 1) you care about books that have more than one author, and 2) A book has the same authors in each country (ie translations are attributed to the original language author) May 5, 2011 at 14:22
  • Hi, credits for pointing out my assumptions, which might not be valid. Adding the translator as author-per-country would complicate things. One solution could be to add the author link table between Book-Country and Author tables - to get a list of authors for every book-country combination. Another to use the link table as suggested by @Aufwind with Author-Book-Country, but with the Country ID taken to mean something like "if NULL valid for all countries", if set only for this country (i.e. translator(s))
    – goorj
    May 6, 2011 at 7:15
  • Sorry for respondign that late, I simply forgot, that I asked a questin here. :-) @JackPDouglas: Thanks for pointing that out. @goorj: I assume ever book in my database is written in english. So there are no translations to consider. I must admit I have chosen a bad example. Perhaps one should exchange books by (science) papers and countrys by affiliations (and assume, that every paper is written in english). Would this afford 2 linking tables too?
    – Aufwind
    May 10, 2011 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.