Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Question Relationships for Sales_Users, Manufacturers, and Customers (was "Structure!!!")

    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,

  2. #2
    Join Date
    Feb 2006
    Posts
    7

    questions

    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

  3. #3
    Join Date
    Feb 2006
    Posts
    4
    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!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2006
    Posts
    4
    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!!!

  6. #6
    Join Date
    Jul 2003
    Posts
    74

    Here's an interesting Data Model

    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

  7. #7
    Join Date
    Feb 2006
    Posts
    4
    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!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •