What's the difference between identifying and non-identifying relationships?

Question

I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?

Answer 1

An Identifying relationship specifies that a child object cannot exist without the parent object

Non-identifying relationships specifies a regular association between objects, 1:1 or 1:n cardinality.

Non-identifying relationships can be specified as optional where a parent is not required or mandatory where a parent is required by setting the parent table cardinality...

Answer 2

Here's a good description:

Relationships between two entities may be classified as being either "identifying" or "non-identifying". Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity's primary key. In addition, non-identifying relationships may be further classified as being either "mandatory" or "non-mandatory". A mandatory non-identifying relationship exists when the value in the child table cannot be null. On the other hand, a non-mandatory non-identifying relationship exists when the value in the child table can be null.

http://www.sqlteam.com/article/database-design-and-modeling-fundamentals

Here's a simple example of an identifying relationship:

Parent
------
ID (PK)
Name

Child
-----
ID (PK)
ParentID (PK, FK to Parent.ID) -- notice PK
Name

Here's a corresponding non-identifying relationship:

Parent
------
ID (PK)
Name

Child
-----
ID (PK)
ParentID (FK to Parent.ID) -- notice no PK
Name

Answer 3

• An identifying relationship is when the existence of a row in a child table depends on a row in a parent table. This may be confusing because it's common practice these days to create a pseudokey for a child table, but not make the foreign key to the parent part of the child's primary key. Formally, the "right" way to do this is to make the foreign key part of the child's primary key. But the logical relationship is that the child cannot exist without the parent.

  Example: A Person has one or more phone numbers. If they had just one phone number, we could simply store it in a column of Person. Since we want to support multiple phone numbers, we make a second table PhoneNumbers, whose primary key includes the person_id referencing the Person table.

  We may think of the phone number(s) as belonging to a person, even though they are modeled as attributes of a separate table. This is a strong clue that this is an identifying relationship (even if we don't literally include person_id in the primary key of PhoneNumbers).

• A non-identifying relationship is when the primary key attributes of the parent must not become primary key attributes of the child. A good example of this is a lookup table, such as a foreign key on Person.state referencing the primary key of States.state. Person is a child table with respect to States. But a row in Person is not identified by its state attribute. I.e. state is not part of the primary key of Person.

  A non-identifying relationship can be optional or mandatory, which means the foreign key column allows NULL or disallows NULL, respectively.

Answer 4

A good example comes from order processing. An order from a customer typically has an Order Number that identifies the order, some data that occurs once per order such as the order date and the Customer ID, and a series of line items. Each line item contains an item number that identifies a line item within an order, a product ordered, the quantity of that product, the price of the product, and the amount for the line item, which could be computed by multiplying the quantity by the price.

The number that identifies a line item only identifies it in the context of a single order. The first line item in every order is item number "1". The complete identity of a line item is the item number together with the order number of which it is a part.

The parent child relationship between orders and line items is therefore an identifying relationship. A closely related concept in ER modeling goes by the name "subentity", where line item is a subentity of order. Typically, a subentity has a mandatory child-parent identitying relationship to the entity that it's subordinate to.

In classical database design, the primary key of the LineItems table would be (OrderNumber, ItemNumber). Some of today's designers would give an item a separate ItemID, that serves as a primary key, and is autoincremented by the DBMS. I recommend classical design in this case.

Answer 5

Very good explanation by Bill Karwin...

Thanks a lot...

Murali (SMK Reddy)

Answer 6

There is another explatation from the real world:

A book belongs to an owner, and an owner can own multiple books. But the book can exist also without the owner and it can change the owner. The relationship between a book and an owner is a non identifying relationship.

A book however is written by an author, and the author could have written multiple books. But the book needs to be written by an author it cannot exist without an author. Therefore the relationship between the book and the author is an identifying relationship.