看板数据库架构

Question

看板数据库架构

ruby-on-railspostgresqlarchitectureapplication-designkanban

5

我正在尝试在我的应用程序中实现类似于Trello的看板敏捷板。我正在思考最佳方法。我考虑以下实体：

Board 有很多列表

List 有很多卡片

Card 包含一些内容

然而，我遇到了一些关于卡片排序的问题。每个卡片都应该有一个排序位置，以按特定顺序对卡片进行排序。当拖动卡片时，我应该更改其位置并将其保存在数据库中。哪种方法最有效？

为每张卡牌添加一个“position”字段似乎是多余的，因为在我将卡牌拖到不同位置之后，我必须重新计算列表（或两个列表）中所有卡片的位置（将会有数百张卡片）。我考虑将所有卡片ID的数组存储在列表中，并按此数组排序卡片。这种解决方案的优缺点是什么？还有更好的解决方案吗？

我使用Ruby on Rails和PostgreSQL。

更新：

使用@cske的答案，我想到了以下解决方案：

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 < 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;
$$;

- leemour

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cske · Accepted Answer

考虑这个问题，技巧不在于存储位置，而在于权重，这样你就可以在任意两个元素之间插入。

create table listOfCards (
   listId INTEGER
  ,cardId INTEGER
  ,weigth FLOAT4
  ,PRIMARY KEY (listId,cardId)
);

CREATE OR REPLACE FUNCTION addCard(
    plistId INT
  , pcardId INT
  , ppos    INT
) RETURNS FLOAT4
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  vlb FLOAT4;
  vub FLOAT4;
  vnw FLOAT4;
BEGIN
  IF 2 > ppos THEN
    vlb := 0;
    SELECT min(weigth) FROM listOfCards WHERE listId = plistId INTO vub;
    IF vub IS NULL THEN /*empty list*/
      vub := 2;
    END IF; 
  ELSE
    with corder as (select cardId,RANK() OVER (order by weigth asc) as r,weigth FROM listOfCards WHERE listId=1)
    select c0.weigth,c1.weigth from corder c0 JOIN corder c1 ON c1.r = c0.r + 1 where c1.r = ppos INTO vlb,vub;
    IF NOT FOUND THEN
      SELECT max(weigth) FROM listOfCards WHERE listId = plistId INTO vlb;
      IF vlb IS NULL THEN /*empty list*/
        vlb := 0;
      END IF;
      vub := (vlb+1) * 2;
    END IF;  
  END IF;
  vnw := vlb + (vub-vlb) /2;
  INSERT INTO listOfCards(listId, cardId, weigth) 
       VALUES (plistId,pcardId,vnw )
  ON CONFLICT ON CONSTRAINT listofcards_pkey DO UPDATE SET weigth = vnw ;
  RETURN vnw;
END;
$$;

使用方法：

select addCard(1,1,1);
select addCard(1,2,1);
select addCard(1,3,2);
select addCard(1,4,2);
select addCard(1,5,5);
select addCard(1,5,2);

select * from listOfCards ORDER BY weigth;

Result: 1,2,0.5 1,5,0.5625 1,4,0.625 1,3,0.75 1,1,1