SQL Server架构设计
我有几个产品 - 工厂关系，需要在 SQL SERVER 中创建表。一种产品（大约 8000 种）由所有 8 个工厂生产，但很少（8000 种产品中只有 3-4 种）由 1/2 家工厂生产。
我正在考虑通过两种方式之一来建立关系。 （我知道第一种方法更好，但第二种方法更容易设计和开发应用程序代码）。
1 - 多对多关系，基本上创建第三个链接表 ProductPlant。
2 - 产品表中可为空的 PlantID 外键列。产品表的 PlantId（所有工厂生产的产品）将为 Null，如果是在工厂生产，则为 PlantId。
请提供您的专家意见。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果选择#2，你就会把自己逼到墙角。
首先，如果不在产品表中仅针对一种产品输入两行，则无法指定一种产品是在两个工厂生产的。如果你这样做，你的应用程序开发将会变得非常混乱。
其次，您无法应对企业中增加一家新工厂的可能性，该工厂最初不生产任何产品。这可能永远不会发生。但如果是这样，你就陷入困境了。
第三，你不能在产品组合中添加一种最初不在任何地方生产的新产品。你不能用像#2 这样的模式来这么说。
第四，您必须在各处编写这样的选择标准：“其中 ProductID = CurrentProductID 或 ProductID 为空”。这对你来说可能看起来很简单，但对我来说肯定不是。我宁愿处理额外的连接。
好的设计原则是好的，并不是因为某个大祭司这么说的。它们很好，因为它们在各种情况下都能发挥作用。
如果您有多对多关系，请使用联结表。如果你不这样做你会后悔的。
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.
创建一个名为
ProductPlant或其他名称的表。以下是列：拥有在某些情况下意味着一件事而在其他情况下意味着另一件事的数据从来都不是一个好主意。这可能会导致您错误地读取数据。
另外，你可以通过这种方式更好地规划未来，因为后来，当有人来找你说，现在产品是在这些其他工厂生产时，你就已经得到了保障。
Create a table called
ProductPlantor something. Here are the columns: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.
我同意其他发帖者关于创建 ProductPlant 表来标识具有特定工厂（或如果需求发生变化则为工厂）的产品的观点，但我会更进一步说，您只需要填充该表以获取例外情况，而不是全部 8000 种产品。我会在名为 IsAllPlants 的产品上添加一个位标志（或者可能是一个计算字段？）。对于那些未设置该标志的情况，将填充 ProductPlant 表。然后通过 PlantID 查询查找产品将非常简单：
SELECT * FROM Product WHERE IsAllPlants != 0 OR ProductID IN (SELECT ProductID FROM ProductPlant WHERE PlantID = @PlantID)
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)