CODEWITHSUNDEEP
mysqldatabase-designrelational-databasedatabase-schemaentity-relationship
O.A.
O.A.

Reputation: 171

Cardinality and foreign key relationship

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?

Upvotes: 0

Views: 4808

Answers (3)

Anam Amin
Anam Amin

Reputation: 1

the M-to-M relationship is equivalent to two 1:M relationships .we can not assign primary key of 1 side as a foreign key of other for this purpose we use a middle entitiy that resolve an M:M and that entity is tipically called "association " or intersection entity. e.g A M:M relationship between project and employee a new midlle entity can be assignment so in this way the relation will convert into 1:M and we can assign a foreign key easilly

Upvotes: 0

reaanb
reaanb

Reputation: 10064

Foreign key constraints are restrictions, not references. A relation exists implicitly wherever different columns represent the same domain, and their tables can be joined, with or without foreign keys. The constraint just ensures that the values/entities stored in one column exists in another column. They're appropriate to use wherever a relation is dependent on another, regardless of the cardinality of either. The typical use case here is subtyping of entity sets.

The main benefits of foreign key constraints is the performance gains (reduced calls across the wire as well as development time) enabled by cascading updates/deletes from a single statement instead of having to query for an ID before executing multiple update/delete statements wrapped in a transaction. Not to mention repair time if changes weren't propagated properly.

Upvotes: 0

Enigma
Enigma

Reputation: 102

There are three possible approaches when we are mapping 1:1 relation to Relational Model:

  1. 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.

  2. 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.

  3. 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

Upvotes: 1

Related Questions