助人为快乐之本!

  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理
  27 Posts :: 0 Stories :: 299 Comments :: 4 Trackbacks

公告

反模式:树型结构怎么设计?在表中加parentid

解决方案:

1.Sql Server 2005 可以用Common Table Expressions (CTE)

WITH CommentTree
    (comment_id, bug_id, parent_id, author, comment, depth)
AS (
    SELECT *0 AS depth FROM Comments
    WHERE parent_id IS NULL
  UNION ALL
    SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct
    JOIN Comments c ON (ct.comment_id = c.parent_id)
)
SELECT * FROM CommentTree WHERE bug_id = 1234;

2.建专门的表存祖孙关系,包括自身,即使跨级

CREATE TABLE TreePaths (
  ancestor    BIGINT UNSIGNED NOT NULL,
  descendant  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(ancestor, descendant),
  FOREIGN KEY (ancestor) REFERENCES Comments(comment_id),
  FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);

 

1 --2--3
  |
  --4--5
    |
    --6--7

 

a       d                  a       d                  a       d                 
------------------------------------------------------------------
1        1                 2        2                 5        5
1        2                 2        3                 6        6
1        3                 3        3                 6        7
1        4                 4        4                 7        7
1        5                 4        5
1        6                 4        6
1        7                 4        7

 

/*child for 4 */
SELECT *
FROM TreePaths
WHERE ancestor = 4 AND path_length = 1;

/*insert 8*/
INSERT INTO TreePaths (ancestor, descendant)
  SELECT t.ancestor, 8
  FROM TreePaths AS t
  WHERE t.descendant = 5
 UNION ALL
  SELECT 88;

/*move 6 under 3*/
-- START:delete
DELETE FROM TreePaths
WHERE descendant IN (SELECT descendant
             FROM TreePaths
             WHERE ancestor = 6)
  AND ancestor IN (SELECT ancestor
           FROM TreePaths
           WHERE descendant = 6
             AND ancestor != descendant);
-- END:delete
--
 START:reinsert
INSERT INTO TreePaths (ancestor, descendant)
  SELECT supertree.ancestor, subtree.descendant
  FROM TreePaths AS supertree
    CROSS JOIN TreePaths AS subtree
  WHERE supertree.descendant = 3
    AND subtree.ancestor = 6;
-- END:reinsert

  

反模式:不加Foreign Key, 因为修改数据时麻烦

解决方案:一定要添加约束, 保证数据一致性

 

反模式:一个表的数据可能给A表,也可能给B表用,怎么办?加个type区分

解决方案:

1.加个中间关联表,保证1对多关系

2.建上级表,A,B用同一套id标识

 

反模式:表太大,怎么办?克隆表结构,然后按年存到不同表

解决方案:

1.水平分割:表分区

2.垂直分割:把大数据列放到另外一个表,主键值和原表相同

  

反模式:如何限定列的取值范围?用check约束

解决方案:建lookup表

CREATE TABLE BugStatus (
  status  VARCHAR(20PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');

CREATE TABLE Bugs (
  -- other columns
  status  VARCHAR(20),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
    ON UPDATE CASCADE
);

 

反模式:使用一个复杂查询去解决要求

解决方案:分步解决

 

 

posted on 2012-04-11 15:11 feiyun0112 阅读(...) 评论(...) 编辑 收藏