SQL关系
我正在使用 MS SQL Server 2008R2，但我相信这与数据库无关。
我正在重新设计一些 sql 结构，并且正在寻找建立一对多关系的最佳方法。
我有 3 个表，公司、供应商和公用事业，其中任何一个都可以与另一个名为 VanInfo 的表建立一对多关系。
货车信息记录可以属于公司、供应商或公用事业公司。
我最初在VanInfo表中有一个指向公司表的company_id，但是当我添加供应商时，他们也需要vaninfo记录，所以我在VanInfo中为supplier_id添加了另一列，并设置了一个约束，即supplier_id或company_id是设置，另一个为空。
现在我已经添加了实用程序，现在它们需要访问 VanInfo 表，并且我意识到这不是最佳结构。
建立这些关系的正确方法是什么？或者我应该继续向 VanInfo 表添加外键？或设置某种交叉引用表。
该应用程序在技术上尚未上线，但我想确保它是使用最佳实践来设置的。
更新： 感谢您的快速回复。
我已阅读所有建议，检查了所有链接。我的主要标准是易于修改和维护，因为客户的需求总是会在没有太多通知的情况下发生变化。经过学习、研究和规划后，我认为最好使用名为“组织”的交叉引用表，以及公司/公用事业/供应商与组织表之间的 1 对 1 关系，从而与 Vaninfo 表建立清晰的关系。这将很容易维护，并且仍然可以正确地建模我的业务对象。
I'm using MS SQL Server 2008R2, but I believe this is database agnostic.
I'm redesigning some of my sql structure, and I'm looking for the best way to set up 1 to many relationships.
I have 3 tables, Companies, Suppliers and Utilities, any of these can have a 1 to many relationship with another table called VanInfo.
A van info record can either belong to a company, supplier or utility.
I originally had a company_id in the VanInfo table that pointed to the company table, but then when I added suppliers, they needed vaninfo records as well, so I added another column in VanInfo for supplier_id, and set a constraint that either supplier_id or company_id was set and the other was null.
Now I've added Utilities, and now they need access to the VanInfo table, and I'm realizing that this is not the optimum structure.
What would be the proper way of setting up these relationships? Or should I just continue adding foreign keys to the VanInfo table? or set up some sort of cross reference table.
The application isn't technically live yet, but I want to make sure that this is set up using the best possible practices.
UPDATE:
Thank you for all the quick responses.
I've read all the suggestions, checked out all the links. My main criteria is something that would be easy to modify and maintain as clients requirements always tend to change without a lot of notice. After studying, research and planning, I'm thinking it is best to go with a cross reference table of sorts named Organizations, and 1 to 1 relationships between Companies/Utilities/Suppliers and the Organizations table, allowing a clean relationship to the Vaninfo table. This is going to be easy to maintain and still properly model my business objects.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
就你的例子来说，我总是会选择“某种交叉引用表”——向 VanInfo 表中添加列。
最终，您的 SP 中将会有更多的连接，但我认为开销是值得的。
With your example I would always go for 'some sort of cross reference table' - adding columns to the VanInfo table smells.
Ultimately you'll have more joins in your SP's but I think the overhead is worth it.
当您设计数据库时，您不应该考虑主键/外键的位置，因为这些概念不属于设计阶段。我知道这听起来很奇怪，但你也不应该考虑桌子！ （你可以使用 XML/Files/Whatever 来实现你的 E/R 模型
坚持 E/R 关系设计，您应该只识别您的实体（在您的情况下是公司/供应商/公用事业/vanInfo），然后考虑它们之间存在什么样的关系（如果有的话）。例如，您说公司可以拥有一个或多个 VanInfo，但 VanInfo 只能属于一个公司。正如您已经猜到的那样，我们正在谈论一对多关系。此时，当您将设计模型（一对多关系）“转换”为数据库表时，您将知道在哪里放置键/外键。在一对多关系的情况下，外键应该位于“Many”一侧。在这种情况下，货车信息将具有公司的外键（因此货车信息表将包含公司 ID）。对于所有其他表格，您必须遵循这种方式
查看以下链接：
https://homepages.westminster.org.uk/it_new/BTEC%20Development/Advanced/Advanced%20Data%20Handling/ERdiagrams/build.htm
When you design a database you should not think about where the primary/foreign key goes because those are concepts that doesn’t belong to the design stage. I know it sound weird but you should not think about tables as well ! (you could implement your E/R model using XML/Files/Whatever
Sticking to E/R relationship design you should just indentify your entity (in your case Company/supplier/utilities/vanInfo) and then think about what kind of relationship there is between them(if there are any). For example you said the company can have one or more VanInfo but the Van Info can belong only to one Company. We are talking about a one – to- many relationship as you have already guessed. At this point when you “convert” you design model (a one-to many relationship) to a Database table you will know where to put the keys/ foreign keys. In the case of a one-to-Many relationship the foreign key should go to the “Many” side. In this case the van info will have a foreign keys to company (so the vaninfo table will contain the company id) . You have to follow this way for all the others tables
Have a look at the link below:
https://homepages.westminster.org.uk/it_new/BTEC%20Development/Advanced/Advanced%20Data%20Handling/ERdiagrams/build.htm
考虑将
Com、Sup和UtilPK 设为GUID，这应该足以解决问题。然而，这个问题可能是数据库设计不佳的一个很好的指标，但是要提出一种不同的解决方案，人们应该了解更广泛的数据库上下文，即您正在尝试实现的目标。对我来说，这似乎VanInfo应该只是每个表的一个单独的实体（是的，完全重复，如Com_VanInfo、Sup_VanInfo等） ，除非 VanInfo 不在该实体之间共享（那么关系应该颠倒，即Com、Sup和Util应包含 FKVanInfo）。Consider making
Com,SupandUtilPKs aGUID, this should be enough to solve the problem. However this sutiation may be a good indicator of poor database design, but to propose a different solution one should know more broad database context, i.e. that you are trying to achive. To me this seems like aVanInfoshould be just a separate entity for each of the tables (yes, exact duplicate likeCom_VanInfo,Sup_VanInfoetc), unless VanInfo isn't shared between this entities (then relationships should be inverted, i.e.Com,SupandUtilshould contain FK forVanInfo).您的数据库基本上需要规范化，我认为您的数据库应该采用第五范式，其中有两个表由一个表链接。请参阅这篇文章，这会对您有所帮助：
http://en.wikipedia.org/wiki/Fifth_normal_form
您可能还想查看数据库规范化：
http://en.wikipedia.org/wiki/Database_normalization
Your database basically need normalization and I think you're database should be on its fifth normal form where you have two tables linked by one table. Please see this article, this will help you:
http://en.wikipedia.org/wiki/Fifth_normal_form
You may also want to see this, database normalization:
http://en.wikipedia.org/wiki/Database_normalization