BOM分层结构详解与数据表设计

CREATE TABLE [dbo].[BOM_DETAIL] (
 [PKID] [int] ,--IDENTITY (1, 1) NOT NULL ,
 [BOM_HEAD_PKID] [int] NOT NULL ,
 [CHILDREN_ITEM] [int] NOT NULL ,
 [QTY] [decimal](10, 0) NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[BOM_HEAD] (
 [PKID] [int] ,--IDENTITY (1, 1) NOT NULL ,
 [MASTER_ITEM] [int] NOT NULL ,
 [QTY] [int] NOT NULL ,
 [VER] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [STATUS] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[item] (
 [item] [int] ,--IDENTITY (1, 1) NOT NULL ,
 [brand] [nvarchar] (10) ,
 [part_no] [nvarchar] (10)
) ON [PRIMARY]

insert item select 1 ,'A' ,'A1'
union  all  select 2 ,'B' ,'AAAAA'
union  all  select 3 ,'A' ,'AD'
union  all  select 4 ,'A' ,'SS'
union  all  select 5 ,'C' ,'123'
union  all  select 6 ,'C' ,'AAADSFD'
union  all  select 7 ,'D' ,'D22'
union  all  select 8 ,'C' ,'DDDD512'
union  all  select 9 ,'A' ,'AA3223'