Normalisation help needed please for Contact section of DB

[KellyGroom]

Hello. I wonder if anyone can help me please with my normalisation. I am fairly new to access and do not write code. I am using Access 2007.

I am currently creating a database which will include a contact details area for each individual. Each individual though could have multiple addresses and multiple uses for each address. The end user would like to see on screen at once, the Individual’s full name and all their addresses and their uses.

For instance, an individual could have a home address, a business address, their secretary’s address and an address they would like their correspondence mailed to. Some of these addresses may even be used for joint purposes, i.e. the business address may also be the secretary’s address, and even possibly the correspondence address.

As some of the addresses are business addresses these may well be used by other individuals in the database too.

Finally, to add further confusion, some secretaries have shared responsibilities and work for more than one individual.

My table proposal is as follows and I would be very grateful if anyone could take a look and shed any light or advice on it for me.​

Individual Table​

IndivID (Text – e.g. AO200) Primary Key
Title
Forename
Surname
DOB
Job Title
​

Address Table​

AddID - Autonumber
Address Line 1
Address Line 2
Address Line 3
Address Line 4
Postcode
Main Tel No
Main Fax No
​

Secretary Table​

SecID - Autonumber
Secretary Name
Direct Tel
Direct Fax
Email

I am then left with what to do to identify and link each address type (business, home, etc) with the individual whilst displaying all addresses even if they are repeated due to multiple uses, their secretary’s contact details (if they have one) and also each individual’s unique contact details such as mobile telephone number, e-mail address and car registration.

I have debated creating additional tables, as below, but am unsure of how I would link and pull all the data together in one form for the end user, which leads me to think that I have not grasped normalisation.
​

Address Types Table​

AddTypID – Autonumber
Address Type
​

Unique Contact Table​

IndivID
Mobile
E-mail
Car Registration
​

Contact Details Table​

(to pull it all together??)
IndivID
AddID
AddTypID
SecID

I hope that all made sense. Any help or comments would be appreciated. Many thanks. ​

 

[KellyGroom]

Thank you for your quick reply. I have looked at the link attached but I am afraid I am still somewhat unsure. Could I please ask you though to clarify a couple of points for me if you wouldn’t mind as I am a little confused?

I now have the following 3 tables...
​

tblIndividual​

IndivID - Primary Key
Title
Forename
Surname
DOB
Job Title
E-mail
Car Reg
Mobile
​

tblAddress​

AddID - Primary key
Address Line 1
Address Line 2
Address Line 3
Address Line 4
Postcode
Main Tel No
Main Fax No
​

tblContactDetails​

IndivID
AddID
​

You mentioned that possibly a many to many table would help me associate the secretary. Would the tbl Contact Details not do that or would I need another many to many table? Also, I am unsure as to where I would now store the Secretary information? Although they are also individuals, we only need to record their direct contact details and association with which individuals they work for.

Many thanks!​