数据库表设计；链接表或很少使用的外键？
我的问题涉及特定场景的数据库中表的最佳实践设计。
假设我们有一家销售办公设备的公司，即 Printers。该公司还向购买了一台或多台打印机的客户提供服务合同。
根据上述信息，我们可以为数据库推断出三个表：
- 客户
- 打印机
- 服务合同
因此，对于给定的服务合同，我们指定为哪个客户创建合同，并分配 1 台或多台构成合同协议的打印机。
对于作为服务合同协议一部分的打印机，我们可以通过两种方式进行数据库设计。
第一个是在 Printers 表中创建 ServiceContractID 列，并在它与 ServiceContracts 表之间创建基本的主/外键关系。我看到的方法的唯一问题是打印机不必成为服务合同的一部分，因此数据库中可能有数百甚至数千条打印机记录，其中许多记录不是合同的一部分，因此许多记录都没有使用此外键列。
第二个选项是创建一个链接表，其中包含 2 个外键，分别引用 ServiceContracts 表（它的主键）和 Printers 表（它的主键）。组合这个新表中的两列以生成唯一的复合主键。
好吧，这就是我的困境。我不认为这两种选择通常都是一个非常糟糕的主意，但我一直坚持知道这两个设计决策中哪一个是最佳实践。
欢迎所有评论。
My question relates to the best practices design of tables in a database for a specific scenario.
Say we have a Company that sells office equipment, Say Printers. This company also offers Service Contracts to Customers that have bought 1 or more of its Printers.
Given the above information, we can deduce three tables for our database:
- Customer
- Printer
- ServiceContract
So for a given service contract, we specify which Customer the contract is created for and we assign 1 or more printers that comprise the contract agreement.
With regards to the Printers that are part of the service contract agreement, there are 2 ways we could approach the database design.
The first is to create a ServiceContractID column in the Printers table and create a basic Primary/Foreign key relationship between it and the ServiceContracts table. The only problem I see with with approach is that Printers don't have to be a part of a Service Contract and therefore you could have hundreds or even thousands of Printer records in the database, many of which are not part of a contract, so having this Foreign key column not being used for many of the records.
The second option is to create a link table which would contain 2 foreign keys referencing both The ServiceContracts table (It's primary key) and the Printers table (It's Primary Key). Combining both columns in this new table to make a unique composite primary key.
OK, here's my quandary. I don't see either option as being typically a Really bad idea, but I am stuck on knowing which of these 2 design decisions would be the best practise.
All comments welcome.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为，在不了解您的整个问题的情况下，第二种选择更好。 （即通常更适当地规范化）
第二个选项将允许您可能尚未想到的一些业务规则灵活性（或者您的业务模型可能会改变）。
例如：日期可能变得很重要。例如，即使企业决定某些规则，也可以使用相同的服务合同，例如所有打印机在该客户购买后一年内保修。同一协议仅涵盖多次采购。
因此选项 2 为您提供了在关系上添加其他属性的灵活性...
I think, without knowing your entire issue, that the second option is preferable. (i.e more properly normalized -in general)
The second option would allow some business rule flexibility which you may not have come up with yet (or your business model may change).
for example: dates may become important. The same ServiceContract may for instance be used even if the business decides on some rule, like all printers are covered for one year after purchase by this customer. where the same agreement just covers many purchases.
so option 2 gives you flexibility for adding other attributes on the relationship...
如果我正确理解您的问题域，执行此操作的正确方法是选项 2。听起来客户可以拥有 0 多个服务合同，服务合同可以有 0 多个与之关联的打印机，并且打印机可以是与 0-1 服务合同相关联（除非合同到期并续签新合同，在这种情况下打印机可以有很多。
希望有所帮助。我有兴趣听听其他人的想法......
If I understand your problem domain correctly, the proper way to do this would be option 2. It sounds like a customer can have 0-many service contract, a service contract can have 0-many printers associated with it, and a printer can be associate with 0-1 service contracts (unless contracts expire and renew with a NEW contract, in which case the printer can have many-many.
Hope that helps. I will be interested in hearing what others think . . .