By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Table Design

P: 1
Looking for suggestions on the best way to table design the following data.

My records consist of FName, LName, SignificantOtherFName, SignificantOtherLastName, Address, City, State, ZipCode, phone#. As is common today, some people are married using same last name where others are not.

Some records have only one Name.
Some records have two names with the same last name.
Some records have two names where both first/last are different.

Some records have one address.
Some names are associated with two addresses. When I run into this, I find the names entered twice - once for each address.

I'm trying to convert this to Access to normalize and prevent duplicates as well and force consistency with data entry.

I can easily create an Address table and create a one-to-many table relationship. This is NOT my problem.

My problem:

I need to be able to list family names and their current address - We send mailings out twice a year and some people live in different parts of the country in winter/summer.

I also need to be able to search on Jane Doe - separately from John Doe as Jane might be connected to a volunteers table later on where John would not be involved in volunteer work.


So far, I am able to separate PriNames and SecNames into two tables with a one-to-one relationship. I can then create an address table that has a one-to-many relationship to the PriNames table.

I would like to have ONE table with names in it but, I can't figure out how to identify that John Doe and Jane Doe are partners (husband/wife or significant other). Not sure this is doable.

I believe having all names in one table would allow me to create a one-to-many relationship to a volunteer table later on.

Any suggestions on how best to design the tables/relationships to make this work properly?
4 Weeks Ago #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,371
First - have you read thru this first (you might just read thru as a refresher so that we have the same nomenclature):
home > topics > microsoft access / vba > insights > database normalization and table structures

My thought is this...

These first should be straight forward
tbl_address
tbl_members
tbl_phone
tbl_account
-
From the names I hope that the these are obvious (they make sense to me but I'm abit unusual), they are all many-to-many
tbl_joinAccountMembers
tbl_joinAddressMembers
tbl_joinPhoneMembers
-

So, say you have
>personA
>PersonB
>PersonAB child to A and B
>PersonC

-
In the tbl tbl_joinAccountMembers you would have a record entry for each relationship (I'll use the first three persons)
Expand|Select|Wrap|Line Numbers
  1. [PK][FK_Acccount][FK_Members]
  2. [1 ][10000      ][1         ]
  3. [1 ][10000      ][2         ]
  4. [1 ][10000      ][3         ]
Same setup for the other join tables.

Your queries should be able to handle pulling the data...

Does that make sense?
4 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.