我是FE，也是Fe

前端来源于不断的点滴积累。我一直在努力。

统计

留言簿(15)

阅读排行榜

评论排行榜

一种能跨数据库的树形数据表格设计

在数据表的设计中，对于树形的表格数据，处理起来时比较棘手的，个人经历过sql server,oracle,mysql，发现对于树形的表格数据处理都不同。

通常的属性表格设计时我们会这样设计:
create table demo_tree (
       id int ,
       nodename  varchar(50),
       parentid int 
)
这样对于不同的数据库处理起来方法就各异了。在sql server里面可能需要用函数递归，在oracle里面可以使用 connect by  .. start with .. order sibling by ..去达到深度遍历树的目的。

虽然主流的ORM框架能处理数据库厂商sql语法的差异，但是对于树形数据还是缺乏统一的支持。所以有人设计了一个这样的表结构:
1 create table DEMO_TREE
2 (  
3   NODELEVEL  INTEGER not null,
4   LEVELCODE  VARCHAR2(500) not null,
5   PARENTNODE VARCHAR2(500),
6   NODENAME   VARCHAR2(200)
7   /*其他字段略*/
8 )
  • nodelevel是树的深度，1,2,3...
  • levelcode这个字段格式是这样的，我们可以假定每级节点的数量是有上限的，可以根据需要约定比如我们限定每个节点的最多是99999个子节点。这样。levelcode 的第一个节点可以levelcode编号为"00001",其相邻节点为"00002"，他的第一个子节点为"0000100001"，以此类推，可以为每个节点一个唯一编号。
  • parentnode就是父节点的levelcode
  • nodename是节点名称

这样的设计后。我们给出一个实例查询：

select t.nodelevel,t.nodename,t.levelcode,t.parentnode From demo_tree t order by t.levelcode

这样查询的结果形如：
NODELEVEL NODENAME LEVELCODE PARENTNODE
1 一级测试节点1  00001
2 二级测试节点1  0000100001 00001
3 sdfasfasfad  000010000100001 0000100001
2 二级测试节点2  0000100002 00001
2 二级测试节点3  0000100003 00001
2 二级测试节点5  0000100005 00001
2 asdfgh  0000100007 00001
1 一级测试节点2  00002
2 二级测试节点2  0000200001 00002
3 fasdfasfsaf  000020000100001 0000200001
2 二级测试gg4  0000200002 00002
3 dfasfasfas  000020000200001 0000200002
3 fgh  000020000200001 0000200002
4 fdsafdas  00002000020000100001 000020000200001
4 dfasfsafsda  00002000020000100001 000020000200001
5 fadsfasfsa  0000200002000010000100001 00002000020000100001
5 fdasfdasfasdf  0000200002000010000100001 00002000020000100001
3 dsafasfasdf  000020000300001 0000200003
1 测试深度节点1  10001
2 测试深度节点10  1000100000 10001
3 测试深度节点100  100010000000000 1000100000
4 测试深度节点1000  10001000000000000000 100010000000000
5 测试深度节点10000  1000100000000000000000000 10001000000000000000
1 测试深度节点2  10002
2 测试深度节点20  1000200000 10002
3 测试深度节点200  100020000000000 1000200000
4 测试深度节点2000  10002000000000000000 100020000000000
5 测试深度节点20000  1000200000000000000000000 10002000000000000000
6 qwerfga  100020000000000000000000000001 1000200000000000000000000
2 sdfg  1000200001 10002
3 safsdfsadfaaa  100020000100001 1000200001

他是树的深度遍历结果,这也就是这样设计的最大的好处。

对于新增树节点时需要多做一步就是计算levelcode,比如增加同级节点时需要找到同级节点的最后一个节点。然后将levelcode最后一节+1。对于新增子节点需要找到最大levelcode的子节点然后+1。

删除也比较方便，如需要删除一个节点以及其所有的子节点，可以
delete from demo_tree where levelcode like '00001%'

需要获取树的广度遍历结果可以直接用nodelevel排序。

这样的设计带来的好处是在各种数据库上都可以用。不会因为数据库不同获取树的遍历结果需要写不同的sql。

当然，问题在于levelcode的计算会导致同级节点的排序不好实现。要获取遍历结果，通常是按照levelcode排序，由于计算levelcode是根据新增的先后顺序，所以同级排序就留给大家思考了。


posted on 2010-10-25 11:20 衡锋 阅读(2521) 评论(4)  编辑  收藏 所属分类: Oracle

评论

# re: 一种能跨数据库的树形数据表格设计 2010-10-25 12:35 vagrant

with查询应该都可以搞定了吧  回复  更多评论   

# re: 一种能跨数据库的树形数据表格设计[未登录] 2010-10-25 15:03 小小

这个确实可以实现，我们试过，但大数据量下效率实在远不如oracle connect by，小数据量无所谓；另外这种实现对于树结点的移动操作实在是噩梦啊。
五位一个编码小数据量下浪费，大数据量下还有10万的约束，虽不大可能超出，但这个约束还是不爽，类似于“潜规则”啊。
所以我们最终更改了曾经的这种实现，还是充分使用不同的数据库提供的解决方法更优化，现在的java/c#实现多数据库对应不同实现代码还是很简单的。
  回复  更多评论   

# re: 一种能跨数据库的树形数据表格设计 2010-10-25 17:28 阳衡锋

@vagrant
with语法不是每个数据库都支持的。

@未登录
这样的设计主要还是考虑在不同的数据库都能够简单的实现遍历和级联删除。当然任何设计都是有利有弊的。如果产品要求数据库兼容，必然会有些取舍。  回复  更多评论   

# re: 一种能跨数据库的树形数据表格设计 2010-10-27 15:46 单飞

一般来说tree model 不会全部构造出来，除非数据量很少。针对海量的深度，要使用动态加载，比如树的节点被点击开的时候才取子目录的数据。Lazy-Load.  回复  更多评论   


只有注册用户登录后才能发表评论。
网站导航: