composite foreign keys - Forum - OpenEdge Development - Progress Community
 Forum

composite foreign keys

  • 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 ?

    • Have I been a pedantic db designer all my life ?
    • Is there something that I am missing ?
    • Or is it simply bad db design to have this sort of construct ?

    please say the latter ... ;)

  • 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 :(


    On 05/13/2014 02:10 PM, jmls wrote:
    >
    From: jmls
    > Post: composite foreign keys
    > Posted in: OpenEdge Development
    > Link: http://community.progress.com/technicalusers/f/19/t/10162.aspx
    >
    > 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 ?
    >
    > Have I been a pedantic db designer all my life ?
    > Is there something that I am missing ?
    > Or is it simply bad db design to have this sort of construct ?
    >
    > please say the latter ... ;)
    >
    >
    > --
    > You were sent this email because you opted to receive email notifications when someone created a new thread.
    >
    > To unsubscribe
    from:
    > - ...only this thread, disable notifications at http://community.progress.com/technicalusers/f/19/t/10162.aspx.
    > - ...all email notifications from Progress Community, navigate to "Settings", click on the "Email" tab, then under the "Email Configuration" section, set Send Notifications to "No".
    >
    >


    --
    m.edu
    keep it simple
    http://www.ganimede.ro
    http://ro.linkedin.com/in/marianedu
    medu@ganimede.ro
    mobile: +40 740 036 212
    skype: marian.edu
  • In what way does a composite foreign key violate TNF?

    --
    Tom Bascom
    tom@wss.com

  • "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".Music

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

    --
    Tom Bascom
    tom@wss.com

  • But.. there is no official foreign-key designator in Progress. It's just an implied foreign key.

    Jeff Ledbetter

    Roundtable Product Architect

    www.roundtable-software.com

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

    On 13 May 2014 14:50, "Jeff Ledbetter" <bounce-jeffledbetter@community.progress.com> wrote:
    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.

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

    Jeff Ledbetter

    Roundtable Product Architect

    www.roundtable-software.com

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com


  • Agree with Tom, composed natural keys are not a violation of 3nf but I prefer a surrogate key (meaningless) as foreign key because: 
    - easier to write joins 
    - take less space (child tables and also indexes will have less fields) 
    - allows for updates on fields part of natural key (does not trigger update on all child's records).
    Other than that I don't like any surrogate so please give us proper FK constraints in the database, yeah Thomas can opt not to use that but I'll live with that :) 

    Ah, and integers do take less space (table and index) compared to guid so auto-increment fields will be a nice addition (very 4gl-ish imho).


    Sent from my HTC

  • The use of a meaningful field as a key is clearly anathema.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com