title: KanBan Database architecture
body: <p>I'm trying to implement a kanban agile board like Trello in my application. I'm wondering how to do it the best way. I'm considering these entities:  </p>
  
  <p><strong>Board</strong><br>
  has many Lists</p>
  
  <p><strong>List</strong><br>
  has many Cards</p>
  
  <p><strong>Card</strong><br>
  contains some content</p>
  
  <p>However I'm stuck with ordering of Cards. Each Card should have a sort position to sort Cards in particular order in a List. When dragging a Card, I should change it's position and save it in the database. What is the most efficient way of doing this?</p>
  
  <p>Adding a <code>position</code> field to each Card seems redundant since I would have to recalculate positions of all Cards in a List (or two) after I drag a Card to a different position (there will be hundreds of Cards). I was thinking of storing an array of all Card ids in a List and sort Cards by this array. What are pros/cons of this solution? And are there any better solutions?</p>
  
  <p>I'm using Ruby on Rails and PostgreSQL.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Using @cske answer I came up with the following solution:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION move_buyer_card(
      new_list_id   INT
    , param_id      INT
    , new_position  INT
  ) RETURNS FLOAT4
  LANGUAGE plpgsql SECURITY DEFINER
  AS $$
  DECLARE
    var_lower_bound FLOAT4;
    var_upper_bound FLOAT4;
    var_new_weight  FLOAT4; /*between 0 and 1*/
  BEGIN
    IF new_position &lt; 2 THEN /*first position*/
      var_lower_bound := 0;
      SELECT MIN(weight) FROM Buyers
        WHERE board_list_id = new_list_id
        INTO var_upper_bound;
      IF var_upper_bound IS NULL THEN /*empty list*/
        var_upper_bound := 1;
      END IF;
    ELSE /*not first position*/
      WITH ordered_cards AS (
        SELECT id, RANK() OVER (ORDER BY weight ASC) AS rank, weight
        FROM Buyers WHERE board_list_id = new_list_id
      )
      SELECT cards0.weight, cards1.weight from ordered_cards cards0
        JOIN ordered_cards cards1
          ON cards0.rank = cards1.rank - 1
        WHERE cards1.rank = new_position
        INTO var_lower_bound, var_upper_bound;
      IF NOT FOUND THEN /*only 1 item in list OR last position*/
        SELECT MAX(weight) FROM Buyers WHERE board_list_id = new_list_id
          INTO var_lower_bound;
        IF var_lower_bound IS NULL THEN /*empty list*/
          var_lower_bound := 0;
        END IF;
        var_upper_bound := 1;
      END IF;
    END IF;
    var_new_weight := var_lower_bound + (var_upper_bound - var_lower_bound) / 2;
    UPDATE Buyers
      SET weight = var_new_weight,
        board_list_id = new_list_id
      WHERE id = param_id;
    RETURN var_new_weight;
  END;
  $$;
  </code></pre>