Modeling a 1:Many relationship with an attribute
<p>I've come across a table design that immediately struck me as odd, but now that I've thought through it I can't seem to come up with a design that I'm really happy about.</p>

<p>The existing design (simplified) is:</p>

<pre><code>CREATE TABLE Accounts (
    account_id      INT NOT NULL,
    account_name    VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Accounts PRIMARY KEY CLUSTERED (account_id)
)

CREATE TABLE Groups (
    group_id      INT NOT NULL,
    group_name    VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (group_id)
)

CREATE TABLE Group_Accounts (
    group_id      INT NOT NULL,
    account_id    INT NOT NULL,
    is_primary    BIT NOT NULL,
    CONSTRAINT PK_Group_Accounts PRIMARY KEY CLUSTERED (group_id, account_id)
)
</code></pre>

<p>While it looks like a standard many:many relationship, an account never actually belongs to more than one group. I immediately thought, "Ok, we could put the <code>group_id</code> into the <code>Accounts</code> table and that should work." But then what would I do with the <code>is_primary</code> attribute?</p>

<p>I could put an <code>account_id</code> into the <code>Groups</code> table as <code>primary_account_id</code> and then I believe that I could enforce RI with a foreign key on the <code>primary_account_id, group_id to account_id, group_id</code>.</p>

<p>Alternatively, I could move the "is_primary" flag into the <code>Accounts</code> table. Maybe that's the best solution?</p>

<p>Any thoughts on pros/cons for each approach? Am I missing any potential issues? Is there some other alternative that I've missed?</p>

<p>Is there any way to enforce a single primary account within a group in any of these situations outside of triggers (so primarily declarative RI)?</p>

<p>Thanks!</p>