Using MS Access/Table design


I have just started doing a database for stock control. I've made all the tables but I have a question about how to normalize the addresses. I'm not going to keep records of customers but i have a suppliers and an employees table. Both of them have the same address fields like Address1, Address2, City, Zip etc. From what I've read I should normalize it and make a separate Address table?

Do I have to make two address tables, one for suppliers and one for employees? Or if i make one address table, how would I differentiate between suppliers addresses and employees addresses?

Thank you

The only reason to use a separate Address table in this situation is if you have a lot of duplicate addresses. While normalization is a highly desirable goal, there is such a thing as too much normalization. The main purpose of normalization is to reduce redundant data. So if you have a lot of suppliers at the same address, then it may be a good idea to create a supplier's address table. Same would be true if a lot of employees share the same address.

If you do decide you need an address table, I would create one table. There is really no need to identify an address as an employee or supplier address. While it might be unlikely that an employee and a supplier would share the same address, there is still no need to identify the source of the address.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.

[an error occurred while processing this directive]