In the ERD notation there are identifying and non-identifying relationships. Basically all info I could find on the two relationships only provide a very short explanation about the differences between them. It comes down to the following quote from an
earlier forum post:
Quote:
Identifying
- primary key attributes of the parent entity become part of the primary key of the child entity
Non-Identifying
- primary key attributes of the parent entity become part of the non-key attributes of the child entity
|
Since I'm new to (proper) db design, descriptions in these lines don't provide enough information for me.
I understand the consequence of using the second, non-identifying, relationship. You will get a normal additional column in the child entity. What does it actually mean when an attribute becomes 'part of the primary key' when we are dealing with the first, identifying, relationship?
How should someone decide to use which relationship?
As an example, I'm currently working on a db design in the program
Power*Architect. Here I've got multiple entities (tables including 'Venues' and 'Persons') which link to a 'Contacts' entity which contains columns for address, phone numbers, e-mail address, website, etc. The reason I choose to create a separate 'Contact' table is because there are going to be about 4 entities which need this info. Instead of adding the same contact columns to all 4 of them, I figured it would be more appropriate to split the lot. A contact entry can not be linked to more then one entry from one of the other 4 tables.
I drew an identifying relationship from all 4 tables to the 'Contacts' table (the horizontal line should be solid):
[Persons]--|--------0|--[Contacts]
[Venues]--|--------0|--[Contacts]
etc.
As a consequence, each ID of the 4 tables became part of the primary key in the child 'Contacts' table. How will this 'part of the primary key' aspect affect the situation? Doesn't a situation like this require 4 IDs from each of the 4 tables for a contacts entry to be added? Because of this assumption, it almost seems to me that an identifying relationship should only be used to solve a many-to-many relationship. Am I correct about this?
Did I make the right move with creating a separate 'Contacts' table and using it with an identifying relationship? A non-identifying relationship didn't seem right since a contacts entry can't exist on it's own.