I
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
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