Should a relationship in an E-R diagram be created in the database as a table or a foreign key?
a tutor lectures a class
I see it as three possibilities:
- there is a "lectures" table showing this relationship, containing tutor_id and class_id, both as foreign keys
- Or "tutor_id" is a foreign key in the class table
- 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.