一对多的数据库设计，其中许多至少是一
与我的上一个问题有些相关，这是关于创建模式来强制执行表模式，其中“A”有许多“B”子项，其中“C”是“A”的子项“B”的表，但至少有一个。
目前我有：
A (A_ID primary key, B_ID reference key)
B (B_ID primary key, etc)
C (A_ID reference, B_ID reference)
重点是 A 肯定总是至少有一个“B”“子”，但也可以有更多。但是我遇到的问题是“C”表当前可以引用与“相同”的“B” A' 已经隐式引用..
示例：
A
- Id: 1
- B_Id: 37
C
- A_Id: 1
- B_Id: 37
限制此的最佳方法是什么？ 注意到“A”可以被更新以尝试引用“C”集合中已经为该“A”声明的“B”，并且更有可能的是，“C”引用了已经隐式声明的“B”相关的“A”..
希望这是有道理的，并再次提前欢呼。
编辑：表格如下：
“A”是提交，提交可以有许多“贡献者”（成员），但始终至少有一个。 “B”是会员 “C”是一个将“A”链接到多个“B”的表
Somewhat related to my previous question, this is regarding creation patterns to enforce table patterns where 'A' has many 'B' children where 'C' is the table of child 'B's for 'A', but has at least ONE.
Currently I have:
A (A_ID primary key, B_ID reference key)
B (B_ID primary key, etc)
C (A_ID reference, B_ID reference)
The point is that A definately always has at least ONE 'B' 'child', but optionally many more.. however the problem I have is that the 'C' table could currently reference the same 'B' that 'A' is already implicitely references..
example:
A
- Id: 1
- B_Id: 37
C
- A_Id: 1
- B_Id: 37
Whats the best way to constrain this? noting that 'A' could be updated to attempt to reference a 'B' that is already stated in the 'C' collection for that 'A', and more likely, 'C' references a 'B' that is already stated implicitely by the related 'A'..
Hope that makes sense, and again cheers in advance.
Edit: the tables are as follows:
'A' is a submission, a submission can have many 'contributors' (members), but always at least one.
'B' is a member
'C' is a table that links a 'A's to many 'B's
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将您的抽象模式转换为具体模式，我相信看起来像这样：
这可以为您提供“至少一个“每次提交的贡献者，但这意味着需要执行一些奇怪/困难的规则。 困难的存在是由于 PrimaryContributorID - SubmissionContributors 表中是否存在匹配的记录？ 如果 PrimaryContributor 发生变化，是否需要重新排列 SubmissionContributors 中的记录？ 如果 PrimaryContributor 没有匹配的 SubmissionContributor 记录，则每次列出提交的贡献者时，您都必须在 PrimaryContributor 中联合，等等。
不幸的是，SQL 没有“至少一对多”关系（无论如何，我不知道:)），所以你想出的任何解决方案都将涉及一些妥协。
对我来说，最好的方法是删除 PrimaryContributorID； 所有贡献者都存在于 SubmissionContributors 表中，并且您将拥有强制要求的“至少一个”部分的域逻辑（例如，如果没有至少一个贡献者，则插入/更新提交记录的方法将失败，并且方法如果没有至少一个贡献者，读取提交记录将会失败）。
Translating your abstract schema into a concrete schema I believe looks something like this:
This can work to give you the "at least one" contributor per submission, but it means having some odd/difficult rules to enforce. The difficulty exists because of the PrimaryContributorID -- Does a matching record exist in the SubmissionContributors table? If the PrimaryContributor changes, do you have to rearrange records in SubmissionContributors? If the PrimaryContributor doesn't have a matching SubmissionContributor record, every time you list contributors for a submission you have to union in the PrimaryContributor, etc.
Unfortunately SQL doesn't have an "at-least-one-to-many" relationship (not that I know of anyway :) ), so any solution you come up with is going to involve some compromise.
For me, the best approach would be to remove the PrimaryContributorID; All contributors exist in the SubmissionContributors table and you will have domain logic that enforces the "at-least-one" part of the requirements (eg, methods that insert/update submission records will fail if there is not at least one contributor, and methods that read submission records will fail if there is not at least one contributor).
我认为在任何情况下您都不会希望/需要在两个单独的区域中包含引用。
另外，您希望获得一对多关系的多方参考。 如果 A 有 1+ B 个，则 B 表应包含 A 引用，反之亦然。
如果C需要返回A拥有的B，只需将C设置为一个存储过程或一个连接A和B表的视图，这样数据就永远不会不同步。
I don't think that there is ever a case where you would want/need to contain a reference in two seperate areas.
Also, you want to have the reference on the many side of a one to many relationship. If there are 1+ B's for an A, then the B table should contain an A reference, not vice versa.
If C needs to return the B's that A has, just make C a stored procedure or a view that joins the A and B table, that way the data can never get out of sync.
也许我不理解并且过于简单化，但是如果B和A之间设置了主键/外键关系，并且表A中带有B的id的fk字段是必填字段，那么它不会让您向 A 添加一条在 B 中没有记录的记录。如果比这更复杂，您可能需要通过 A 上的触发器强制执行规则。
Maybe I'm not understanding and oversimplifying, but if there is a Primary key/foreign key relationships set up between B and A and the fk field with the id for B that is in table A is a required field, it won't let you add a record to A that doesn't have a record in B. If it is more complicated than that you may need to enforce the rules through a trigger on A.