I am trying to design a DB for my company. We are a Manufacturers Rep Agency.

Here is my situation.

I have contacts that have certain properties. NONE of the contacts have the same properties (except for the normal, City, State, Zip, etc.).

For instance:

"Customer_A" buys from "Sales_User1" who represents products from "Manufacturer1" & "Manufacturer2"

while

"Customer_B" buys from "Sales_User2" who represents products from "Manufacturer1" & "Manufacturer3" & "Manufacturer4"

Any of my "Sales_User"'s can sell to any of my "Customer"'s who can buy product from any of the "Manufacturer"'s we represent.

Not that it makes a difference, but we have 10 Sales_Users, 25 Manufacturers, and 1000 Customers.

Someone please help on how to structure my tables and relationships!!!

Thanks kindly,

What does a "contact" represent in your company? Is it an occurance of a Customer who buys from a SalesRep who represents a manufacturer?

Can you also explain how the contacts all have different properties?

Thanks

A "contact" is defantly a company or even a client so to speak. But I don't know if I like your choice of words with "occurance". We are definatly adding new customers all the time, but it's more along the lines of keeping our customers general history. Not anything like from a purchase order aspect. Not exactly what they're buying from the manufacturer, just that they've bought in the past. (I hope that helps explain my position a little more)

But anyway my "contact" table currently has the following properties:

Contact_ID
Name
Address
City
State
Zip_Code
Contact
Title
Email
Phone
Fax
Sales_User_ID (from different table)
Type_ID (from different table)

Any insight would be great!

Thanks!

can an organsiation have more than one contact, for the same address, if so then your address is redundant in the contact table

a phone number may be appropriate if the phone number is unique to that contact, but not if its generic to all contacts at that organsiation.

can more than one sales rep go to the same organisation either covering the same proiducts, or different products

how do you identify which reps cover which manufacturers
how do you indentify which customers are interested in which type of product / manufacturer.

I'm probably not explaining myself that well because of my loose organization, but I'll try and make some sense of it this time. And I think my choice of words this time is not that great. The word "contact" is just a company that is in our current "Contacts Spreadsheet". This spreadsheet contains all of our "contacts". But "contacts" in this case are synonymous with customers. Our contacts are our customers. So maybe I'll just change the column header Contact_ID to Customer_ID to make a little more sense of it.

Anyway, maybe now that that is straightened out...

Yes, more than one sales rep can call on the same organization but always covering different products.

ALL sales reps cover ALL manufacturers.

Potentially any customer can buy from any manufacturer/product. But we generalize it with "Types" as in "Type_ID" that I have contained in a different table. For example; Type: IR = Irrigation, Type: HV = Heating & Ventilation, Type: ME = Mechanical Engineer.

Again, thanks so much for the responses and help!!!

I recommend you go back to basics and think about the Data Model and the fundamental 'Things of Interest'.

What you call a Contact is, of course, an Organisation.

Check out this Data Model which shows Contacts as people who represent Organisations :-
http://www.databaseanswers.org/data_...acts/index.htm

B.Dimple
Senior DBA

Thank you for the link... very fundamentally informative.

And yes, what I call a contact is an organization. But I believe that not to be my problem, as that is just verb-age.

In your model where would/should the address information be stored? With the Company Profile or the Company Contact?

Also, I need our Sales_Users to be directly related with our customers. How would I do this. Your model suggests a stand alone table of Our Employees but does not tie it in with any of the Companies, Contacts or Products?

Thanks again for the responses!!! Keep'em coming if you can!!!