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 ... ;)
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 :(
[collapse]On 05/13/2014 02:10 PM, jmls wrote:
>[collapse] 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[collapse] 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[/collapse][/collapse][/collapse]
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".[8]
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
[collapse]Reply by Jeff LedbetterBut.. 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.
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.
`key` like in what is commonly referred as PK, aka the key that is used
in foreign-key constraints (table relations)... I don't see anything
wrong about natural keys, in fact those should be there and enforced by
a unique index and beside I really like those brits ;)
On 05/13/2014 07:05 PM, Thomas Mercer-Hursh wrote:
>[collapse] From: Thomas Mercer-Hursh
> Post: RE: composite foreign keys
> Posted in: OpenEdge Development
> Link: http://community.progress.com/technicalusers/f/19/p/10162/38241.aspx#38241
>
> The use of a meaningful field as a key is clearly anathema.
>
>
> --
> You were sent this email because you opted to receive email notifications when someone created a new thread.
>
> To unsubscribe[collapse] from:
> - ...only this thread, disable notifications at http://community.progress.com/technicalusers/f/19/p/10162/38241.aspx#38241.
> - ...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[/collapse][/collapse]
The other fields should be there, of course, subject to normalization, but the primary key, the way the record is located by any foreign key relations, i.e., the key that is stored in other records, should have no application meaning.
That is good design but it isn't part of TNF.
Ah, see this gentle discussion a bit tardy, too tardy to throw some vitriol in (but to be honest I'm waiting for a new delivery, currently my barn is empty). Agreed with what is said here by Marian (and others that said the same). I only object to the auto-increment integer field. A sequential number is needed for example for an "order" table, where you need to know what the order of creation is. The guid does not give you that. But in most other cases a guid is preferable to me (in case of the order table you can have the guid (PK) next to the sequence (AK)). The generation of the unique field is coupled to the db. The generation of the guid is not. You can assign it on all kinds of client-types and simply set up complete relations in a dataset on the client. Furthermore you get a problem when merging tables from two sources when you only use the sequence.
Even with the order table, I prefer the GUID as the reference for the record. The next customer will turn out to have external orders follow the normal sequence and internal orders follow a sequence starting with 900000 or something.
so what. as long as the order numbers are unique, that could work. guids make my head hurt when i have to look at them.
Point being that with a requirement like that, one can't use an autonumber type field. Moreover, there is always the possibility that someone will enter an order as an internal order and then discover it should have been external. With the GUID (which one never looks at as a user), one can change the order number and all is well. I had a customer years ago who had a system like this for customer numbers ... against my advice. I had to write him a routine that would go through the database and change from old number to new number everywhere it was used whenever they made a mistake.
Yeah, that too but one should never look at them anyway... well, unless
a perl warrior for which it might not be too complicate to decompile :)
The reason I've mentioned only have to do with size (oh yes, sometimes
it does matter), for a data warehouse implementation having guid as PK
in dimension tables will not only increase the size of those tables but
more important it will be multiplied in fact tables by the number of
dimensions in a star schema. This will also be true for indexes both on
dimension (parent) table and facts (children). While storage is chip
nowadays this wasted space do affect performance by bloating memory with
(slightly) larger keys and last but not least comparing two integers is
much faster than comparing two strings of 36 characters :)
And this has nothing to do with the order number thing, that is a
natural key while we were talking about surrogates here... so it should
not have any meaning, a sequence can work just fine and it does not
matter if there are any gaps - having an auto-increment translates to a
set to sequence next-value inside a trigger just easier to describe in
definition.
[collapse]On 05/23/2014 08:46 PM, gus wrote:
>[collapse] From: gus
> Post: RE: composite foreign keys
> Posted in: OpenEdge Development
> Link: http://community.progress.com/technicalusers/f/19/p/10162/39004.aspx#39004
>
> so what. as long as the order numbers are unique, that could work. guids make my head hurt when i have to look at them.
>
>
> --
> You were sent this email because you opted to receive email notifications when someone created a new thread.
>
> To unsubscribe[collapse] from:
> - ...only this thread, disable notifications at http://community.progress.com/technicalusers/f/19/p/10162/39004.aspx#39004.
> - ...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[/collapse][/collapse][/collapse]
The performance impact is there, I know. But still: "The generation of the unique field is coupled to the db. The generation of the guid is not. You can assign it on all kinds of client-types and simply set up complete relations in a dataset on the client. Furthermore you get a problem when merging tables from two sources when you only use the sequence.".
> And this has nothing to do with the order number thing, that is a natural key while we were talking about surrogates here... so > it should not have any meaning
Forgot to react on this one. When the ordernumber is system-generated (it mostly is) it can be seen as a surrogate too. Why not, like f.e. in chemistry HSO4 minus can act as acid as well as as basic.
well because it isn't :)
that order number is seen by the user, client, accountants and IRS so it
does have a meaning being the natural primary key on the order table...
don't remember much about chemistry so if you like calling it a
surrogate as well then by all means go ahead, it will take more than
that to confuse me :p
[collapse]On 05/25/2014 06:02 PM, agent_008_nl wrote:
>[collapse] From: agent_008_nl
> Post: RE: composite foreign keys
> Posted in: OpenEdge Development
> Link: http://community.progress.com/technicalusers/f/19/p/10162/39045.aspx#39045
>
>> And this has nothing to do with the order number thing, that is a natural key while we were talking about surrogates here... so > it should not have any meaning
> Forgot to react on this one. When the ordernumber is system-generated (it mostly is) it can be seen as a surrogate too. Why not, like f.e. in chemistry HSO4 minus can act as acid as well as as basic.
>
>
> --
> You were sent this email because you opted to receive email notifications when someone created a new thread.
>
> To unsubscribe[collapse] from:
> - ...only this thread, disable notifications at http://community.progress.com/technicalusers/f/19/p/10162/39045.aspx#39045.
> - ...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[/collapse][/collapse][/collapse]
And, of course, if it has meaning in the real world, there is always the possibility of it being assigned incorrectly and the mess one has with changing it. In companies with no special policy about order numbers, this might not apply to orders specifically, but order is just one example.
> well because it isn't :)
According to the theory you hold for true. And that truth may not be the absolute.
> that order number is seen by the user, client, accountants and IRS so it does have a meaning
Does that mean that when you decide to show another autonumber in another table to a user the key automatically changes from surrogate to natural? On the other hand the theory I read states that a system-generated key is a surrogate. And we're talking about a system-generated ordernumber.
There are of course humans too of which the gender is not clear. If that's more clear. ;-)
Old discussion on guid/sequence/natural vs surrogate
As far as the GUIDs go, I do see a use case for being able to store them as a 16 byte integer instead of a 36 byte character. Maybe a nice feature for OE12. Btw, I agree with Gus that GUIDs make my head hurt, but being able to generate the key on client, independent of the technology involved, can prove quite useful.
Guid's should not be looked at, when you do the surrogate key is automagically transformed into a natural key. :-)
Attempts to make the guid PK fast have been made, see google. F.e.: www.codeproject.com/.../GUIDs-as-fast-primary-keys-under-multiple-database
I'm not quite sure what you're trying to tell.
I'm not sure about your question.
Discussion on the comb guid with the clustered index is on the peg now. See there what Gus and others tell.