Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Question Unanswered: Question about db Design and usage

    I'm laying out a simple order entry/inventory system for a client. ASP.NET 3.5 and SQL Server 2005. I can't seem to get my head around the best way to approach my problem.

    Items in the Products table may be a physical item or a kit consisting of a group of items. So far, so good. However, a kit (assembly/subassembly) may contain a kit/subassembly as a member. It is a many to many relationship so the conventional parent_id pointer can't work.

    I've attempted to use a junction table like so:

    Code:
    kit_id  int  identity
    product_id int          relates to Product->Product_id 
    child_product_id int  relates to Product->Product_id
    quantity decimal (18,4)
    I could write a recursive function but I need to use this in reports, grids, etc. I need to find a way to extend the tableadapter or create a stored procedure.

    This isn't rocket surgery, somebody must have done something like this before and I'd rather not re-invent the wheel. Any bright ideas?

    Thanks,
    Eddie

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your design is fine, except that you don't actually need kit_id, just make the two FKs a composite PK

    as for reporting, sorry, i can't help you with that, there are too many ways to approach it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use a CTE (Common Table Expression).

    I believe there is an example of doing just this kind of thing in the BOL (SQL Server Books Online).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2010
    Posts
    3
    Thanks for the CTE tip. I modified an example from MSDN. It returns the correct amount of rows but its the same parent row repeated. Can you see my error? Link to original sample, DDL and query included:

    MS article with sample: Recursive Queries Using Common Table Expressions

    Primary table

    Code:
    CREATE TABLE [dbo].[Products](
    	[products_id] [int] IDENTITY(1,1) NOT NULL,
    	[products_name] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[products_displayname] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_CategoryID] [int] NULL,
    	[products_class] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_QuantityPerUnit] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock]  DEFAULT ((0)),
    	[products_UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder]  DEFAULT ((0)),
    	[products_ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel]  DEFAULT ((0)),
    	[products_size] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_style] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_color] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_image] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued]  DEFAULT ((0)),
    	[products_cost] [decimal](18, 2) NULL,
    	[products_uom] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_order_uom] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[products_msrp] [decimal](18, 2) NULL,
    	[products_kitid] [int] NULL,
     CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
    (
    	[products_id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    Junction Table
    Code:
    CREATE TABLE [dbo].[Kits](
    	[kit_id] [int] IDENTITY(1,1) NOT NULL,
    	[product_id] [int] NOT NULL,
    	[child_product_id] [int] NOT NULL,
    	[qty] [int] NOT NULL CONSTRAINT [DF_kits_Qyt]  DEFAULT ((1)),
     CONSTRAINT [PK_Kits] PRIMARY KEY CLUSTERED 
    (
    	[kit_id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    CTE query
    Code:
    WITH FullKit ([products_id]
          ,[products_name]
          ,[products_displayname]
          ,[products_CategoryID]
          ,[products_class]
          ,[products_QuantityPerUnit]
          ,[products_UnitsInStock]
          ,[products_UnitsOnOrder]
          ,[products_ReorderLevel]
          ,[products_size]
          ,[products_style]
          ,[products_color]
          ,[products_image]
          ,[products_Discontinued]
          ,[products_cost]
          ,[products_uom]
          ,[products_order_uom]
          ,[products_msrp]
          ,[products_kitid]
    	  ,[kit_child_product_id]
    )
    AS
    (
    -- Anchor member definition
        SELECT p.*, k.Child_Product_Id 
        FROM Products AS p
        INNER JOIN kits AS k
            ON p.products_id = k.product_id 
        WHERE  p.products_id = 13
        UNION ALL
    -- Recursive member definition
        SELECT p.*, k.child_product_id
        FROM Products AS p
        INNER JOIN Kits AS k
            ON p.products_id = k.product_id
        INNER JOIN FullKit AS d
            ON p.Products_id = d.kit_child_product_id
    )
    -- Statement that executes the CTE
    SELECT * 
    FROM FullKit;
    GO
    Last edited by eflyerman; 04-04-10 at 15:56.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have removed the IDENTITY from the PK's for ease of entering the examples.
    Code:
    INSERT INTO Products (products_id, [products_name], [products_Discontinued]) 
    SELECT 1, 'car', 1
    	UNION ALL
    SELECT 2, 'engine', 1
    	UNION ALL
    SELECT 3, 'screw', 1
    	UNION ALL
    SELECT 4, 'seat', 1
    
    INSERT INTO Kits (	kit_id, product_id, child_product_id, qty )
    select 1, 1, 2, 1
    	UNION ALL
    select 2, 2, 3, 10
    	UNION ALL
    select 3, 1, 4, 4
    	UNION ALL
    select 4, 4, 3, 15
    go
    
    WITH FullKit (
    	products_id
    	, products_name
    	, kit_child_product_id
    	, Child_product_name
    	, child_qty
    )
    AS
    (
    -- Anchor member definition
        SELECT 	P.products_id, P.products_name, k.Child_Product_Id, ChildP.products_name, k.qty
        FROM Products AS p
        INNER JOIN kits AS k
            ON p.products_id = k.product_id 
        INNER JOIN Products AS ChildP
            ON k.child_product_id = ChildP.products_id 
        WHERE p.products_id = 1
        UNION ALL
    -- Recursive member definition
        SELECT FK.products_id, FK.products_name, 
            k.child_product_id, ChildP.products_name, 
            FK.child_qty * k.qty
        FROM FullKit AS FK
        INNER JOIN Kits AS k
            ON FK.kit_child_product_id = k.product_id
        INNER JOIN Products AS ChildP
            ON k.child_product_id = ChildP.products_id 
    )
    -- Statement that executes the CTE
    SELECT kit_child_product_id
    	, Child_product_name
    	, sum(child_qty) as qty
    FROM FullKit
    GROUP BY kit_child_product_id
    	, Child_product_name
    ;
    GO
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Apr 2010
    Posts
    3
    You are the bomb! I didn't expect it to be done for me. Thanks for your post. I can't say that I fully understand why you had to use the group by clause. I haven't taken the time to dissect the pieces yet but I will.

    Thanks again.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by eflyerman View Post
    I can't say that I fully understand why you had to use the group by clause.
    With GROUP BY, you get this result:
    Code:
    2	engine	1
    3	screw	70
    4	seat	4
    Without it you get :
    Code:
    2	engine	1
    4	seat	4
    3	screw	60
    3	screw	10
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •