Trouble deciding on identifying or non-identifying

Question

I've read this question: What's the difference between identifying and non-identifying relationships?

But I'm still not too sure... What I have is three tables.

1. Users
2. Objects
3. Pictures

A user can own many objects and can also post many pictures per individual object. My gut feeling tells me this is an identifying relationship, because I'll need the userID in the objects table and I'll need the objectID in the pictures tables...

Or am I wrong? The explanations in the other topic limit themselves to the theoretical explanation of the way the database interprets it after it's already been coded, not how the objects are connected in real life. I'm kinda confused as to how to make the decision of identifying versus non-identifying when thinking about how I'm going to build the database.

Answer 1

Mahdi, your instincts are correct. This is a duplicate question and this up-voted answer is not correct or complete. Look at the top two answers here: difference between identifying non-identifying

Identifying vs non-identifying has nothing to do with identity. Simply ask yourself can the child record exist without the parent? If the answer is yes, the it is non-identifying.

The core issue whether the primary key of the child includes the foreign key of the parent. In the non-identifying relationship the child's primary key (PK) cannot include the foreign key (FK).

Ask yourself this question

• Can the child record exist without the parent record?

If the child can exist without the parent, then the relationship is non-identifying. (Thank you MontrealDevOne for stating it more clearly)

One-to-one identifying relationship

Social security numbers fit nicely in to this category. Let's imagine for example that social security numbers cannot exist with out a person (perhaps they can in reality, but not in our database) The person_id would be the PK for the person table, including columns such as a name and address. (let's keep it simple). The social_security_number table would include the ssn column and the person_id column as a foreign key. Since this FK can be used as the PK for the social_security_number table it is an identifying relationship.

One-to-one non-identifying relationship

At a large office complex you might have an office table that includes the room numbers by floor and building number with a PK, and a separate employee table. The employee table (child) has a FK which is the office_id column from the office table PK. While each employee has only one office and (for this example) every office only has one employee this is a non-identifying relationship since offices can exist without employees, and employees can change offices or work in the field.

One-to-many relationships

One-to-many relationships can be categorized easily by asking the same question.

Many-to-many relationships

Many-to-many relationships are always identifying relationships. This may seem counter intuitive, but bear with me. Take two tables libary and books, each library has many books, and a copy of each book exists in many libraries.

Here's what makes it and identifying relationship: In order to implement this you need a linking table with two columns which are the primary keys of each table. Call them the library_id column and the ISBN column. This new linking table has no separate primary key, but wait! The foreign keys become a multi-column primary key for the linking table since duplicate records in the linking table would be meaningless. The links cannot exist with out the parents; therefore, this is an identifying relationship. I know, yuck right?

Most of the time the type of relationship does not matter.

All that said, usually you don't have to worry about which you have. Just assign the proper primary and foreign keys to each table and the relationship will discover itself.

EDIT: NicoleC, I read the answer you linked and it does agree with mine. I take his point about SSN, and agree that is a bad example. I'll try to think up another clearer example there. However if we start to use real-world analogies in defining a database relationship the analogies always break down. It matters not, whether an SSN identifies a person, it matters whether you used it as a foreign key.

Answer 2

I think that an easier way to visualize it is to ask yourself if the child record can exist without the parent. For example, an order line item requires an order header to exist. Thus, an order line item must have the order header identifier as part of its key and hence, this is an example of an identifying relationship.
On the other hand, telephone numbers can exist without ownership of a person, although a person may have several phone numbers. In this case, the person who owns the phone number is a non-key or non-identifying relationship since the phone numbers can exist irrespective of the owner person (hence, the phone number owner person can be null whereas in the order line item example, the order header identifier cannot be null.

Answer 3

NickC Said: one-to- relationships are identifying relationships, and many-to-many relationships are non-identifying relationships

The explanation seems totally wrong to me. You can have:

• Ono-to-One Non-identifying Relationships
• One-to-Many Non-identifying Relationships
• One-to-One Identifying Relationships
• One-to-Many Identifying Relationships
• Many-to-Many Identifying Relationships

Imagine you have the following tables: customer, products and feedback. All of them are based on the customer_id which exists on the cutomer table. So, by NickC definition there shouldn't be exists any kind of Many-to-Many Identifying Relationships, however in my example, you can clearly see that: A Feedback can exists only if the relevant Product exists and has been bought by the Customer, so Customer, Products and Feedback should be Identifying.

You can take a look at MySQL Manual, explaining how to add Foreign Keys on MySQL Workbench as well.

Answer 4

Both sound like identifying relationships to me. If you have heard the terms one-to-one or one-to-many, and many-to-many, one-to- relationships are identifying relationships, and many-to-many relationships are non-identifying relationships.

• If the child identifies its parent, it is an identifying relationship. In the link you have given, if you have a phone number, you know who it belongs to (it only belongs to one).

• If the child does not identify its parent, it is a non-identifying relationship. In the link, it mentions states. Think of a state as a row in a table representing mood. "Happy" doesn't identify a particular person, but many people.

Edit: Other real life examples:

• A physical address is a non-identifying relationship, because many people may reside at one address. On the other hand, an email address is (usually considered) an identifying relationship.
• A Social Security Number is an identifying relationship, because it only belongs to one person
• Comments on Youtube videos are identifying relationships, because they only belong to one video.
• An original of a painting only has one owner (identifying), while many people may own reprints of the painting (non-identifying).