2

I've recently started using MySQL Workbench to manage EER diagrams. Up until now I have been using phpmyadmin and I never came across the terms Identifying & Non-Identifying relationships.

I have looked up the difference online and read a few Stack Overflow answers on the topic, but I'm still pretty much clueless.

From what I understand, I am going to give an example of a scenario in my database and I will propose the right solution.

So in my database I have a users table and a contact table containing various methods to contact the user (such as email & phone number). Since a contact record cannot exist without relating to a user, this should be an identifying relationship.

Here is what my tables look like:

+-------+    +-------+
| users |    |contact|
+-------+    +-------+
| id    |    |id     |
+-------+    |userid |
             |contact| // contains email or phone
             |type   | // specifies if email or phone
             +-------+

However, when I create an identifying relationship between the two tables, it makes the userId part of a composite key. I understand that the table can have a composite primary key made up of userid and contact, however want to keep a consistent structure throughout the database where every table has its own surrogate key. (I have seen many times that it's better practice to use surrogate keys over composite keys.)

So in this case, what's the actual correct way to do this? Should I use a composite key and scrap the surrogate key (which I really would prefer not to do)? Or should I use a non-identifying relationship in this case?

Please explain exactly what the difference between the two relationships is and why identifying relationships need to point to a field which is part of a composite key.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Abraham Murciano Benzadon
  • 1,424
  • 13
  • 27
  • What are you using that's creating keys automatically for you? – Barmar Jun 21 '17 at 23:28
  • @Barmar "I've recently started using MySQL Workbench to manage EER diagrams" – Abraham Murciano Benzadon Jun 21 '17 at 23:33
  • I'm going to have to delete my answer and edit it. Must get non- vs identifying usage correct. It will be back. "The technical definition of an identifying relationship is that a child's foreign key is part of its primary key." – philipxy Jun 22 '17 at 08:28

1 Answers1

3

It sounds like you have a non-identifying relationship, where the primary key of the parent exists in the child but is not part of the primary key of the child - given that the child entity (contact) has its own generated primary key.

Identifying relationships mean that the primary key of the parent entity is part of the natural key (supposedly composite unless the entities are 1:1), and are not generally in fashion right now.

In terms of database design, identifying vs. non-identifying is a pretty rare distinction in the real world. Most data models I encounter or design have a primary key on each entity, and a foreign key from other entites to that primary key for any object which refers to it. Very occasionally will I, or any other data modeler I know, enforce uniqueness at the natural key level through the use of database constraints.

Bryan Newman
  • 621
  • 3
  • 9