Composite Foreign Keys v Autonumber IDs

[Ilan Sebba]

I have a 'parent' table with natural two field composite key, and this table
has a link to a 'child' table. One way to connect the two tables is by
having both the parent fields in the child table. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields

tblChild
CountryOfOriginFK [PK1]
IDNumberInCounryOfOrigin [PK2]
DateOfBirth [PK3]
ChildFields

The problem with this structure is that the link is 'messy' because you have
to link 2 fields to create the child-parent relationship. The problem gets
exacerbated if the child table gives birth to a 'grandchild' table. The
grandchild table will then need triple link to its parent table. And what
if the grandchild table were to have a great-grandchild table? To get
around this problem, this newsgroup strongly encouraged me to insert
autonumber fields which serve no other purpose as to have tidy links. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields
ID (autonumber)

tblChild
ParentFK [PK1]
DateOfBirth [PK2]
ChildFields


I accepted the newsgroups advice, but now my problem is this: every time I
insert a record in the child table (using SQL), I must first retrieve the
parent ID numbers. My hierarchy is really quite deep (with 6 layers top to
bottom). Say I now want to add a record in the bottom most table. I must go
to the topmost table in the hierarchy, retrieve the ID, then go to the next
generation, and retrieve that ID, and so on and so on (I wish I were smart
enough to use recursion in this context).

Most users may probably use Access forms to insert new records. For them, it
makes sense to insert dummy autonumbers, because Access does the work for
them (is there an Access VBA command which will also do the work for me?).
However, in cases such as mine where I use SQL to insert records, am I
really better off having dummy IDs for the link? Maybe I should stick to
multi-field primary keys?

What do you think. Maybe you have a trick for me up your sleeve?

Many thanks


Ilan Sebba

 

[Allen Browne]

Would you consider putting all the people into one table, and joining it to
itself?

Assuming you are interested only in biological parents (not step-parents,
foster parents, adopted parents, donor-parents, etc), the structure would be
like this:

tblPerson
======
PersonID AutoNumber primary key
CountryOfOrigin
IDNumberInCountryOfOrigin
MotherID PersonID of mother (in this table)
FatherID PersonID of father.

To create the relations, drag 3 copies of tblPerson into the Relationships
window (Tools menu). Access will alias the 2nd as tblPerson_1, and the 3rd
as tblPerson2. You can now drag tblPerson_1.PersonID onto
tblPerson.PersonID, and tblPerson_2.PersonID onto tblPerson.FatherID.

For more information on the concept and how to query it and build a family
tree, see::
http://allenbrowne.com/ser-06.html

It would be possible to code a function to recursively look up the tree to
the desired level if that's what you need to do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a 'parent' table with natural two field composite key, and this table
has a link to a 'child' table. One way to connect the two tables is by
having both the parent fields in the child table. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields

tblChild
CountryOfOriginFK [PK1]
IDNumberInCounryOfOrigin [PK2]
DateOfBirth [PK3]
ChildFields

The problem with this structure is that the link is 'messy' because you have
to link 2 fields to create the child-parent relationship. The problem gets
exacerbated if the child table gives birth to a 'grandchild' table. The
grandchild table will then need triple link to its parent table. And what
if the grandchild table were to have a great-grandchild table? To get
around this problem, this newsgroup strongly encouraged me to insert
autonumber fields which serve no other purpose as to have tidy links. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields
ID (autonumber)

tblChild
ParentFK [PK1]
DateOfBirth [PK2]
ChildFields


I accepted the newsgroups advice, but now my problem is this: every time I
insert a record in the child table (using SQL), I must first retrieve the
parent ID numbers. My hierarchy is really quite deep (with 6 layers top to
bottom). Say I now want to add a record in the bottom most table. I must go
to the topmost table in the hierarchy, retrieve the ID, then go to the next
generation, and retrieve that ID, and so on and so on (I wish I were smart
enough to use recursion in this context).

