Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    228

    Unanswered: Help with relationships

    I have a table containing all 'customers' and a table containing all 'model numbers'. The thing a don't have is a connection between 'model numbers' and 'customers'

    First off, I'm not sure which way is better to look at:
    A. Each model can have multiple customers OR
    B. Each customer can have multiple models?

    Second, lets say I have a common field and I create a one to many relationship. The table will have many duplicate entries with only a customerID or ModelID differentiating them. That is why I don't want to create a direct relationship between these two tables. Do I need a 3rd table to relate the data between customers and models?

    I am new so be easy on me.

  2. #2
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Think of this...Can models own customers?
    Ryan
    My Blog

  3. #3
    Join Date
    Sep 2003
    Posts
    228
    No they cannot. So you are suggesting that the relationship be one Customer to many Models.

    Maybe this is a many to many relationship which I have not dealt with before.
    Model 'A' can belong to many customers and Customer 1 can have many models.

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi otto,

    Let's take it from the top. You have Customers and you have Models
    1. What is a Model? Is it something that ONLY (1) customer at a time can have? Is it something MORE than (1) customer SHARES at the SAME time?
    IF, only (1) customer can have a certain model at one time, that is a One-To-Many. IF, MORE than (1) customer can share the same model at the same time, you need a third table for your Many-To-Many relationship.
    Create both your Customer table and your Model table.
    Create your third table with the PrimaryKey from both tables just created,(they are now ForeignKeys).
    Set your CascadeUpdate/Deletes and Enforce Referrential Integrity.

    Give it a whirl and see where that lands ya. That should get you off the ground to a safe start.
    Just remember, if only ONE customer can have many models at a time and ONLY one customer, then Customer is the ONE side and Models is the Many side of the relationship.
    If the opposite is true then simply Reverse that logic.
    Last, if BOTH are true then the Many-To-Many join table is needed.

    have a nice one,
    Bud

  5. #5
    Join Date
    Sep 2003
    Posts
    228
    Model is a model # of a product sold.

    Customer 1 can own model #'s 1000, 3000 and 5000.
    Customer 2 can own model #'s 2000 and 4000.
    Customer 3 can own model #'s 1000, 2000, 3000, 4000 and 5000.

    So each customer can have many models and each model# can be owned by many customers.

    Do you still feel this should be designed as a many to many relationship?

  6. #6
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Yes buddy you have to work with a many-many relationship. Its very easy to do have another table and call it as CustomerModel. Have atleast 2 primarykeys in it. These two primary key will be the foreign key of both Customer and Model tables. Link all the keys and there you go..!!!!

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Like khan said, and I as well. That is a prime case for Many-To-Many. Armed with the tips we have now given you, your database should be on it's way to completion. 8-)

    If you still need assistance later, you know where to reach us.

    have a nice day all, I'm outta here and headin home....

    Bud

Posting Permissions

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