Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 Models 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 a ERM look like for these ones?

Edit:


I thought of:

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

But how do I fit in the linkTable?

share|improve this question
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 '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
add comment

1 Answer

up vote 5 down vote accepted

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
share|improve this answer
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 5 '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 6 '11 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 '11 at 9:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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