title: How to properly design statuses table
body: <p>This question is more about what is the best approch to desgin a table that contains statuses of proccess in a system.</p>
  
  <p>I have a proccess that can be in status: <code>init, waiting for price approve, sent, accepted,finished</code></p>
  
  <p>Status can be forward one step at a time or back 1 step at a time, meaning from <code>init</code> you can't jump to <code>sent</code> without passing throu <code>waitig for price approve</code>.</p>
  
  <p>I designed the following table (<code>PostgreSQL</code> but it doesn't matter for the question):</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>Which basicly means:</p>
  
  <pre><code>id name                         backid  forwardid
  1  init                                    2
  2  waiting for price approve      1        3
  3  sent                           2        4
  4  accepted                       3        5
  5  finished                       4
  </code></pre>
  
  <p>Note: There is no gurenntee that after <code>1</code> there will be <code>2</code>. In the future I can delete one of the lines and it might look like that:</p>
  
  <pre><code>id name                         backid  forwardid
  1  init                                    3
  3  sent                           1        4
  4  accepted                       3        5
  5  finished                       4
  </code></pre>
  
  <p>It's the same behavior as <code>Doubly linked list</code>.
  The goal is that in the future if there will be a new status it will be insrted to the table and I won't have to do changes anywhere else. When the client ask to forward/back it will be done automaticly to the new "list" of statuses.</p>
  
  <p>The thing is that this way seems a bit not ellegant. The insertion of new status will be the same algoritem as insertion to <code>Doubly linked list</code>. It seems like too much work and it doesn't seems proper work with <code>SQL</code> and DataBase abilities.</p>
  
  <p>Can you suggest a better way to do that?</p>