1. Registered User
Join Date
Sep 2003
Posts
228

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. Registered User
Join Date
Jun 2004
Location
Seattle, WA
Posts
601
Think of this...Can models own customers?

3. Registered User
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. Registered User
Join Date
Dec 2003
Location
Dallas, TX
Posts
1,004
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 your third table with the PrimaryKey from both tables just created,(they are now ForeignKeys).

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. Registered User
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. Registered User
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. Registered User
Join Date
Dec 2003
Location
Dallas, TX
Posts
1,004
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
•