Modeling a 1:Many relationship with an attribute

Question

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.

The existing design (simplified) is:

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)
)

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 group_id into the Accounts table and that should work." But then what would I do with the is_primary attribute?

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

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

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

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)?

Thanks!

Answer 1

Relationship Cardinality

Judging by your description, you need 1:N relationship, which means you do not need the junction table Group_Accounts. Just a simple FK from Accounts to Groups should do.

Special Row

The next question is how you pick one row at the N side (Accounts) to be "special". You can either:

1. use the Accounts.is_primary flag and enforce its uniqueness (per group) through a filtered unique index (if your DBMS supports it),
2. or you could have a FK in Groups pointing to the primary account. In the latter case, though, you have to be careful to pick a primary account which actually belongs to the group.

The second approach can be modeled similar to this:

Groups.FK1 denotes:

FOREIGN KEY (group_id, primary_account_no) REFERENCES Accounts (group_id, account_no)

The presence of group_id in the FK above is what enforces primary account to belong to the group it is the primary account of.

Just be careful how you generate account_no when creating new accounts. You'll need to do something like this to avoid race conditions in concurrent environment (the actual code will varry by DBMS, of course).

---

Pick the first approach if your DBMS supports filtered indexes and there is no specific reason to pick the second approach.

Pick the second if:

• you DBMS doesn't support filtered indexes,
• or your DBMS supports deferred constraints and you need to enforce the presence of primary account at all times (just make primary_account_no NOT NULL),
• or you don't actually need account_id, so you can have potentially one index less (depending on how strictly your DBMS requires indexes on FKs, and your actual workload, you may be able to avoid index on primary_account_no, as opposed to index that must be present on is_primary).

Answer 2

It is definitely possible to get rid of Group_Accounts.

From your description, it seems each group has many accounts, but each account only has one group. So you would put the group_id into the Accounts table as you suggest, and then put primary_account_id as a field in Groups.

Answer 3

It is possible to change the m:n intersection table, Group_Accounts, to a 1:n table by changing the PK to just the account id instead of both account and group. However, you would still be stuck with the additional overhead of enforcing the constraint that one and only one account is primary for any group.

However, if you move the group FK to the account record, where it really should be for 1:n cardinality, you can create a Primary_Accounts table kinda like the Group_Accounts table except the PK would be the group id. So each group could have one only one entry and that would be the one primary account. It would look like this:

create table Groups (
    Id      int not null,
    Name    varchar( 50 ) not null,
    constraint PK_Groups primary key( Id )
);

create table Accounts (
    Id      int not null,
    Name    varchar( 50 ) not null,
    GroupID int not null,
    constraint PK_Accounts primary key( Id ),
    constraint FK_AccountGroup foreign key( GroupID )
        references Groups( ID )
);

create table PrimaryAccounts (
    GroupID      int not null,
    AccountID    int not null,
    constraint PK_PrimaryAccounts primary key( GroupId ),
    constraint FK_PrimaryGroup foreign key( GroupID )
        references Groups( ID ),
    constraint FK_PrimaryAccount foreign key( AccountID )
        references Accounts( ID )
);

Now you have the 1:n cardinality design properly and you have the ability to designate one and only one account per group as the primary account.

However, there is one flaw. The PrimaryAccounts table must refer to an existing group and an existing account, but there is nothing that enforces the implicit requirement that the account be associated with the group.

Fortunately, this is easily fixed. Just add a constraint to the Accounts table:

    constraint UQ_AccountGroup unique( GroupID, ID ),

Then, instead of creating two FKs in the PrimaryAccounts table, you need only one:

    constraint FK_PrimaryGroupAccount foreign key( GroupID, AccountID )
        references Accounts( GroupID, ID )

Now there can be only one primary account for each group and that account must be associated with the group.