Using MS Access/Table design

Advertisement


Question
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

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.