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

database-designdependenciespostgresqltable

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:

Give me the branches of environment 'E'
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?

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

Related Solutions

MySQL Auto-Increment – How to Have Two Auto-Increment Columns in One Table

What you are proposing to do can only be done with MySQL cleanly under three(3) conditions

CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM

Here is your original table layout

CREATE TABLE `invoices` ( 
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  `invoicenumber` mediumint unsigned NOT NULL, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL 
) COMMENT='' ENGINE='InnoDB';

Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';

Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM'; 
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;

Here it is executed:

mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
    ->   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    ->   `branch` enum('A','B') NOT NULL,
    ->   `date` date NOT NULL,
    ->   `client` varchar(100) NOT NULL,
    ->   PRIMARY KEY (branch,invoicenumber)
    -> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoices (branch,date,client) VALUES
    -> ('A',DATE(NOW()),'John'),
    -> ('B',DATE(NOW()),'Jack'),
    -> ('A',DATE(NOW()),'Jeff'),
    -> ('B',DATE(NOW()),'Joel'),
    -> ('A',DATE(NOW()),'Jane'),
    -> ('B',DATE(NOW()),'Joan'),
    -> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!

Design Pattern – One of Many Parent Tables

I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.

You are stuck to just two solutions because of the strict 3NF compliance.

I would design a lesser coupling schema:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  -- See (1)
  -- AlertID     int identity(1,1) not null,

  AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
  ForeignKey int not null,
  CreateUTC  datetime2(7) not null,

  -- See (2)
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)

-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys

Or, if integrity relationship shall be mandatory, I might design:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  AlertID     int identity(1,1) not null,
  AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
  CreateUTC  datetime2(7) not null,
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

CREATE TABLE AlertProduct  (AlertID..., ProductID...,  CONSTRAINT FK_AlertProduct_X_Product(ProductID)    REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews     (AlertID..., NewsID...,     CONSTRAINT FK_AlertNews_X_News(NewsID)             REFERENCES News)

Anyway...

Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...

These presented, what's the moral?

They differ subtly, and weight the same on the criterias:

performance on insertions and updatings
complexity on querying
storage space

So, choose that comfier to you.

Best Answer

Related Solutions

MySQL Auto-Increment – How to Have Two Auto-Increment Columns in One Table

Design Pattern – One of Many Parent Tables

Related Question