Definition of 'identifying' and 'non-identifying' relationships
Jeffrey Mo
I'd like to bring up the issue of whether our definition of identifying and non-identifying relationships is 'correct'. I know Jonathan has raised some doubts about this before.
As I understand it, our current definition of an 'identifying' relationship is:
A foreign key relationship in which the foreign key is part of the primary key of the child table.
and a 'non-identifying relationship is:
A foreign key relationship in which the foreign key is NOT part of the primary key of the child table.
I have attached a screenshot demonstrating this. Notice how the table 'chicken' doesn't include the foreign key 'farm_id' in it's primary key and thus is attached to table 'farm' with a dashed line, while the table 'cow' does, and thus is attached with a solid line.
The reason I bring it up is because it will affect the fix for bug 1447 (http://trillian.sqlpower.ca/bugzilla/show_bug.cgi?id=1447). There is a related issue in which non-identifying relationships end up getting reverse engineered as identifying relationships. (http://trillian.sqlpower.ca/bugzilla/show_bug.cgi?id=1389)
I have found the reason for this. In PlayPen.java, in the method createRelationshipsFromPP (I suspect this method is poorly named, as it appears to be used for adding relationships into the playpen from the source database), line 1385, the relationship getting imported always has setIdentified(true). As a result, ALL reverse engineered relationships end up as identifying.
So I thought 'OK, just change it so that it takes the isIdentifying property from the source relationship'. No go. Then it just reverse-engineers everything to a non-identifying relationship. I've tried to look through the code that creates the SQLRelationship object that represents the source database relationship, but it doesn't look like the 'identifying' property doesn't actually get set anywhere for source database ones, resulting in them always being 'false'.
The thing is that although I could just set the identifying property based on our current definition, would it really be worth it if we're ultimately going to change the very definition of 'identifying' vs. 'non-identifying' relationships?
Looking forward to your input,
Jeff
Jonathan Fuerth
> Hey everyone,
Hi Jeff, it's about time we raised this issue on the list. It was
touched on in a forum thread in December (among many other things in
that discussion with Michał), but never fully explored.
> As I understand it, our current definition of an 'identifying' relationship
> is:
> A foreign key relationship in which the foreign key is part of the primary
> key of the child table.
>
> and a 'non-identifying relationship is:
> A foreign key relationship in which the foreign key is NOT part of the
> primary key of the child table.
This is a special case of our current general definitions. The general
definitions have to allow for keys and relationships involving
multiple columns. So, generalizing your definition, we get:
an 'identifying' relationship is:
A foreign key relationship in which the whole primary key of the
parent table is entirely contained in the primary key of the child
table.
and a 'non-identifying' relationship is:
A foreign key relationship in which the whole primary key of the
parent table is NOT entirely contained in the primary key of the child
table.
That's what the Architect currently means by identifying vs.
non-identifying relationships.
Here are the definitions that I think might be more usual/more correct:
an 'identifying' relationship is:
A foreign key relationship which maps the whole primary key of the
parent table to the child table.
and a 'non-identifying relationship' is:
A foreign key relationship which maps a whole non-PK unique index of
the parent table to the child table.
By these proposed definitions, all relationships generated by the
Architect are identifying relationships. We don't presently have a way
of mapping a set of unique columns other than the PK onto the child
table. However, it should be possible to obtain such a relationship by
reverse engineering.
> The reason I bring it up is because it will affect the fix for bug 1447
> (http://trillian.sqlpower.ca/bugzilla/show_bug.cgi?id=1447).
> There is a related issue in which non-identifying relationships end up
> getting reverse engineered as identifying relationships.
> (http://trillian.sqlpower.ca/bugzilla/show_bug.cgi?id=1389)
I'm glad you're looking into this. It's definitely a hole in the
round-trip engineering process, and has been mentioned on the forum by
more than one user.
> The thing is that although I could just set the identifying property based
> on our current definition, would it really be worth it if we're ultimately
> going to change the very definition of 'identifying' vs. 'non-identifying'
> relationships?
Probably not, but if the decision was made by a
detectIdentifyingStatus() method on the SQLRelationship instance, it
would be easy to change. The only bad part would be user annoyance
when the definition of identifying vs. non-identifying changes between
releases.
What's the best way to find out the usual/common/correct definition of
these terms? I don't want to make these changes based on my guess. :)
-Jonathan
Jeffrey Mo
In which case, it should be safe to proceed with our current definition. I'm fine with the detectIdentifyingStatus() method.
Jeff
http://www.datanamic.com/support/identifying-non-ident-rel.html
http://www.datamodel.org/DataModelDictionary.html#IDRel
http://www.datamodel.org/DataModelDictionary.html#NonIDRel
http://books.google.ca/books?id=bQQa8E4-l40C&pg=PA48&lpg=PA48&dq=%22non+identifying%22+relationships&source=web&ots=dL4VnIkruS&sig=Sb3CuKLrRfEHYV6mRJTL-7m6ioo&hl=en#PPA47,M1
http://www.sqlteam.com/article/database-design-and-modeling-fundamentals
http://www.inconcept.com/JCM/August2000/halpin.html
http://publib.boulder.ibm.com/infocenter/wchelp/v5r6m1/topic/com.ibm.commerce.data.doc/refs/rdmlegend.htm
http://www.teradata.com/t/page/132175/index.html