Autonumber as Primary Key and Relationships

I have created several tables that all have data that is similar: name,
address, phone number and other data. The problem is they do not really have
any unique data to be set as the PK. What I have done in order to attempt to
correct the problem is set an autonumber field for each table and use that as
the PK. I set the relationships up using this field. The problem I am having
is if I enter data in one table and go to the next and enter data the
autonumbers are not matching up. If you need I can send you a screen shot of
the relationships.

On Tue, 7 Jul 2009 14:41:01 -0700, cmc096
wrote:

I have created several tables that all have data that is similar: name,
address, phone number and other data.

Why multiple tables? That's almost surely a bad design. If you have several
categories of people, you would really do best to have ONE table of people,
with an additional field indicating which category. You could then use queries
to extract just the people in one category.

The problem is they do not really have
any unique data to be set as the PK. What I have done in order to attempt to
correct the problem is set an autonumber field for each table and use that as
the PK. I set the relationships up using this field. The problem I am having
is if I enter data in one table and go to the next and enter data the
autonumbers are not matching up. If you need I can send you a screen shot of
the relationships.

You CANNOT relate autonumber to autonumber!!!

Autonumbers are meaningless and arbitrary. If you have a one to many
relationship (say from a table of Customers to a table of Orders), you should
probably use an autonumber CustomerID in the customers table; the Orders table
would have a Long Integer (*not* autonumber) CustomerID field as a linking
field.

Perhaps you should post a text description of what these tables contain and
how you are visualizing relating them.
--

John W. Vinson [MVP]

Hi,

Only set the column type to autonumber in the parent table. In all of
the child tables use a long integer as the type for the relating field.

On another note, why do you have data that is similar in several
tables? If you have say people's names and addresses in several tables that
is not a good design. People should all be in one people table. Now, you
may wish to have a separate addresses table since some people have more than
one address.

Combining the two thoughts:

tblPeople
PeopleID (autonumber; primary key)
LastName
FirstName
etc.

tblAddresses
PeopleID (long integer; primary key with following field; related to
tblPeople)
AddressType (mail, home, summer, campus, etc.; primary key with above
field)
Address1
Address2
etc.

Hope this helps,

Clifford Bass

"cmc096" wrote:

I have created several tables that all have data that is similar: name,
address, phone number and other data. The problem is they do not really have
any unique data to be set as the PK. What I have done in order to attempt to
correct the problem is set an autonumber field for each table and use that as
the PK. I set the relationships up using this field. The problem I am having
is if I enter data in one table and go to the next and enter data the
autonumbers are not matching up. If you need I can send you a screen shot of
the relationships.

Cliff

Depending on the domain (think many people working for a single
organization), you can also have many people sharing the same address.

If that's the situation, then a Person, an Address, and a PersonAddress
table would be appropriate. (and a new/revised zip code for one of those
addresses only needs to be altered in one record!)

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Clifford Bass" wrote in message
...
Hi,

Only set the column type to autonumber in the parent table. In all of
the child tables use a long integer as the type for the relating field.

On another note, why do you have data that is similar in several
tables? If you have say people's names and addresses in several tables
that
is not a good design. People should all be in one people table. Now, you
may wish to have a separate addresses table since some people have more
than
one address.

Combining the two thoughts:

tblPeople
PeopleID (autonumber; primary key)
LastName
FirstName
etc.

tblAddresses
PeopleID (long integer; primary key with following field; related
to
tblPeople)
AddressType (mail, home, summer, campus, etc.; primary key with above
field)
Address1
Address2
etc.

Hope this helps,

Clifford Bass

"cmc096" wrote:

I have created several tables that all have data that is similar: name,
address, phone number and other data. The problem is they do not really
have
any unique data to be set as the PK. What I have done in order to attempt
to
correct the problem is set an autonumber field for each table and use
that as
the PK. I set the relationships up using this field. The problem I am
having
is if I enter data in one table and go to the next and enter data the
autonumbers are not matching up. If you need I can send you a screen shot
of
the relationships.

Hi Jeff,

Good point. I might expand it. If you also are storing organizations
in your database, you could still store those addresses in the same table.
You would then also have an OrganizationAddress table. In fact, from your
example, you would have the people working for the organization along with
the organization itself. Of course, for lots of people working for an
organization, their addresses may differ slightly in that the employee
addresses may include a department and/or a room number. This would
complicate things a little.

Clifford Bass

"Jeff Boyce" wrote:

Cliff

Depending on the domain (think many people working for a single
organization), you can also have many people sharing the same address.

