As large and vitriolic discussions seemed to have died down recently and I have lost a source of entertainment, I thought that I'd throw this to the list ...
I was having a discussion with a colleague today about having a foreign key that is multi-component index
I questioned how common this would be , and the answer was "more common than you think"
Surely this is inherently a bad database design ? After all, it violates TNF etc etc
what's the consensus ?
please say the latter ... ;)
In what way does a composite foreign key violate TNF?
--Tom Bascomtom@wss.com
That is my favorite definition as well.
I would say that attributes id1 and id2 do meet the criteria -- they describe the relationship between table A and table B and that relationship is a fact about the key to table A. If, for instance, id1 = order# and id2 = orderLine then id1 is a fact about what order table A is related to (so, in this case, id1 is also a foreign key to table C which is the parent of table B...) and id2 is the orderLine of that order.
Having said that -- I certainly prefer the use of a surrogate key as Marian suggests and as you illustrate with the GUID.
(Although a GUID is relationally questionable because it is itself an composite even though we usually pretend not to know that.)
But.. there is no official foreign-key designator in Progress. It's just an implied foreign key.
Jeff Ledbetter Product Architect | Roundtable Software
True. Doesn't stop you from defining a proper schema though
RE: composite foreign keys Reply by Jeff Ledbetter But.. there is no official foreign-key designator in Progress. It's just an implied foreign key. Stop receiving emails on this subject. Flag this post as spam/abuse.
Flag this post as spam/abuse.
Well, I wasn't advocating defining improper schema (although we have all done it). Having a non-composite "foreign key" is a nice rule and lofty goal but there are exceptions.
EntityMnemonic and SourceRecordKey now there's a thing that is overused.
Where I have typically seen this is in schema where meaningful fields are used as the keys instead of having a separate field which has no application meaning and simply provides the handle to the record. Whether the later is a GUID or autonumber or whatever is less relevant than that the field have no meaning in the application.
Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice http://www.cintegrity.com
@jmls: perfectly fine with me
@Jeff : you may have, I certainly haven't ... :)
where is the tongue-in-cheek smiley ?
OK, I'll bite, Jeff, what is a compelling case for an exception?
The use of a meaningful field as a key is clearly anathema.