I am new to database design and am trying to understand the best practice for using foreign keys. I know that when you have a 1:m relationship, we don't have to create a relation for the relationship; instead we could add a foreign key to the m-side of the relationship(which corresponds to the primary key on the 1-side) so as to preserve referential integrity. My question however is: Under what other circumstances could we do the same? Can we do the same when we have a 0..1 to 1 or 1-1 relationship as well? What is the best practice for this type of situation when referential integrity is as important as the computational cost?
There are three possible approaches when we are mapping 1:1 relation to Relational Model:
Foreign Key approach: Choose one of the relations-say S-and include a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S.
Merged relation option: An alternate mapping of a 1:1 relationship type is possible by merging the two entity types and the relationship into a single relation. This may be appropriate when both participations are total.
Cross-reference or relationship relation option: The third alternative is to set up a third relation R for the purpose of cross-referencing the primary keys of the two relations S and T representing the entity types.
For more details you can look into this home.iitj.ac.in/~ramana/ch7-mapping-ER-EER-relations.pdf