SQL 模型/架构设计、检查约束、FK 或表重新设计？
我有以下模型（简化后，忽略所有其他字段，重点关注主键 ID）：
[invoice]
invoice_id
customer_id
invoice_description
[customer]
customer_id
company_id
customer_name
[company]
company_id
company_name
[strategy]
strategy_id
strategy_descripton
一家公司包含许多客户，而这些客户又可以拥有许多属于该客户的发票。
虽然上面没有显示，但我也希望有一份属于一家公司（并且只有一家公司）的策略列表。
我希望客户、发票和公司能够定义一个“默认策略”，引用他们所属公司的策略。
我知道我可以在每个表（发票、客户和公司）中添加一个名为strategy_id的额外FK字段，但是我如何确保如果进行更改（有人试图将策略转移到另一家公司），我们最终不会得到发票或客户指向属于另一家公司策略列表的策略？
我将如何处理这个问题 - 我是否需要在业务逻辑中检查所有这些，或者使用检查约束，或者这可以通过表重新设计和外键来实现吗？
预先非常感谢，
克里斯
I have the following models (simplified leaving all other fields out, focusing on primary key ID's):
[invoice]
invoice_id
customer_id
invoice_description
[customer]
customer_id
company_id
customer_name
[company]
company_id
company_name
[strategy]
strategy_id
strategy_descripton
A company contains many customers, which in turn can have many invoices belonging to that customer.
Although not shown above, I also wish to have a list of strategies, that belong to a company (and only one company).
I would like the customer, invoice, and company, to be able to define a 'default strategy' which references a strategy of the company to which they belong.
I know i could put an extra FK field called strategy_id in each table (invoice, customer and company), but how can i ensure that if changes are made (someone tries to move a strategy to a different company) we do not end up with an invoice, or customer pointing to a strategy that belongs in another company's strategy list?
How would i deal with this - Do i need to check all this in business logic, or use check constraints, or can this be achieved through table redesign and foreign keys?
Many thanks in advance,
Chris
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果公司改变策略，您是否介意属于该公司的客户或发票改变其策略。
例如：如果公司 1 有策略 1。它有 customer1、customer2（有发票1）。那么，如果company1 将其发票更改为strategy2，那么customer1、customer2 和invoice1 属于strategy2 或strategy1 是否可以。
如果没关系，请执行以下操作：
否则，如果确实重要的话：
如果您想要更具体的答案，则必须澄清。
Do you mind if customer or invoice that belong to a company change their strategy if the company changes their strategy.
For example: If company1 has strategy1. It has customer1, customer2 (having invoice1). Then if company1 changes it's invoice to strategy2 is it ok if customer1, customer2 and invoice1 belong to strategy2 or strategy1.
If it doesn't matter do:
Otherwise if it does matter:
If you want a more specific answer you will have to clarify.
如果我理解正确的话，你希望每个公司都有很多战略，每个战略都属于一个公司（因此公司和战略之间存在
1:n关系）。每家公司都有一个默认策略，这也是所有客户及其发票的默认策略。您可以通过添加一个与
[strategy]存在1:1关系的表[company_default_strategy]将这些约束定义为外键约束> 表（某些策略是默认的）：If I understand correctly, you want every company to have many strategies, every strategy belonging to one company (so a
1:nrelationship between company and strategy). And also one default strategy for every company which is is also the default strategy for all its customers and their invoices.You could define these constraints as foreign keys constraints by adding one more table
[company_default_strategy]which is in a1:1relationship with the[strategy]table (some strategies are default):