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?
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.
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..!!!!