Basic Design Question

[CalPolyGrad]

Aloha,

I'm new to database design and have a basic question. I'm writing a customer database. All of the customers can have several contacts. I've created a Customer table, and also a Contacts table. The contacts table stores name, email, phone number, title, etc. However, all of the customers can also have contacts within our company (account rep, sales engineer, project manager, etc.). These contacts have the same information as the customer contacts. The only difference is that these company contacts can be the same for multiple customers (many to many relationship). Should I create a separate table for these contacts (e.g. CompanyContacts), or should I just use one table for all contacts and have a field to indicate whether or not they are a company contact?

Thanks in advance,

Frank

[brandonrunyon]

Might go with the intersection tables and then a join for the many to many.

[griswolf]

You can do it with either technique you mention, but it may be better over the long term (in maintenance) to have company_contact separate from customer_contact. This will start to matter when you start to care about something that matters for customers but not in house contacts (date of the last time we sent them something, maybe; or how many contacts we have with them) and similar for the other direction (number of times we've gotten positive ...or negative... feedback on a company person, maybe).

The general rule is to keep things that are always(*) going to be the same in the same table, and have distinct tables for things that are now or may become(*) different.
(*) My crystal ball doesn't work either, but you have to do your best...