如何设计产品分类的表结构才是合理
如何设计存货分类的表结构才是合理：
类似上下级关系的数据结构例如：产品分类，客户分类等结构都会采用两种方式：
产品分类表：
1、父子树形结构 大部分设置为 id ,name ,parentid
2、编码级次分类 01，0101，0101001，
============
另外一张产品档案表通过与产品分类表的叶节点的ID关联得到
============
两种方法各有利弊。
父子节点的好处是：利用递归等方法可以很便捷的构造Tree
但是缺点是通过分类导航产品的时候，先要把分类下级叶节点的编码都得到
然后通过SELECT * FROM IN('','')这样的形式（可能有更好的形式我不知道）
这样的方式 效率比较低下点。而且 IN 里面带的内容巨大。
==================================
利用编码分类的方式可以通过方便的 SELECT * FROM xx like '%' 的形式 一次性得到
所属 叶节点分类的产品档案
但是编码分类的方式 比较嗦，而且编码长度需要预先定义，定义好了扩展起来也是麻烦
我看过国内有些软件设计的时候把两中方式都总和了
父子结构的同时内部维护一个 编码结构的级次，给了足够的位长。
未知各位同行先进 是采用的什么方法？？？请不吝赐教
[解决办法]
父子树形结构 大部分设置为 id ,name ,parentid 这个好。。。结构比较清晰
而且现在树形递归查询算法比较 多 容易查询
树形显示
- SQL code
--测试数据DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))INSERT @t SELECT '001',NULL ,'山东省'UNION ALL SELECT '002','001','烟台市'UNION ALL SELECT '004','002','招远市'UNION ALL SELECT '003','001','青岛市'UNION ALL SELECT '005',NULL ,'四会市'UNION ALL SELECT '006','005','清远市'UNION ALL SELECT '007','006','小分市'--深度排序显示处理--生成每个节点的编码累计(相同当单编号法的编码)DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))DECLARE @Level intSET @Level=0INSERT @t_Level SELECT ID,@Level,IDFROM @tWHERE PID IS NULLWHILE @@ROWCOUNT>0BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID FROM @t a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1END--显示结果SELECT SPACE(b.Level*2)+'|--'+a.NameFROM @t a,@t_Level bWHERE a.ID=b.IDORDER BY b.Sort/*--结果|--山东省 |--烟台市 |--招远市 |--青岛市|--四会市 |--清远市 |--小分市--*/--2005的方法declare @T table (ID int,pid int,NAME varchar(6))insert into @Tselect 1,0,'上衣' union allselect 2,0,'鞋子' union allselect 3,0,'裤子' union allselect 4,1,'毛衣' union allselect 5,1,'衬衫' union allselect 6,2,'球鞋' union allselect 7,2,'皮鞋' union allselect 8,3,'西裤' union allselect 9,3,'筒裤' union allselect 10,4,'羊毛衣' union allselect 11,4,'牛毛衣' union allselect 12,5,'白衬衫' union allselect 13,5,'黑衬衫';withdepts as( select * from @T where ID = 1 union all select a.* from @T a, depts b where a.pid = b.ID)select * from depts/*ID pid NAME----------- ----------- ------1 0 上衣4 1 毛衣5 1 衬衫12 5 白衬衫13 5 黑衬衫10 4 羊毛衣11 4 牛毛衣*/------------------------------------------------------CREATE TABLE [dbo].[levelTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [parentID] [int] NOT NULL CONSTRAINT [DF_levelTable_parentID] DEFAULT ((0)), [name] [nvarchar](50) NULL)insert into levelTable(parentID,[NAME]) values(0,'开发部') insert into levelTable(parentID,[NAME]) values(0,'客户服务部') insert into levelTable(parentID,[NAME]) values(0,'行政部') insert into levelTable(parentID,[NAME]) values(1,'开发一部') insert into levelTable(parentID,[NAME]) values(1,'开发二部') insert into levelTable(parentID,[NAME]) values(2,'后勤服务部') insert into levelTable(parentID,[NAME]) values(2,'大厅服务部') insert into levelTable(parentID,[NAME]) values(3,'总裁部') insert into levelTable(parentID,[NAME]) values(3,'人力资源部') insert into levelTable(parentID,[NAME]) values(9,'员工管理部') insert into levelTable(parentID,[NAME]) values(9,'人员招聘部') insert into levelTable(parentID,[NAME]) values(5,'开发一部项目A部') goDECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(100))DECLARE @Level intSET @Level=0INSERT @t_Level SELECT id,@Level,right('1000'+ltrim(ID),3)FROM levelTableWHERE parentID =0 -------modifyWHILE @@ROWCOUNT>0BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level,ltrim(b.Sort)+right('1000'+ltrim(a.ID),3) FROM levelTable a,@t_Level b WHERE a.parentID=b.ID AND b.Level=@Level-1END--显示结果SELECT a.*FROM levelTable a,@t_Level bWHERE a.ID=b.IDORDER BY b.Sort/*ID parentID name----------- ----------- --------------------------------------------------1 0 开发部4 1 开发一部5 1 开发二部12 5 开发一部项目A部2 0 客户服务部6 2 后勤服务部7 2 大厅服务部3 0 行政部8 3 总裁部9 3 人力资源部10 9 员工管理部11 9 人员招聘部*/
[解决办法]
父子树形结构 还是比较好的 是现在用得比较多的一种方法
BOM结构用得多
可以参考下面的一些代码:
- SQL code
CREATE TABLE BOM(PID INT,ID INT)INSERT INTO BOM SELECT 801,101INSERT INTO BOM SELECT 801,102INSERT INTO BOM SELECT 801,103INSERT INTO BOM SELECT 801,601INSERT INTO BOM SELECT 601,101INSERT INTO BOM SELECT 601,105INSERT INTO BOM SELECT 601,501INSERT INTO BOM SELECT 501,106INSERT INTO BOM SELECT 501,121GOCREATE FUNCTION F_GETROOT(@PID INT)RETURNS INTASBEGIN DECLARE @ID INT WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID) BEGIN SET @ID=@PID SELECT @PID=PID FROM BOM WHERE ID=@ID END RETURN @PIDENDGOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOMGO/*PID ID----------- ----------- 801 101801 102801 103801 601801 101801 105801 501801 106801 121*/DROP FUNCTION F_GETROOTDROP TABLE BOMGO--生成测试数据create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute varchar(20))insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'insert into BOM_1 select 3 ,'A2','A3',3,'生产'insert into BOM_1 select 4 ,'A2','A4',2,'采购'insert into BOM_1 select 5 ,'A3','A5',2,'采购'insert into BOM_1 select 6 ,'A3','A6',1,'采购'insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'insert into BOM_1 select 9 ,'B2','B3',3,'生产'insert into BOM_1 select 10,'B2','B4',2,'采购'insert into BOM_1 select 11,'B3','B5',2,'采购'insert into BOM_1 select 12,'B3','B6',2,'采购'go --创建用户定义函数，用于取每个父节点下子节点的采购配置信息create function f_stock(@bom_head varchar(20))returns @t table(bom varchar(20),number int)asbegin declare @level int declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int) set @level=1 if exists(select 1 from BOM_1 where bom_head=@bom_head) insert into @a select bom_child,number,products_attribute,@level from BOM_1 where bom_head=@bom_head while exists(select 1 from @a where [level]=@level and products_attribute='生产') begin set @level=@level+1 insert into @a(bom,number,products_attribute,[level]) select a.bom_child,a.number,a.products_attribute,@level from BOM_1 a,@a b where a.bom_head=b.bom and b.[level]=@level-1 end insert into @t(bom,number) select bom,number from @a where products_attribute='采购' returnendgo--执行调用，取父节点'A'一个标准配置分解的采购信息及数量select * from dbo.f_stock('A')--生成测试数据create table BOM(ID INT,PID INT,MSG VARCHAR(1000))insert into BOM select 1,0,NULLinsert into BOM select 2,1,NULLinsert into BOM select 3,1,NULLinsert into BOM select 4,2,NULLinsert into BOM select 5,3,NULLinsert into BOM select 6,5,NULLinsert into BOM select 7,6,NULLgo--创建用户定义函数用于取每个父节点下子节点的采购配置信息create function f_getChild(@ID VARCHAR(10))returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)asbegin declare @i int set @i = 1 insert into @t select ID,PID,@i from BOM where PID = @ID while @@rowcount<>0 begin set @i = @i + 1 insert into @t select a.ID,a.PID,@i from BOM a,@t b where a.PID=b.ID and b.Level = @i-1 end returnendgo--执行查询select ID from dbo.f_getChild(3)go--输出结果/*ID----567*/--删除测试数据drop function f_getChilddrop table BOM创建用户定义函数,每个子节点de父节点的信息--生成测试数据create table BOM(ID int,parentID int,sClassName varchar(10))insert into BOM values(1,0,'1111' )insert into BOM values(2,1,'1111_1' )insert into BOM values(3,2,'1111-1-1' )insert into BOM values(4,3,'1111-1-1-1') insert into BOM values(5,1,'1111-2' )go--创建用户定义函数,每个子节点de父节点的信息create function f_getParent(@ID int)returns varchar(40)asbegin declare @ret varchar(40) while exists(select 1 from BOM where ID=@ID and parentID<>0) begin select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'') from BOM a,BOM b where a.ID=@ID and b.ID=a.parentID end set @ret=stuff(@ret,1,1,'') return @retendgo--执行查询select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOMgo--输出结果/*ID parentID ----------- ---------------------------------------- 1 2 13 1,24 1,2,35 1 */--删除测试数据drop function f_getParentdrop table BOMgo
[解决办法]
1、两个F都疯了。
2、一般来说，双编码和单编码确实各有优缺点，正如楼上所说的。
个人认为：为什么不能两种方式都用呢？当然在两张表都需要建立触发器。
在插入数据时，可以使用双编码，在双编码表上建立触发器，同时更新单编码表，在查询的 时候从单编码表中查询，避免递归层次太深。
这种方式不知是否可以呢？
[解决办法]
父子树形结构 还有一种巧妙设置为 id ,name ,parentid , IDList 查询超级方便
IDList存放的是所有父级的ID列表，如,1（爷爷级）,3（父级）,4(自己),
这样爷爷级要查自己的子孙，只要跟IDList查询含有,1,的就可以