composite foreign keys

the later of course :)

I tend to use surrogate keys almost all the time for FK (wait, what the
hell am I talking about... was that triggers, BL, DA, BPM, BRM) and
that's because most of the time even things we think will never change
actually does :(

In what way does a composite foreign key violate TNF?

"Nothing but the key"

A memorable statement of Codd's definition of 3NF, paralleling the
traditional pledge to give true evidence in a court of law, was given
by Bill Kent: "[Every] non-key [attribute] must provide a fact about
the key, the whole key, and nothing but the key." A common variation
supplements this definition with the oath: "so help me Codd".

Requiring existence of "the key" ensures that the table is in 1NF;
requiring that non-key attributes be dependent on "the whole key"
ensures 2NF; further requiring that non-key attributes be dependent on
"nothing but the key" ensures 3NF."

http://en.wikipedia.org/wiki/Third_normal_form

so if table A has id1 and id2 as a primary key, and tableB is linked
to table A using id1 and id2 then table B is not TNF as it has
duplicated items apart from the key attribute of A

in other words, table A should have a unique key (guid) and table B
should have an index with the first component of tableA_guid

On 13 May 2014 13:58, ChUIMonster
wrote:
> RE: composite foreign keys
> Reply by ChUIMonster
>
> In what way does a composite foreign key violate TNF?
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten”

Follow dot.r on http://twitter.com/DotRlimited

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.

True. Doesn't stop you from defining a proper schema though

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.

@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.