One Table or Split It?

[daisy]

I am creating a database containing accounting
information. I read the Normalizing articles and watched
the Webcast, but I still have a question:
There will be information on Customers, Vendors, and
Employees. Since many of the fields will be the same
(name, address, phone, etc) should I use one
big "Contacts" table, with a "Relationship" field
indicating whether it's a customer, vendor, or employee?
Or should I divide it up into three separate tables?
There are some fields that would be unique to each type of
record, so those fields would be blank sometimes if I used
one big table, which I hear isn't good. But then again,
you're not supposed to have a bunch of tables holding a
lot of the same type of information, right?

I appreciate any help.

 

[Allen Browne]

Daisy, this is a good question. There is no cut'n'dried answer though.

It's a good idea to include the similar data in the one table where that is
practical. It can greatly simplify further relationships. For exmple, in a
table of payments, you can easily make a payment to a vendor or to a staff
member since these are both in the same table.

You can use a field to distinguish them if you are certain that no client
can have more than one role. However, if it is possible that an employee may
also be a customer (employees do buy from their firms), it may be better to
create a table of roles (lookup values such as Customer, Employee, Vendor),
and then a junction table containing the ClientID and RoleID (for each valid
combination).

OTOH, if there are lots of unique fields for Vendors (say), and further
1-to-1 relationships are not appropriate, you may have a reason to go with
the 3 separate tables and just enter the same person in multiple tables
where necessary. This would not be my preferred option if the single table
is workable, but take your pick.

 

[daisy]

Thanks for your response, Allen. ... Maybe you could give
your opinion on a related question?

Our customers are companies, rather than individuals, and
we typically have more than one contact with each
company. Rather than have several entries with the same
company name, address, etc, do you suppose I should have a
separate "Companies" table with information that is common
to the whole company, and therefore all of the contacts
for that company? Then in the Contacts table, I could
just enter the CompanyID as a foreign key. This is
probably a pretty basic question, but sometimes it feels
like I'm breaking stuff up into too many tables....

 

[Allen Browne]

Definately!

If would be wrong to enter the company info again and again on different
contracts.