Modelling Relationships in an E-R Diagram, Table or Foreign Key?

Question

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.

Answer 1

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.