Most users may probably use Access forms to insert new records. For them, it
makes sense to insert dummy autonumbers, because Access does the work for
them (is there an Access VBA command which will also do the work for me?).
However, in cases such as mine where I use SQL to insert records, am I
really better off having dummy IDs for the link? Maybe I should stick to
multi-field primary keys?

What do you think. Maybe you have a trick for me up your sleeve?

 

[Roger Carlson]

I'm not sure I follow. In order to create a record in the bottom most
table, you only need to find the autonumber primary key value of the table
above it. You shouldn't have to go all the way to the top. Further, I
don't understand your table design. In a normal One-to-Many relationship,
you have a single autonumber field as the primary key which is then
duplicated as the foreign key in the child table, something like this:

tblParent
ParentID (PK) (autonumber)
ParentFields

tblChild
ChildID (PK) (autonumber)
ParentID (FK to tblParent) (long)
ChildFields

tblGrandChild
GrandChildID (PK) (autonumber)
ChildID (FK to tblChild) (long)
GrandChildFields

And so forth. Therefore each level need only have a single value in the
foreign key which matches the primary key of the table immediately above it
in the hierarchy.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a 'parent' table with natural two field composite key, and this table
has a link to a 'child' table. One way to connect the two tables is by
having both the parent fields in the child table. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields

tblChild
CountryOfOriginFK [PK1]
IDNumberInCounryOfOrigin [PK2]
DateOfBirth [PK3]
ChildFields

The problem with this structure is that the link is 'messy' because you have
to link 2 fields to create the child-parent relationship. The problem gets
exacerbated if the child table gives birth to a 'grandchild' table. The
grandchild table will then need triple link to its parent table. And what
if the grandchild table were to have a great-grandchild table? To get
around this problem, this newsgroup strongly encouraged me to insert
autonumber fields which serve no other purpose as to have tidy links. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields
ID (autonumber)

tblChild
ParentFK [PK1]
DateOfBirth [PK2]
ChildFields


I accepted the newsgroups advice, but now my problem is this: every time I
insert a record in the child table (using SQL), I must first retrieve the
parent ID numbers. My hierarchy is really quite deep (with 6 layers top to
bottom). Say I now want to add a record in the bottom most table. I must go
to the topmost table in the hierarchy, retrieve the ID, then go to the next
generation, and retrieve that ID, and so on and so on (I wish I were smart
enough to use recursion in this context).

Most users may probably use Access forms to insert new records. For them, it
makes sense to insert dummy autonumbers, because Access does the work for
them (is there an Access VBA command which will also do the work for me?).
However, in cases such as mine where I use SQL to insert records, am I
really better off having dummy IDs for the link? Maybe I should stick to
multi-field primary keys?

What do you think. Maybe you have a trick for me up your sleeve?

Many thanks


Ilan Sebba

 

[Roger Carlson]

Seeing Allen's reply, I have to ask: Are these real people we are talking
about, or were these just generic names that designate the heirarchy? I
assumed the latter. If it is the former, Allen's idea is best.

 

[Ilan Sebba]

My greatest apologies.

The table names 'tblParent' and 'tblChild' were not supposed to be real life
objects - just an illustration of my problem in conceptual terms.

The actual problems of building true family trees are fascinating. I will
look at the website. Do you allow a person, for example, to give birth to
himself (cloning?).

Would you consider putting all the people into one table, and joining it to
itself?

Assuming you are interested only in biological parents (not step-parents,
foster parents, adopted parents, donor-parents, etc), the structure would be
like this:

tblPerson
======
PersonID AutoNumber primary key
CountryOfOrigin
IDNumberInCountryOfOrigin
MotherID PersonID of mother (in this table)
FatherID PersonID of father.

To create the relations, drag 3 copies of tblPerson into the Relationships
window (Tools menu). Access will alias the 2nd as tblPerson_1, and the 3rd
as tblPerson2. You can now drag tblPerson_1.PersonID onto
tblPerson.PersonID, and tblPerson_2.PersonID onto tblPerson.FatherID.

