sql - How to properly design statuses table -

this question more best approch desgin table contains statuses of proccess in system.

i have proccess can in status: init, waiting price approve, sent, accepted,finished

status can forward 1 step @ time or 1 step @ time, meaning init can't jump sent without passing throu waitig price approve.

i designed following table (postgresql doesn't matter question):

create table status (   id serial primary key,   name text not null,   backid integer,   forwardid integer,    constraint id_pkey primary key (id)   constraint backid_fkey foreign key (id)       references status(id) match simple on update no action on delete no action   constraint forward_fkey foreign key (id)       references status(id) match simple on update no action on delete no action ) 

which basicly means:

id name                         backid  forwardid 1  init                                    2 2  waiting price approve      1        3 3  sent                           2        4 4  accepted                       3        5 5  finished                       4 

note: there no gurenntee after 1 there 2. in future can delete 1 of lines , might that:

id name                         backid  forwardid 1  init                                    3 3  sent                           1        4 4  accepted                       3        5 5  finished                       4 

it's same behavior doubly linked list. goal in future if there new status insrted table , won't have changes anywhere else. when client ask forward/back done automaticly new "list" of statuses.

the thing way seems bit not ellegant. insertion of new status same algoritem insertion doubly linked list. seems work , doesn't seems proper work sql , database abilities.

can suggest better way that?

the forwardid not needed , think removing makes life easier (although could keep if think make queries easier).

i not define id column serial. want have control on ids during insert , update.

assuming table definition can query whole hierarchy including sort column using recursive common table expression:

with recursive status_tree (    select id, name, backid, 1 level    status    backid null    union     select c.id, c.name, c.backid, p.level + 1    status c      join status_tree p on p.id = c.backid ) select * status_tree order level; 

for sample data return:

id | name                      | backid | level ---+---------------------------+--------+------  1 | init                      |        |     1  2 | waiting ror price approve |      1 |     2  3 | sent                      |      2 |     3  4 | accepted                  |      3 |     4  5 | finished                  |      4 |     5 

inserting new status quite easy (and point manually defined id makes life bit easier:

-- create new status may set after 'waiting ror price approve' insert status (id, name, backid)  values (6, 'partially sent', 2);  -- make current 'sent' status descendant of new 1 update status   set backid = 6 name = 'sent'; 

the above query returns following:

id | name                      | backid | level ---+---------------------------+--------+------  1 | init                      |        |     1  2 | waiting ror price approve |      1 |     2  6 | partially sent            |      2 |     3  3 | sent                      |      6 |     4  4 | accepted                  |      3 |     5  5 | finished                  |      4 |     6 

the order of status values not reflect "order" of id column.

if want can create view returning information.

---

edit

if find next status rather looking @ of them, can flip logic , store next one:

create table status (   id integer primary key,   name text not null,   next_status integer references status );  insert status(id, name, next_status) values  (1,'init', 2), (2,'waiting ror price approve', 3), (3,'sent', 4), (4,'accepted', 5), (5,'finished', null); 

to retrieve next status requires single select statement (no recursion).

you can still status values in correct order using cte, need reverse join condition:

with recursive status_tree (    select id, name, next_status, 1 level    status    id = 1    union     select c.id, c.name, c.next_status, p.level + 1    status c      join status_tree p on p.next_status = c.id ) select * status_tree order level; 

inserting new nodes done same way backid (just need "re-link" "other" row)

insert status (id, name, next_status)  values (6, 'partially sent', 3);  update status   set next_status = 6 name = 'waiting ror price approve';