Autonumber as Primary Key and Relationships

[cmc096]

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.

 

[John W. Vinson]

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.

 

[Clifford Bass]

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

 

[Jeff Boyce]

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]

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

 

[cmc096]

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?

 

[Armen Stein]

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

 

[Jeff Boyce]

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

 

[cmc096]

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?

 

[John W. Vinson]

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.

 

[Clifford Bass]

Hi Jeff,

Makes sense to me. Depending on the situation, I could easily end up
with something similar.

Clifford Bass

 

[Clifford Bass]

Hi,

I might suggest the following instead; subject to
modification/refinement as needed and subject to it not being overkill to
your situation.

Again, one table for people that would include all of the service
members, the spouses and the dependents. An address table, a telephone table
and some junction tables (people/spouse, people/dependents, people/address
and people/telephone).

tblPeople
Person_ID (autonumber, primary key)
Last_Name
First_Name
Rank
other person-specific information

tblAddresses
Address_ID (autonumber, primary key)
Address
City
etc.

tblTelephones
Telephone_ID (autonumber, primary key)
Telephone_Type
Telephone_Number
maybe other info such if it can accept text messages

tblPeople_Spouses
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Spouse_ID (long int, connected to tblPeople.Person_ID, part of primary key)
other spouse-specific information

tblPeople_Dependents
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Dependent_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
other dependent-specific information

tblPeople_Addresses
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Address_ID (long int, connected to tblAddresses.Address_ID, part of
primary key)
other information specific to this person/address combination if needed

tblPeople_Telephones
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Telephone_ID (long int, connected to tblTelephones.Telephone_ID, part of
primary key)
other information specific to this person/telephone combination if needed


However if you want to keep your current design: Unless you are
allowing for polygamists and such, you only need the SM_ID in the spouse
table (one person has one spouse). However for the dependent table you would
need need an additional field in your primary key so that you can have
multiple dependents for each service number. If I were doing it, I probably
would add an integer dependent number field that would start with 1 and go up
for each service member. So service member 1 might have dependents numbered
1 and 2; and service member 2 might have dependents numbered 1, 2, 3 and 4.
This is easy enough to automate in a form so the user would not have to enter
the number.

Hope that helps,

Clifford Bass

 

[cmc096]

If I send a screen shot of the tables in the relationship view can someone
help me out with linking them together?

 

[Clifford Bass]

Hi,

Not really anything to the actual setup of the relationships once you
have decided on the table structures. Just click and drag from one table's
field to the other table's field. So you would click on the
tbl_Service_Member's SM_ID and drag that to the tbl_Spouse_Info's SM_ID. You
can add other fields if needed for relations that require multiple fields.
Double-click the linking line to set some of the link's properties.

Clifford Bass