For more information on the concept and how to query it and build a family
tree, see::
http://allenbrowne.com/ser-06.html

It would be possible to code a function to recursively look up the tree to
the desired level if that's what you need to do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Ilan Sebba" <ilan underscore sebba at btinternet stop com (e-mail address removed)>
wrote in message news:[email protected]...

I have a 'parent' table with natural two field composite key, and this table
has a link to a 'child' table. One way to connect the two tables is by
having both the parent fields in the child table. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields

tblChild
CountryOfOriginFK [PK1]
IDNumberInCounryOfOrigin [PK2]
DateOfBirth [PK3]
ChildFields

The problem with this structure is that the link is 'messy' because you have
to link 2 fields to create the child-parent relationship. The problem gets
exacerbated if the child table gives birth to a 'grandchild' table. The
grandchild table will then need triple link to its parent table. And what
if the grandchild table were to have a great-grandchild table? To get
around this problem, this newsgroup strongly encouraged me to insert
autonumber fields which serve no other purpose as to have tidy links. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields
ID (autonumber)

tblChild
ParentFK [PK1]
DateOfBirth [PK2]
ChildFields


I accepted the newsgroups advice, but now my problem is this: every time I
insert a record in the child table (using SQL), I must first retrieve the
parent ID numbers. My hierarchy is really quite deep (with 6 layers top to
bottom). Say I now want to add a record in the bottom most table. I

must

go
to the topmost table in the hierarchy, retrieve the ID, then go to the next
generation, and retrieve that ID, and so on and so on (I wish I were smart
enough to use recursion in this context).

Most users may probably use Access forms to insert new records. For

them,

it
makes sense to insert dummy autonumbers, because Access does the work for
them (is there an Access VBA command which will also do the work for me?).
However, in cases such as mine where I use SQL to insert records, am I
really better off having dummy IDs for the link? Maybe I should stick to
multi-field primary keys?

What do you think. Maybe you have a trick for me up your sleeve?

 

[Ilan Sebba]

" In order to create a record in the bottom most table, you only need to
find the autonumber primary key value of the table above it."

Say I want the to add a record to the bottom most table in a
grandparent-parent-child hierarchy. You suggest - just find the parent
record. But how do I do that? Here is an illustration of the problem:

tblGrandparent
NaturalKey1 [PK1]
NaturalKey2 [PK2]
GrandparentFields
ID

tblParent
GrandparentFK [PK1]
NaturalField [PK2]
ParentFields
ID

tblChild
ParentFK [PK1]
NaturalField [PK2]
ChildFields

In order for me to find the 'parent' record, I need to know the following
about the parent:

1) GrandparentFK
2) NaturalField

I know the natural field for the parent, but I do not know the
GrandparentFK. The only way for me to find the GrandparentFK is by going to
the Grandparent table, and retrieving it from the two natural grandparent
keys.

Next issue:
"> In a normal One-to-Many relationship,

you have a single autonumber field as the primary key which is then
duplicated as the foreign key in the child table, something like this:

tblParent
ParentID (PK) (autonumber)
ParentFields
"

Hence, table could contain two identical records, or the same record twice.
Hence, whenever I design a table, I ensure that I will have natural primary
keys (this can admittedly be obtain through 'indexing'). However, even with
the table as you propose, the my main problem will remain: whenever I want
to enter a child record, I will need the ParentID. The only way I can get
that is by going to the parent table, and retrieving it.

Given that you are suggesting that even the primary key be an autonumber, I
assume that you are affirming what I have been told so far: refrain from
using complex foreign keys, even if this means having to go all the way up
the hierarchy to obtain ID numbers. Is that correct?

Many thanks


Ilan

 

[Tim Ferguson]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com

Say I want the to add a record to the bottom most table in a
grandparent-parent-child hierarchy. You suggest - just find the parent
record. But how do I do that? Here is an illustration of the problem:

There is simply a pay-off between using a chain of artificial keys, or a
stack of compound natural keys.

Joins -- especially ones that jump a 'generation' -- are far more difficult
when you have to translate each PK into the FK to find the next level up or
down. While it is trivial to query using natural keys,

ON Grandchild.GparentID = Grandparent.RecordID

the equivalent would have to include every intervening table, with
predictable effects on the user experience.

On the other hand, it can be a pain writing update and delete queries for
three- or four-field PKs. Then again, you only have to write them once!

In the end, you pays your money and you takes your choice. My instinct is
always to use natural keys, but it's largely a matter of training and
taste.

Hope that helps


Tim F

 

[Roger Carlson]

I think we are saying the same thing, but just using different terms. First
of all, the autonumber field is the PrimaryKey -- period. Its ONLY function
is to maintain the relationship to the child table. Uniqueness is
maintained by a multiple-field Unique Index, composed of the Foreign Key and
one or more natural keys, so perhaps my diagram should have looked like
this:

tblParent
ParentID (PK) (autonumber)
ParentName (UI)
OtherParentFields

tblChild
ChildID (PK) (autonumber)
ParentID (FK to tblParent) (UI) (long)
ChildName (UI)
OtherChildFields

tblGrandChild
GrandChildID (PK) (autonumber)
ChildID (FK to tblChild) (UI) (long)
GrandChildName (UI)
OtherGrandChildFields

So what you are calling the primary key, I am calling a unique index. It
may sound trivial, but using the proper terminology saves a lot of
confusion.

Now, as to the problem of the 6 levels, it seems to me that you need to do
the same thing using natural keys. Using these generic names is a little
awkward, but suppose you wanted to add a new record (Roger) to tblGrandChild
and you knew the father was Stanley. You still wouldn't know whose son
Stanley was unless you went up to the tblParent table. So if you knew
Stanley's father was Gunnar, a query like this will give you the ChildID
value that you need to add to the GrandChild table inorder to add a record.

SELECT tblParent.ParentName, tblChild.ChildName, tblChild.ChildID
FROM tblParent INNER JOIN tblChild ON tblParent.ParentID = tblChild.ParentID
WHERE (((tblChild.ChildName)="Stanley") AND
((tblParent.ParentName)="Gunnar"));

So I guess I'd say: Yes. Unless I could find an overwhelming reason in the
business rules to use natural keys, I would stick with autonumber primary
keys. All of the arguments for using them still hold true.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

" In order to create a record in the bottom most table, you only need to

find the autonumber primary key value of the table above it."

Say I want the to add a record to the bottom most table in a
grandparent-parent-child hierarchy. You suggest - just find the parent
record. But how do I do that? Here is an illustration of the problem:

tblGrandparent
NaturalKey1 [PK1]
NaturalKey2 [PK2]
GrandparentFields
ID

tblParent
GrandparentFK [PK1]
NaturalField [PK2]
ParentFields
ID

tblChild
ParentFK [PK1]
NaturalField [PK2]
ChildFields

In order for me to find the 'parent' record, I need to know the following
about the parent:

1) GrandparentFK
2) NaturalField

I know the natural field for the parent, but I do not know the
GrandparentFK. The only way for me to find the GrandparentFK is by going to
the Grandparent table, and retrieving it from the two natural grandparent
keys.

Next issue:
"> In a normal One-to-Many relationship,

you have a single autonumber field as the primary key which is then
duplicated as the foreign key in the child table, something like this:

tblParent
ParentID (PK) (autonumber)
ParentFields
"

Hence, table could contain two identical records, or the same record twice.
Hence, whenever I design a table, I ensure that I will have natural primary
keys (this can admittedly be obtain through 'indexing'). However, even with
the table as you propose, the my main problem will remain: whenever I want
to enter a child record, I will need the ParentID. The only way I can get
that is by going to the parent table, and retrieving it.

Given that you are suggesting that even the primary key be an autonumber, I
assume that you are affirming what I have been told so far: refrain from
using complex foreign keys, even if this means having to go all the way up
the hierarchy to obtain ID numbers. Is that correct?

Many thanks


Ilan

I'm not sure I follow. In order to create a record in the bottom most
table, you only need to find the autonumber primary key value of the table
above it. You shouldn't have to go all the way to the top. Further, I
don't understand your table design. In a normal One-to-Many relationship,
you have a single autonumber field as the primary key which is then
duplicated as the foreign key in the child table, something like this:

tblParent
ParentID (PK) (autonumber)
ParentFields

tblChild
ChildID (PK) (autonumber)
ParentID (FK to tblParent) (long)
ChildFields

tblGrandChild
GrandChildID (PK) (autonumber)
ChildID (FK to tblChild) (long)
GrandChildFields

And so forth. Therefore each level need only have a single value in the
foreign key which matches the primary key of the table immediately above it
in the hierarchy.

 

[Ilan Sebba]

Thank you Roger, Thank you Tim.

It looks like whichever method I choose, I have a lot of careful coding to
do. MS-Access, in a form, seamlessly adds new records, no matter how many
tables there are in a hierarchy. I guess that magic trick is not available
in code.

Thanks again, and apologies about not being careful regarding PK and UI.

 

[Allen Browne]

Cloning, eh?

Life is much more complex these days, With IVF, the birth mother may not be
the biological mother, and we don't have enough words to describe the
possible relationship between siblings for such cases.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My greatest apologies.

The table names 'tblParent' and 'tblChild' were not supposed to be real life
objects - just an illustration of my problem in conceptual terms.

The actual problems of building true family trees are fascinating. I will
look at the website. Do you allow a person, for example, to give birth to
himself (cloning?).

Would you consider putting all the people into one table, and joining it to
itself?

Assuming you are interested only in biological parents (not step-parents,
foster parents, adopted parents, donor-parents, etc), the structure

would

be
like this:

tblPerson
======
PersonID AutoNumber primary key
CountryOfOrigin
IDNumberInCountryOfOrigin
MotherID PersonID of mother (in this table)
FatherID PersonID of father.

To create the relations, drag 3 copies of tblPerson into the Relationships
window (Tools menu). Access will alias the 2nd as tblPerson_1, and the 3rd
as tblPerson2. You can now drag tblPerson_1.PersonID onto
tblPerson.PersonID, and tblPerson_2.PersonID onto tblPerson.FatherID.

For more information on the concept and how to query it and build a family
tree, see::
http://allenbrowne.com/ser-06.html

It would be possible to code a function to recursively look up the tree to
the desired level if that's what you need to do.

"Ilan Sebba" <ilan underscore sebba at btinternet stop com (e-mail address removed)>
wrote in message news:[email protected]...

I have a 'parent' table with natural two field composite key, and this table
has a link to a 'child' table. One way to connect the two tables is by
having both the parent fields in the child table. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields

tblChild
CountryOfOriginFK [PK1]
IDNumberInCounryOfOrigin [PK2]
DateOfBirth [PK3]
ChildFields

The problem with this structure is that the link is 'messy' because

you
have

to link 2 fields to create the child-parent relationship. The problem gets
exacerbated if the child table gives birth to a 'grandchild' table. The
grandchild table will then need triple link to its parent table. And what
if the grandchild table were to have a great-grandchild table? To get
around this problem, this newsgroup strongly encouraged me to insert
autonumber fields which serve no other purpose as to have tidy links. EG:

tblParent
CountryOfOrigin [PK1]
IDNumberInCountryOfOrigin [PK2]
ParentFields
ID (autonumber)

tblChild
ParentFK [PK1]
DateOfBirth [PK2]
ChildFields


I accepted the newsgroups advice, but now my problem is this: every

time

I top
to must them,

 

[Pete Arnow]

This would appear to be a many to many relationship. Have you considered a junction table? You have a tblParent table, a Child table, and Grandchild table. Great a table called tblFamily and into it place foreign keys from the other tables, then create your form from that table.