学步园

反模式:树型结构怎么设计？在表中加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 8, 8;
/*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(20) PRIMARY 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
);

 

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

解决方案：分步解决