组织层次结构在关系型数据库中的实现方法.doc

组织层次结构在关系型数据库中的实现方法作者：湖南粮食技工学校信息机电教研室张响摘要在B/S和C/S结构应用系统的数据库模型设计过程中，经常遇到分级分层的组织结构数据库设计的问题，它们以树状结构存在，上级有若干个下级、下级又分若干个下级，这样一级一级的出现层次的问题，如：公司的商品与类别关系，商品与品牌关系，人事部门的组织架构用户与部门关系等，这些关系中：商品的类别商品、品牌商品、部门用户之间存在实体间的一对多关系，类别、品牌、部门内部之间又存在隶属(层次)关系，怎样在数据库设计中设计出这种关系，同时有效快速检索不同层次查询信息？是数据库设计者要考虑的问题。这些关系在关系型数据库中表示时，有它特别的方法，有些人把它们分别建立在不同的表中，建立相应的关系，这虽然是一种解决办法，但如果在应用中，它给软件带诸多不便，特别是程序健壮性方法，如果此时需要多加一个职位，这种解决办法就需要多加一张表，那此时对应用程序的改动将可想而知。关键字1. 关系型数据库：用二维表结构来表示实体以及实体之间联系的模型。2. 递归：某一事物直接地或间接地由自己组成。3. 递归调用：一个函数直接或间接地调用自身，便构成了函数的递归调用。前者称直接递归调用，后者称间接递归调用。4. 检索:根据给定的任何条件,快速的查询出各条件相关的数据库查询信息。正文 实现方法一：层次模型在关系数据库本身是个问题，根本问题在于关系模型的第一范式：要求属性分为不可再分，对于组织结构这种关系，方法是：在设计中做一个指向本身主键的属性，也就是这个属性的外键就是本表的主键，它们之间建立自连接，可以实际层次查询的需求。 如：假设一个雇员关系： 雇员（雇员号，雇员姓名，经理）基中雇员号和经理两个属性出自同一个值域，同一元组的这两个属性值是“上、下级”关系。雇员号为主键，在目录层次结构中，雇员号为子结点，经理为父结点。 1：N 雇员号雇员姓名经理雇员号雇员姓名经理E3P1E4P2E3E6P3E3E8P4E6 图 雇员关系例： 根据雇员关系列出上一级经理及其所领导的职员清单。 SELECT S.雇员姓名，领导”，E.雇员姓名 FROM 雇员 S,雇员 E WHERE;S.雇员号=E.经理 说明：定义经理关系 S 和 雇员关系 E，结果在关系S和E上的联接实现了检索的要求。在实际应用中我们往往需要检索它的相应的不同层（子结点或者父结点），同时子结点与相应表之间存在联系，这时在数据结构中采用递归的方法，根据不同结点的层次位置，递归出相应结点的内容，虽然这种结构的实现需要计算机付出时间、空间的代价，但能实现实际不同结点信息的检索需要，也是层次结构在关系数据库中的一种常用的解决方法。例如：定义类别表：CREATE TABLE dbo.BASDEPT (ID 类别ID int NOT NULL, DPTNO 类别编号 varchar(20) NOT NULL, DPTNAME 类别名称 varchar(60) NOT NULL, PID 父类别ID int NOT NULL, )其中类别ID与父类别ID 存在父与子之间的层次关系。父类别为0,表示根结点。类别层次结构如下：层次类别第一层第二层第三层第四层类别名称(类别编号)食品(01)休闲类(24)米果(241)海苔(242)果冻类(243)果仁(244)坚果仁(24401)瓜仁类(24402)豆类、花生(24403)其他类(24404)休闲小食(245)粮油(21)南北干货(22)烹调食品(23)类别表如下:类别id类别编号类别名称父类别id12271食品0110024休闲食品12271196244果仁1100139524401坚果仁1196定义商品表 CREATE TABLE dbo.BASPLU (PLUID 商品ID int NOT NULL, DPTID 类别ID int NOT NULL, PLUNO 商品编号 varchar(20) NOT NULL, PLUNAME 商品名称 varchar(60) NOT NULL商品表如下:商品id类别id商品编号pluno商品名称pluname1833641395301360开心果 商品表与类别表通过dptid(类别id)关联，dptid(类别id)为商品表外键。类别表的最低层编号为商品表的类别编号怎样实际不同结点的表之间的检索?提高数据库查询的效率,在数据库设计中，可以采用函数方法实际不同层次的结点检索（向上(父结点)、向下（子结点）两个方向等）（A）、判断某个节点A(ID)是否是节点B(PID)的子节点。函数名：DptSonNode（ID integer, PID integer）结果：返回1 ：表示节点A（ID）是节点B(PID)的子节点 返回0：表示节点A（ID）不是节点B(PID)的子节点说明：递归查询A结点是不是属于它们的高n级类别，而非高一级类别函数定义：Create function DptSonNode(ID integer, PID integer)returns bit asBegin-如果父结点为0,返回1if PID = 0 return 1-如果子结点为0，返回0 if ID = 0return 0-如果子结点是父结点高一级的类别，返回1if exists(select 1 from BASDEPT where ID = ID and PID = PID) return 1-如果子结点不是父结点高一级的类别，而是高N级的类别，递归调用。 if (PID 0) and exists(select 1 from BASDEPT where ID = ID and PID = 0) return 0 select ID = PID from BASDEPT where ID = IDreturn dbo.DptSonNode(ID, PID)end(B)、判断类别结点A（ID）的层次数函数名：GetDPTLevel函数结果：-如果结点的父结点为0,返回1。-如果结点的父结点不为0,递归调用函数本身。函数定义：Create function GetDPTLevel(ID integer)returns integer asbegin declare PID integer select PID = PID from BASDEPT where ID = ID if (PID = 0) return 1return dbo.GetDPTLevel(PID) + 1end(C)、查找结点的第N级子结点表函数名：SubTreeDpt(ID integer, SELF bit)结果：返回结点A（ID）的所有子结点表说明：SELF为0时,函数返回的子表不返回自己本身。CREATE function SubTreeDpt(ID integer, SELF bit)returns SUBTREE table (ID integer)asbegin declare SUBID integer if (ID = 0) begin insert into SUBTREE(ID) select ID from BASDEPT return end if (SELF = 1 or NESTLEVEL = 2)and exists(select 1 from BASDEPT where ID = ID) insert into SUBTREE(ID)values(ID)-NESTLEVEL 返回对本地服务器上执行的当前存储过程的嵌套级别（初始值为 0）select SUBID = 0while isnull(SUBID, -1) -1 begin select SUBID = min(ID) from BASDEPT where PID = ID and ID SUBID if isnull(SUBID , 0) 0 begin insert into SUBTREE(ID) select ID from dbo.SubTreeDpt(SUBID, SELF) end end returnend(D)、查找结点（id）是第N（pLevel）级父结点函数：FindParentDpt(id integer, pLevel integer)结果：-返回本节点的第N级父亲节点，如果节点本身的级别比N高，则返回自己。-查找成功则返回其第N级父辈，如果自己的级别比N级还高，返回自己，如果父辈节点不存在或则返回0函数定义：CREATE Function FindParentDpt(id integer, pLevel integer) returns integer Asbegin declare level integer, pid integer select level = dbo.GetDPTLevel(ID), pid = pid from BASDEPT where ID = ID-如果这个ID不存在于数据库中，则返回-1。 if rowcount = 0 or pLevel = 0 return -1 if level = pLevel return id return dbo.FindParentDpt(pid, pLevel)end通过以上函数可以实现类别关联表和类别表之间的各种检索需求(类别向上汇总、向下汇总目、录树各类别层次汇总等)，利用sql查询语句调用函数。从而产生层次类别之间的比较数据，达到数据高级分析，利用数据分析、为科学的决策提供依据。（以上函数在sqlserver2000实现中通过）结论：通过以上实例，我们可以实现任何层次的目录结构数据在关系数据模式中的设计，尤其是组织层次比较庞大的结构，为软件的界面设计奠定好的基础（如:TreeView控件实现界面），解决了数据库层次关系检索的需求。实现方法（二）： 对一些层次结构不复杂、各层层次关系固定不变或者是层次结构有明显层次特征结构，我们往往通过编号各位分离划分的方法，来实现层次结构的数据库表的存储（如：身份证号码）如： 第一层 第二层 第三层 第四层 序号N位N位N位N位1N 通过以上各层次位数很容易区分各层次之间的关系，从而检索出各