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