2

Im using MySQLWorkbench to model a diagram. But Im with some doubts about identifying and non-identifying relationships. It seems that all relationships of the project are identifying.

For example, a conference has one user that created the conference. So the conference table have a column "user_id" to store the id of the user that created the conference. A user can create many conferences so there is a 1 to many relationship between the users and conferences table. But Im not understanding if its a identifying relationship or non identifying relationship. Maybe its an identifying relationship because the conference table, the sabe in the many side, has the column user_id, so because of that is identifying? But so it seems that almost all relationships are identifying.

For example, a conference can have many ticket types so there is a 1 to many relationship between the conferences and ticket types table. A ticket type also have a column "conference_id" so it should be also identifying?

Also a conference can have many registrations so there is a 1 to many between conference and registrations, so there is a column "conference_id" in the registrations table. Its also identifying right?

Also a registration can have many participants associated, so there is 1 to many between registrations and participants tables, so the participants table has a column "registration_id", its also identifying right?

Improve this question

1 Answer 1

Reset to default
0

Is the user_id necessary for identifying the conference? Without user_id, would you still be able to unambiguously identify one conference from another?

My guess would be yes because I imagine venue_id (which joins to a table of conference centers) and venue_date uniquely identify conferences. If so, then you have a non-identifying relationship between the conferences table and the users table.

For example: in a baseball DB, is team/player an identifying or non-identifying relationship? It may seem that a team would be a necessary component for identifying a player, but think for a moment: what if a player is a free agent between seasons? Then he may not belong to a team for quite a while.

This takes some time to fully contemplate. Try these explanations:

From - https://stackoverflow.com/questions/762937/whats-the-difference-between-identifying-and-non-identifying-relationships

An identifying relationship is when the existence of a row in a child table depends on a row in a parent table....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.

and

A non-identifying relationship is when the primary key attributes of the parent must not become primary key attributes of the child

Here is another example I like: https://www.datanamic.com/support/relationshiptypes.html

Improve this answer
2
  • Thanks, but for example in the case of a conference and the user that creates a conference, a conference can only exist if a user creates the conference right? So its identifying? When a confernece is created needs the user_id of the user that created the conference. There is no venue table, the conference has some columns like city that stores the location of the conference.
    – Carrick
    Commented Aug 31, 2018 at 15:35
  • This is an abstract topic, for sure, so I understand the difficulty here. Common sense tells me venue_id/date is enough to identify a conference, and user_id is recorded for accountability. But the truth is, I don't know how your app works, nor how the DB is designed. But identifying vs non-identifying is about the DB, not about data-entry requirements in the app.
    – Victor Di Leo
    Commented Aug 31, 2018 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.