CODEWITHSUNDEEP
databasedatabase-designlogicrelational-database
anvd
anvd

Reputation: 4057

EER - identifying and non-identifying relationships in example

If i have three tables, One for users, one for activationLink and one for location.

-The relation between the users and activationLink is an identifying relationship because the specific activationLink only exists if the user is registered in the system.

-But the relation between users and location is non identifying, because the location exists without the users.

-And if exists a table called users and other called worker and student, the worker and student can exist without the users? is a non identifying relationship?

my logic is correct in the three points?

thanks.

Upvotes: 0

Views: 668

Answers (2)

Erwin Smout
Erwin Smout

Reputation: 18408

"-The relation between the users and activationLink is an identifying relationship because the specific activationLink only exists if the user is registered in the system."

Hmmmmmmmmmmmm. So the definition is then that a relationship between x and Y is "identifying" if the Y cannot exist "without the X" ?

That's not exactly what I remember from when I was taught. I think I've been told that a relationship is "identifying" if the relationship itself is (necessarily ?) part of the key/identifier (of the "child" entity). That is, that the attributes of the logical key/identifier of the parent entity are also part of the logical key/identifier of the child entity. Is this the case in your example ? Are activationlinks not unique by and of themselves ? Can several distinct users "share" the same activationlink ? Sounds odd.

Note that this definition makes the concept superficial, if not completely inapplicable, to relationships that do not have a "many" on at least one of its sides (such as your user-worker relationship, e.g.).

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

(1) The relation between User and ActivationLink is not entirely clear as presented.

  • An ActivationLink must have a User (i.e. be related to one and only one user)
  • However, how many ActivationLinks can a User have? Zero, One, Zero or more, One or more?

It is probably an identifying relationship, but I'd like to know a bit more before comitting to it.

(2) Correct. Users and Locations are (as you have described here) separate entities.

(3) Based solely on the typical meanings derived from the words used, one would assume that "A User can be a Student or a Workder", so they sound like subtypes... but this cannot be proved or disproved by the information at hand. Followup questions are:

  • Can thre be Students or Workers who are not Users?
  • Must a User be either a Student or a Worker?
  • Can a User be both a Student and a Worker?
  • Alternatively, if neither, what is a User? Is there a third (or fourth or fifth or...) table indicating what kind or type of User this is?

Upvotes: 2

Related Questions