数据库模式一致性问题
我的数据库架构的一部分涉及实体：
Jobs
Agencies
Agents
and relation JobAgent
- 每个作业都有一个属于它的代理机构
- 每个代理属于一个代理机构
- 每个作业有 0-n 个代理
数据库将是 SQL Server 2008
这是我的架构：
我的问题是，通过 JobAgent 关联时，Jobs.agencyid 必须始终等于 Agents.agencyid。 如果 Jobs.agencyid 要更新为新的代理机构，则代理将属于与作业不同的代理机构。
重新设计架构以避免依赖触发器或应用程序代码来确保这种一致性的最佳方法是什么？
Part of my database schema involves the entities:
Jobs
Agencies
Agents
and relation JobAgent
- Each Job has one Agency it belongs to
- Each Agent belongs to one agency
- Each Job has 0-n agents
The database will be SQL Server 2008
Here is my schema:
My problem is that Jobs.agencyid must always be equal to Agents.agencyid when related through JobAgent.
If Jobs.agencyid were to be updated to a new agency, The Agents would then belong to a different Agency than the Job.
What would be the best way to redesign my schema to avoid relying on triggers or application code to ensure this consistency?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
机构
职位
代理
JOBAGENT
您可以对某一列定义多个外键约束 - 这仅意味着 JOBAGENT 中的值必须满足两个外键约束才能被允许。但是，如果您想将作业更新到不同的机构，您会很开心...;) SQL Server 支持复合外键：http://msdn.microsoft.com/en-us/library/ms175464.aspx
关于更新的更新
您有两种选择 -
ON UPDATE CASCADE 等在不使用触发器的情况下不会处理代理和代理更新AGENCIES
JOBS
AGENTS
JOBAGENT
You can define more than one foreign key constraint to a column - it just means that the value in JOBAGENT has to satisfy BOTH foreign key constraints to be allowed. But you'll have fun if you ever want to update jobs to a different agency... ;) SQL Server supports composite foreign keys: http://msdn.microsoft.com/en-us/library/ms175464.aspx
Update Regarding Updating
You have two choices -
ON UPDATE CASCADEetc won't handle agency and agent updates without using triggers问题是，如果一项工作从一个机构转移到另一个机构（如您所说，如果 Jobs.agencyid 要更新...），那么 JobAgent 中的相应记录将变得毫无意义：这些代理不能 附加到不再属于其代理机构的工作，因此应删除将其连接到工作的 JobAgent 记录...
强制执行此操作的一种方法是添加 JobAgent.agencyid 字段，并使其成为外键Jobs.agencyid，使用 ON UPDATE RESTRICT 强制（手动）删除相关 JobAgent 记录，然后才能更改 Jobs.agencyid。
编辑：另一个我没有真正考虑过的问题是，当您第一次将作业与代理关联时（即创建新的 JobAgent 记录），您需要确保它们都属于同一个机构...为此，我认为 OMG 的解决方案效果最好 - 我很高兴遵循更好的答案。
OMG 还提出了如何处理更新的问题：您可以
更改 Jobs.agencyid 字段并删除（手动）所有关联的 JobAgent 记录：在这种情况下，旧代理不再处理此作业，您可以指派新代理机构中的某人来处理该作业。
更改 Jobs.agencyid 字段并还更改所有关联的 JobAgent 记录（即所有这些代理随工作转移到新代理机构） - 但这是 非常混乱，因为这些代理还将与仍属于原代理机构的其他工作相关联。
按照 OMG 的建议，创建一个新的 Jobs 记录，并将旧的记录标记为已失效（以便稍后删除）。
如上所述，但保留失效的作业记录以保留历史信息。
选择 3 还是 4 在一定程度上取决于您的系统的用途：您是否只想维持“谁拥有哪些工作”的当前状态？或者您是否需要保留某种历史记录，例如，如果工作附有账单记录...该信息需要与原始机构保持关联（但这都超出了您原始问题的范围）。
The problem is that if a job moves from one agency to another (as you say, if Jobs.agencyid were to be updated...) then the corresponding records in JobAgent become meaningless: those agents can't be attached to a job that's no longer with their agency, so the JobAgent records connecting them to the jobs should therefore be deleted...
One way to enforce this is to add a JobAgent.agencyid field, and make it a foreign key on Jobs.agencyid, with ON UPDATE RESTRICT to force (manual) deletion of the relevant JobAgent records before Jobs.agencyid can be changed.
Edit: the other issue, which I hadn't really considered, is that when you first associate a job to an agent (ie create a new JobAgent record) you need to ensure they both belong to the same agency... for this, I think OMG's solution works best - I'm happy to defer to the better answer.
OMG also raises the question of how to handle updates: you can either
Change the Jobs.agencyid field and delete (by hand) all associated JobAgent records: in this case the old agents no longer work on this job, and you can assign someone from the new agency to work on it.
Change the Jobs.agencyid field and also change all associated JobAgent records (ie all those agents move with the job to the new agency) - but this is very messy, because those agents will also be associated with other jobs that are still with the original agency.
As OMG suggests, make a new Jobs record and mark the old one as defunct (for later deletion).
As above but keep the defunct Jobs record to preserve historical information.
Whether you choose 3 or 4 depends a bit on what your system is for: do you just want to maintain the current state of who-has-which-jobs? or do you need to keep some kind of history, for example if there's billing records attached to the job... that info needs to stay associated with the original agency (but this is all outside the scope of your original question).
您可以将
ON UPDATE CASCADE与外键一起使用。请参阅此维基百科页面。或者，如果
agencyid是您期望可变的内容，您可以为其设置唯一约束，并使用其他一些无意义的字段作为代理 ID（例如，自动增量列）。You could use
ON UPDATE CASCADEwith the foreign keys. See this Wikipedia Page.Or maybe, if
agencyidis something that you expect to be mutable, you can have a unique constraint for it and use some other meaningless field for the agency id (say, an auto-increment column).下面的方案能回答你的问题吗？
通常，我为每个表都有一个单字段主键，因为更容易匹配表上的注册表并在下面的表中引用它。因此，按照这种方法，AgientatiatedJob 至少将具有以下字段：
Does the following scheme answer your question?
Normally, I have a single-field primary key for every table, because it is easier to match a registry on a table and to refer it on tables below. So following this approach the AgientiatedJob would have at least the fields: