How to model nested collection with one primary object

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:

1. 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.

2. 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.

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.