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.

Should a relationship in an E-R diagram be created in the database as a table or a foreign key?

For example:

a tutor lectures a class

I see it as three possibilities:

  1. there is a "lectures" table showing this relationship, containing tutor_id and class_id, both as foreign keys
  2. Or "tutor_id" is a foreign key in the class table
  3. Or "class_id" is a foreign key in the tutor table

I say no to #3, because a lecturer can take multiple classes, and then there would be multiple entries in the lecturer table for the same lecturer (breaking 3NF?).

But I can see an argument for both #1 and #2. I imagine that both might be allowed, but that #1 would be over-normalised (is there such a thing?), and that #2 is most practical. #1 has two joins to get the lecturers details, using a potentially unnecessary table.

If when creating an E-R diagram you put in the relationships, then you have the table structure for modelling that relationship.

For what it's worth, I would use approach #2, but the idea of going a step further with #3 came to me today while drawing a diagram, and I wonder if it is worth the extra effort.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Your first option works best when there is the possibility that a lecture can have multiple tutors or when a tutor can teach multiple classes. Your second option works best when you can guarantee that a class will only ever have a single tutor at any one time (otherwise you would need multiple entries in your class table to accommodate extra tutors). The third one will only work if you can guarantee that a tutor will only ever teach one class (the opposite of the second scenario).

If you want/need maximum flexibility then go with option 1. However, you should choose the option that best suits your needs based on the descriptions of the types I have given above.

You can indeed over-normalize a database, and in some cases a de-normalized structure may suit the requirement better - but I don't think this is one of those times.

I hope this helps you.

share|improve this answer
    
Of course, it depends on the relationship! I can't believe I overlooked it. The best answers are always simple. For n..n, have the relationship as a table, but for guaranteed n..1 or 1..n, have it as a foreign key. –  Clarkey Sep 5 '12 at 21:04
    
We all overlook something at some point so don't sweat it ;-) Just glad I could help you. –  Mr.Brownstone Sep 5 '12 at 21:06
    
+1 The following paper is a very good description of how to transform an E-R-diagram to relations (database tables) "T.J.Teorey, D.Yang,J.P.Fry A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model": fe.up.pt/~jmoreira/wwwsi/2000-01/eerm.pdf –  miracle173 Sep 6 '12 at 11:30
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.