If that's the situation, then a Person, an Address, and a PersonAddress
table would be appropriate. (and a new/revised zip code for one of those
addresses only needs to be altered in one record!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Gentlemen, thanks for all the input. I think I am on the right track now. I
am going to set my table up like this:

Parent Table
tbl_Service_Member_Info
SM_ID (autonumber_PK)
L_Name
F_Name
Rank
Address
Phone

Child Table
tbl_Spouse_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

tbl_Dependent_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

My question is with this design will it allow me to have a one to many
relationship with the Parent tbl (Service_Member_Info) to the Child table
(Dependent_Info)? Also, will one service member then be linked to the spouce
and dependent tables using the autonumber to link them and track for instance
multiple dependents?



"Clifford Bass" wrote:

Hi Jeff,

Good point. I might expand it. If you also are storing organizations
in your database, you could still store those addresses in the same table.
You would then also have an OrganizationAddress table. In fact, from your
example, you would have the people working for the organization along with
the organization itself. Of course, for lots of people working for an
organization, their addresses may differ slightly in that the employee
addresses may include a department and/or a room number. This would
complicate things a little.

Clifford Bass

"Jeff Boyce" wrote:

Cliff

Depending on the domain (think many people working for a single
organization), you can also have many people sharing the same address.

If that's the situation, then a Person, an Address, and a PersonAddress
table would be appropriate. (and a new/revised zip code for one of those
addresses only needs to be altered in one record!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

On Wed, 8 Jul 2009 07:56:02 -0700, cmc096
wrote:

Parent Table
tbl_Service_Member_Info
SM_ID (autonumber_PK)
L_Name
F_Name
Rank
Address
Phone

Child Table
tbl_Spouse_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

tbl_Dependent_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

My question is with this design will it allow me to have a one to many
relationship with the Parent tbl (Service_Member_Info) to the Child table
(Dependent_Info)?

Yes. One Service Member can have many Children.

Also, will one service member then be linked to the spouce
and dependent tables using the autonumber to link them and track for instance
multiple dependents?

Yes. Each Service Member can also have many Spouses. But I don't
recommend it.

Note that a Child is not directly related to a Spouse, so your design
can't determine which Spouse is actually little Johnny's mommy or
daddy. But maybe you don't need to know that.

Generally:
You have very similar information in both the Spouse and Dependent
tables. Some would argue that they should be one Dependent table,
with other fields like DependentType (Spouse, Child), Gender,
ActiveFlag (for ex-spouses, grown-up children, deceased), etc.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Cliff

In the very-specific environment I faced, I needed to track who-where-in
what role, so I used a single identifier for each valid combination (i.e., a
"contextID"), and kept Individual, Organization, and Position tables.

Then, since 'contexts' could share an address (think small town city hall),
or a phone number (think very small town) and, theoretically, an email
address, I kept Address, Phone and Email tables.

Finally, to handle the many-to-many, I created junction tables between the
contexts and the address, phone and email records.

A real pain, and definitely not your father's CRM system, but absolutely
required to handle the complexities of the environment.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Clifford Bass" wrote in message
...
Hi Jeff,

Good point. I might expand it. If you also are storing organizations
in your database, you could still store those addresses in the same table.
You would then also have an OrganizationAddress table. In fact, from
your
example, you would have the people working for the organization along with
the organization itself. Of course, for lots of people working for an
organization, their addresses may differ slightly in that the employee
addresses may include a department and/or a room number. This would
complicate things a little.

Clifford Bass

"Jeff Boyce" wrote:

Cliff

Depending on the domain (think many people working for a single
organization), you can also have many people sharing the same address.

If that's the situation, then a Person, an Address, and a PersonAddress
table would be appropriate. (and a new/revised zip code for one of those
addresses only needs to be altered in one record!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks. There is alot more information tied to the spouse that will not
directly relate to other dependents and that is why I am listing them in
different tables.

Another question. When I am creating the relationships it is only showing me
a one-to-one in the relationship type when joining the PK autonumber to a PK
long int field, is there something else I need to do?

"Armen Stein" wrote:

On Wed, 8 Jul 2009 07:56:02 -0700, cmc096
wrote:

Parent Table
tbl_Service_Member_Info
SM_ID (autonumber_PK)
L_Name
F_Name
Rank
Address
Phone

Child Table
tbl_Spouse_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

tbl_Dependent_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

My question is with this design will it allow me to have a one to many
relationship with the Parent tbl (Service_Member_Info) to the Child table
(Dependent_Info)?

Yes. One Service Member can have many Children.

Also, will one service member then be linked to the spouce
and dependent tables using the autonumber to link them and track for instance
multiple dependents?

Yes. Each Service Member can also have many Spouses. But I don't
recommend it.

Note that a Child is not directly related to a Spouse, so your design
can't determine which Spouse is actually little Johnny's mommy or
daddy. But maybe you don't need to know that.

Generally:
You have very similar information in both the Spouse and Dependent
tables. Some would argue that they should be one Dependent table,
with other fields like DependentType (Spouse, Child), Gender,
ActiveFlag (for ex-spouses, grown-up children, deceased), etc.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

On Wed, 8 Jul 2009 08:34:02 -0700, cmc096
wrote:

Another question. When I am creating the relationships it is only showing me
a one-to-one in the relationship type when joining the PK autonumber to a PK
long int field, is there something else I need to do?

Yes. Give each child table ITS OWN PK. You *cannot* use the SM_ID as the
primary key of the child table since a primary key is, by definition, unique -
you can only have one child record for a give SM_ID because you're
(erroneously) using it as the primary key. It's a foreign key which should be
indexed but not unique.

--

John W. Vinson [MVP]