Thursday, 8 November 2018

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


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

 Answers



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



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



Bill's answer is correct, but it is shocking to see that among all the other answers no one points out the most significant aspect.
It is said over and over again, that in and identifying relationship the child can not exist without the parent. (e.g. user287724). This is true, but completely misses the point. It would be enough enough for the foreign key to be non-null, to achieve this. It does not need to be part of the primary key.
So here is the real reason:
The purpose of an identifying relationship is that the foreign key can NEVER CHANGE, because it is part of the primary key... therefore identifying!!!



how user287724 second answer example of the book and author relationship got 576 vote ups?!!! , as it says:
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.
this is a very confusing example and is definitely not a valid example for an identifying relationship.
I finally understand the difference between both relationships :(( , so please don't confuse me with this amount of vote ups!!
yes, a book can not be written without at least one author, but the author(it's foreign key) of the book is NOT IDENTIFYING the book in the books table!
you can remove the author (FK) from the book row and still can identify the book row by some other field (ISBN, ID, ...etc) , BUT NOT the author of the book!!
I think a valid example of an identifying relationship would be the relationship between (products table) and a (specific product details table) 1:1
products table
+------+---------------+-------+--------+
|id(PK)|Name           |type   |amount  |
+------+---------------+-------+--------+
|0     |hp-laser-510   |printer|1000    |
+------+---------------+-------+--------+
|1     |viewsonic-10   |screen |900     |
+------+---------------+-------+--------+
|2     |canon-laser-100|printer|200     |
+------+---------------+-------+--------+

printers_details table
+--------------+------------+---------+---------+------+
|Product_ID(FK)|manufacturer|cartridge|color    |papers|
+--------------+------------+---------+---------+------+
|0             |hp          |CE210    |BLACK    |300   |
+--------------+------------+---------+---------+------+
|2             |canon       |MKJ5     |COLOR    |900   |
+--------------+------------+---------+---------+------+
* please note this is not real data
in this example the Product_ID in the printers_details table is considered a FK references the products.id table and ALSO a PK in the printers_details table , this is an identifying relationship because the Product_ID(FK) in the printers table IS IDENTIFYING the row inside the child table , we can't remove the product_id from the child table because we can't identify the row any more because we lost it's primary key
if you want to put it in 2 lines:
an identifying relationship is the relationship when the FK in the child table is considered a PK(or identifier) in the child table while still references the parent table
another example may be when you have 3 tables (imports - products - countries) in an imports and exports for some country database
the import table is the child that has these fields(the product_id(FK), the country_id(FK) , the amount of the imports , the price , the units imported , the way of transport(air, sea) ) we may use the (product_id, the country_id) to identify each row of the imports "if they all in the same year" here the both columns can compose together a primary key in the child table(imports) and also referencing there parent tables.
please I'm happy I finally understand the concept of the identifying relationship and non identifying relationship :(( , so please don't tell me I'm wrong with all of these vote ups for a completely invalid example
yes logically a book can't be written without an author but a book can be identified without the author,In fact it can't be identified with the author!
you can 100% remove the author from the book row and still can identify the book !!! , please don't tell me I misunderstood the concept :(



If you consider that the child item should be deleted when the parent is deleted, then it is an identifying relationship.
If the child item should be kept even though the parent is deleted, then it is a non-identifying relatioÇıship.
As an example, I have a training database with trainees, trainings, diplomas and training sessions :
  • trainees have an identifying relationship with training sessions
  • trainings have an identifying relationship with training sessions
  • but trainees have a non-identifying relationship with diplomas
Only training sessions should be deleted if one of the related trainee, training or diploma is deleted.



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.



Like well explained in the link below, an identifying relation is somewhat like a weak entity type relation to its parent in the ER conceptual model. UML style CADs for data modeling do not use ER symbols or concepts, and the kind of relations are: identifying, non-identifying and non-specific.
Identifying ones are relations parent/child where the child is kind of a weak entity (even at the traditional ER model its called identifying relationship), which does not have a real primary key by its own attributes and therefore cannot be identified uniquely by its own. Every access to the child table, on the physical model, will be dependent (inclusive semantically) on the parent's primary key, which turns into part or total of the child's primary key (also being a foreign key), generally resulting in a composite key on the child side. The eventual existing keys of the child itself are only pseudo or partial-keys, not sufficient to identify any instance of that type of Entity or Entity Set, without the parent's PK.
Non-identifying relationship are the ordinary relations (partial or total), of completely independent entity sets, whose instances do not depend on each others' primary keys to be uniquely identified, although they might need foreign keys for partial or total relationships, but not as the primary key of the child. The child has its own primary key. The parent idem. Both independently. Depending on the cardinality of the relationship, the PK of one goes as a FK to the other (N side), and if partial, can be null, if total, must be not null. But, at a relationship like this, the FK will never be also the PK of the child, as when an identifying relationship is the case.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)