customer, supplier, employee vrs Contacts

[Russ via AccessMonster.com]

What is the best way (pros and cons) do I have a table for customers a table
for suppliers and a table for employees, or just one table called contacts
linked to a table called contact types?
Any advise would be great.

 

[Pat Hartman\(MVP\)]

Start by identifying the common attributes for each entity. Customers and
suppliers should be pretty much identical and in fact, a customer might also
be a supplier so these should probably be a single table. Whether you
include contacts or not depends on what type of application you have.

In several applications, I have used the concept of an "entity". The entity
may be a company or a person. For those applications, all the common
attributes were stored in the entity table. Two separate tables one for
companies and one for people were defined with a 1-1 relationship to entity
to hold the unique data. The separate tables are not necessary unless you
end up with large numbers of null fields for one type of entity or the
other.

These applications also used role tables to specify valid roles for each
entity as well as separate address tables since many entities shared the
same address and having a separate table made address changes easier. There
was a relation table to link the addresses to an entity and a role table to
identify which role an address played. So you could have a billing address
and a shipping address defined in the relation table and they could point to
the same address or to different addresses.