Re: Identifying an Identifying Relationship

John Dilley wrote:
> In my database designs, I almost exclusively use surrogate keys. Over
> the years I have found NO real-world data that never changes.

Ok. But don't forget that next to the surrogate key there should at least be one other real key.  

> Using Erwin to design a database, I am seing these surrogate keys
> being connected together with other surrogates to make unbelievably
> long primary keys. Research showed me that this was being caused
> because I was using identifying relationships. I then went on a search
> for a definition of what an identifying relationship was, and when to
> use them.
>
> The common definition was that the relationship was an identifying
> relationship if the child record could not exist independant of the
> parent record. An example was given of a vehicle/owners relationship,
> where a vehicle could not be entered unless there was an owner.

That is actually not a very good example. A relationship between A and B is only an identifying relationship if two (!) things hold: 1. Bs cannot exist without an associated A 2. Bs can only be identified through their relationship with As

The second requirement means that the key of a B contains the key of A. Note that this is something that should hold in the world you are modeling. If it does not hold there, then the relationship is not an identifying relationship. So if you can identify cars without refering to their owner, then the relationship is simply not an identifying relationship. A better example is the kidneys you mentioned; usually you can only identify them as "that person's left kidney". :-) Another example would be a paragraph in a text that you can only identify by saying something like "the first paragraph of the second chapter of this book".

> Does the concatenation of keys still make sense when surrogate keys
> are being exclusively used (except for many/many join tables)?

No, never, because then every entity can be identified by its own surrogate key, so the second requirement is never fulfilled.

> But I would like to understand.

The reason that "identifying relationships" have to be indicated is that sometimes the own attributes of an entity are simply not enough to identify the entity. In that case the "key" in some sense consists of some attributes plus a relationship, so you should indicate which relationship that is. And that is why you sometimes need to indicate if a relationship is an identyfying relationship.

So, if you use surrogate keys then an entity can always be identified by its own attributes, so there is then no need for identifying relationships.

-- 
  Jan Hidders