MySQL – Identifying Non-Identifying vs Identifying Relationships

database-designdatabase-diagramsMySQLmysql-workbench

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?

Best Answer

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

Related Solutions

Mysql – Is the ER Diagram correct? Why use relationships

It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources including ad hoc queries, data imports, etc. It is irresponsible to think the data in the database is protected because the application has protections. Sure you think that you will force all changes through a webservice or some such, but the reality is that no one is going to add a million new customer records, from that company you just bought, one record at a time through a service. Your database has to be designed to account for the fact that people will change it directly at the source or through other applications (some of which may not be able to use your web service). Further, the application interface is usually more likely to be thrown away or redesigned than the data and when that happens, you may lose all or some of the data integrity rules. In designing databases, you have to think about how the data needs to be protected over time not about what is easiest and most convenient for the programmer or the intial application. A data professional sets up PK/FK relationships in the database because that is where the data lives and it is the best place to set it up to protect the data quality.

I see data from a lot of different companies in my current position and I see all too many that didn't set up their PK/FK relationships in the database because they have data integrity problems that a correctly designed database would not have.

Mysql – Should this be an identifying relationship or not

Patient and SpiProfessional are uniquely identified only by their relationship to User, which means they are in an identifying relationship. It can also be helpful to think of them as "depending" on User.

That was the original question. Then there were two design questions:

So how do I recognize which one he is or whether he could be both(if that's possible)?

Without knowing more about the application's requirements or application logic, it seems that information can be determined by simple reports.

For example, to find Patients who are not also pros:

SELECT u.* , p.*
FROM User u 
INNER JOIN Patient p 
ON u.idUser = p.idUser
LEFT OUTER JOIN SpiProfessional sp
ON  u.idUser = sp.idUser 
WHERE sp.idUser IS NULL

If you do not care about the User only being one type, then cut off the last three lines. In any case, the best way to do this depends on your application requirements.

Should Patient and SpiProfessional have additional primary keys of their own?

It's becoming a popular practice to give every table a single column primary key. I like doing this, but it is a preference.

Best Answer

Related Solutions

Mysql – Is the ER Diagram correct? Why use relationships

Mysql – Should this be an identifying relationship or not

Related Question