SQL Server Schema Design

Question

I have several Products - Plants relationships that I need to create tables in SQL SERVER. A product (around 8000) is manufactured in all 8 Plants but rarely (3-4 products out of 8000) a Product is manufactured in one/two Plants.

I am thinking to implement relation-ship in either of two ways. (I know 1st approach is better but 2nd approach is simpler to design and develop application Code).

1 - Many to many relationship, basically create a third linking table ProductPlant.

2 - Nullable PlantID foreign Key Column in Products Table. Products table will have Null as PlantId (Product Manufactured in ALL Plants) or a PlantId if manufactured in a Plant.

Please provide your expert opinion.

Answer 1

Create a table called ProductPlant or something. Here are the columns:

• ProductId
• PlantId

Its never a good idea to have data that means one thing in certain situations, and another thing in other situations. That is creating the potential that you will read the data incorrectly.

Also, you are planning better for the future, this way, because later, when someone comes to you and says, now a product is manufactured in these other plants, you are already covered.

Answer 2

IMO it is far better to have a third table to join the two. That way there is no artificial restriction on how many plants the products are manufactured in. If you do it the other way you'd need duplicate product records if the product is produced in more than one plant. (It may be the case that only one plant makes a product today, but what about tomorrow?)

Answer 3

I would agree with the other posters about creating a ProductPlant table to identify a product with a specific plant (or plants if requirements changes), but I would go a bit further and say that you only need to populate that table for the exceptions, not all 8000 products. I would add a bit flag (or maybe a calculated field?) on Product called IsAllPlants. For those where that flag is not set, the ProductPlant table would be populated. Then query to find Products by PlantID would be as simple as: SELECT * FROM Product WHERE IsAllPlants != 0 OR ProductID IN (SELECT ProductID FROM ProductPlant WHERE PlantID = @PlantID)

Answer 4

You are painting yourself into a corner with choice #2.

First off, you can't specify that a product is manufactured in two plants without entering two rows in the product table for just one product. If you do that, your application development is going to get very messy.

Second, you can't deal with the possibility that a new plant will be added to the enterprise, one that initially manufactures no products. This may never happen. But if it does, you're stuck.

Third, you can't add a new product to the mix, one that initially is not manufactured anywhere. You can't say that with a schema like your #2.

Fourth, you're going to have to write selection criteria like this "where ProductID = CurrentProductID or ProductID is null" all over the place. That may look simple to you, but it sure doesn't to me. I'd rather deal with an extra join.

Good design principles are good not because some high priest said so. They are good because they work, in a wide variety of circumstances.

If you have a many to many realtionship, go with a junction table. You'll regret it if you don't.