On Creating Draft Entities

Published on 1/15/2014

---

Requirements

• Constraints not in effect when entity is in DRAFT state
• Constrains are in effect when entity is in other states
• Entity has a stable primary key, so that can share entity with others (you’ll note that Gmail drafts get different IDs on every save)
• Allow for “child” entities to link to the draft entity and not move around

Example:

create table email_messages (
  id serial primary key,
  subject varchar(78) null, --let me be null in drafts, but not otherwise
  status text not null check (status in ('DRAFT', 'OUTBOX', 'DELETED', 'SENT')),

  check (case when status='DRAFT' then true else ( subject is not null ) end )
);

insert into email_messages (subject, status) values (null, 'DRAFT') returning id; --returns 1

insert into email_messages (subject, status) values (null, 'OUTBOX') returning id; --error

Another way to do it:

create table email_messages (
  id serial primary key,
  subject varchar(78) null check ( is_draft or subject is not null ),
  is_draft boolean not null
);

insert into email_messages (subject, is_draft) values (null, true); --returns 1

insert into email_messages (subject, is_draft) values (null, false); --error

... views