A has many B's, and only one 'active' B

Question

Say, User has many Devices. That is, Device.user_id references User.id.

We need to add 'active' status to user-device relation. This means that a User can have no more than one 'active' Device.

-- addendum

It seems I have to clarify things.

• A User can actially have no Devices, one Device, or several Devices;
• Device can have an owning User, but must not;
• that 'ownership' is that reference Device.user_id references User.id;
• User.id and Device.id are the only PKs and must not change (so, no composite PKs);
• A User can have no more then one 'active' Device, but it can be NULL, but if not NULL, it must reference one of the Devices this User owns.

-- end addendum

I can imagine two approaches, both very straightforward:

1. add a active_device_id field to User, that is User.active_device_id references Device.id;
2. add a Boolean-type flag to Device entity.

The most common queries are:

• select an active Device for the given User;
• check if current Device is active for the owning User;

Rarely used, but very important are:

• make a given Device active for the owning User (and make other User's devices inactive);
• change the ownership of a given Device.

The 1st approach has two caveats:

• user1 has active device set to device1, but device1 is owned by user2;
• both user1 and user2 have the same active device (though it could be easily fixed by a unique constraint on the field User.active_device_id.

The 2d approach coul lead to several active Devices for one User.

What are other the disadvantages of both approaches?

...and what should I choose and why? :)

Answer 1

I would definitely go for the first approach -- the active_device_id.

It enables a guarantee of only one active device per user, and as you say allows an optional guarantee of uniqueness of active device id across all users.

All of the queries are very straightforward with this method.

Answer 2

You didn't mention your DBMS but most DBMS have the possibility to create partial indexes and this seems like a perfect use case for them.

I'm a bit confused how your device table looks like, but assuming the structure:

create table device
(
   device_id           integer not null,
   user_id             integer not null,
   is_active_for_user  boolean, 
   primary key (device_id, user_id)
);

You can ensure that each user has exactly one active device using the following index (Postgres syntax, other DBMS have other syntax to define a partial index):

create unique index idx_unique_active_device 
    on device (user_id)
    where is_active_for_user;