如何使用一个主要对象对嵌套集合进行建模
我希望在 RDBMS 中对一场会议进行建模，该会议有多个提议的时间，但将选择一个作为已接受或主要的时间。类似于：
create table Meeting ( meetingId int );
create table ProposedTime( meetingId int, dateAndTime datetime );
但是，将会有一个界面，用户可以在其中选择建议的时间之一，我需要保存该选择。
我可以想到两个选项，每个选项都有一个缺点：
将选定的提案保留在会议桌上：
创建表 Meeting(meetingId int, selectedProposalId int ); 创建表 ProposeTime(proposalId int PK, meetId int, dateAndTime datetime );使用此方法，selectedProposalId 可能适用于属于不同会议的提案。
在所选提案上存储标记：
创建表 Meeting(meetingId int); 创建表 ProposeTime( MeetingId int, dateAndTime datetime, isSelected bool );使用此方法，可以将两个提案标记为选择。
我知道有一些“黑客”可以确保完整性（对于2，在MS SQL中，您可以有一个过滤的唯一索引以确保只选择一个），但我不想提交特定于供应商的代码。
我也同意只在应用程序层强制执行正确性，但这仍然使两个选项保持开放。
你们有什么推荐？如果有人有任何想法，我也愿意接受其他选择;)
注意：我正在使用 Rails 3，因此如果有一种首选方法可以使用 ActiveRecord 处理此问题，我很想听听。
I'm looking to model in an RDBMS a meeting which has several proposed times, but one will be chosen as the accepted or primary one. Something like:
create table Meeting ( meetingId int );
create table ProposedTime( meetingId int, dateAndTime datetime );
However, there will be an interface where the user can pick one of the proposed times, and I need to save that choice.
I can think of two options, each with a downside:
Hold selected proposal in meeting table:
create table Meeting( meetingId int, selectedProposalId int ); create table ProposedTime( proposalId int PK, meetingId int, dateAndTime datetime );With this approach, it is possible that the selectedProposalId is for a proposal that belongs to a different meeting.
Store a flag on the selected proposal:
create table Meeting(meetingId int); create table ProposedTime( meetingId int, dateAndTime datetime, isSelected bool );With this approach, it's possible that two proposals could be marked selected.
I know there are 'hacks' to ensure integrity (for 2, in MS SQL you could have a filtered unique index to make sure that only one is selected), but I'd rather not commit to vendor specific code.
I'm also fine with only enforcing the correctness at the application layer, but that still leaves both options open.
What do you guys recommend? I'm also open to other options if anyone has any ideas ;)
Note: I'm using Rails 3, so if there's a preferred way to handle this with ActiveRecord, I'd like to hear it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
很好的问题。我喜欢你正在评估潜在的不一致之处。在这种情况下，我认为第一个解决方案是正确的，只需进行一个小修改：ProposeTime 的主键应该是包含 meetId 的复合主键。
这样做有几个原因：首先，如果没有会议，提议的时间显然不可能存在，更重要的是，如果没有特定的会议，它就不可能存在。本质上，除非在会议的上下文中，否则无法定义 suggestTime，因此它的定义不应允许它独立存在。
其次，这种依赖关系并没有通过meetingId作为强制属性来充分表达。例如，更新proposalTime 的meetingId 字段是没有意义的。 “让我们早上 9 点去参加另一个会议吧！”。将 meetId 作为主键的一部分明确阻止这种无意义的更新，因为更新的主键本质上是不同的事情。
最后，proposalTime 有一个逻辑上不完整的键，这是您在尝试正确使用它时遇到的第一件事。理想情况下，每个数据库关系都应该声明一致。也就是说，如果你能表达“狮子有两个父母”的关系，那么其中一个父母就不可能是松鼠。显然，性能和平台限制意味着您无法总是达到该目标，但这种不一致可以被视为“代码味道”，可能需要进一步调查。就您而言，修复似乎很小且性能良好。
tl;dr：将meetingId 添加到proposalTime 的PK 中，并采用您的第一个解决方案。
Great question. I love the fact that you're evaluating the potential inconsistencies. In this case, I would argue that the first solution is the correct one, with one small modification: ProposedTime's primary key should be a composite primary key that includes meetingId.
There are a couple reasons for this: First of all, a proposedTime can clearly not exist without a meeting, and more importantly it cannot exist without a specific meeting. Essentially, a proposedTime cannot be defined except in the context of a meeting, and as such it's definition should not allow it to stand alone.
Secondly, this dependency is not sufficiently expressed with meetingId as a mandatory attribute. For example, it wouldn't make sense to update the meetingId field of a proposedTime. "let's move 9am to another meeting!". Making meetingId part of the primary key explictly discourages this sort of nonsensical update, because an updated primary key is essentially a different thing.
Finally, the fact that proposedTime has a logically incomplete key is one of the first things you ran into when trying to use it appropriately. Every database relation, ideally, should be declaratively consistent. That is, if you can express the relation "lions have two parents", it should be impossible for one parent to be a squirrel. Obviously, performance and platform limitations mean you can't always hit that goal, but such an inconsistency can be regarded as a "code smell" which may warrant further investigation. In your case, the fix seems small and performant.
tl;dr: add meetingId to the PK of proposedTime, and go with your first solution.