Entity-Relationship Diagram for a scenario involving Books, Authors and Countries

Question

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 | ...


linkTable
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?

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 05 '11 at 15:06
@Marian: Thank you for the links. They sound promising and I'll certainly go through them! — Aufwind, May 10 '11 at 14:43

score 5 · Accepted Answer · answered May 05 '11 at 13:43

5

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

answered May 05 '11 at 13:43

goorj

258
1
7

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) – Jack Douglas May 05 '11 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 06 '11 at 07: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 '11 at 09:25

Entity-Relationship Diagram for a scenario involving Books, Authors and Countries

1 Answers1