How to design two tables: A hasMany B, and one B per A has state ‘active’?

Posted on

Question :

Figure two tables: environments and branches.

Their relationship is: one environment has many branches. This is easy with an environment foreign key in the branches table.

The problem is adding this constraint: zero or one branch of the environment’s branches can have the “active” status.

The system will be supporting these two requests:

  1. Give me the branches of environment ‘E’
  2. Give me the active branch of the environment ‘E’

A solution would be to add “activeBranchFK” in the environment table, but this produces a cyclic dependence between the tables and it looks not a good solution to play with that.

Another solution would be a boolean “active” in the branches table, but it seems to me that we can have undesired states if many branches of the same environment are active at the same time.

Did you ever have to model this pattern?

Answer :

You can use a partial unique index on the branches table:

create table branches
(
  id integer primary key, 
  env_id integer not null references environments,
  active boolean not null,
  ... other columns ...
);

create unique index on branches (env_id) 
where active;

Now for each env_id, there can be many different rows with active = false, but only one with active = true.

Leave a Reply

Your email address will not be published. Required fields are marked *