March 11, 2008 at 3:45 am
Hello everyone.
Can someone point me to a decent tutorial on how to create Bill of materials using the nested set model?
my db tables are roughly as follows:
1. Table Products:
CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[Unit] [char](3) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
2. Table Assemblies:
CREATE TABLE [dbo].[Assembly](
[AssemblyID] [int] NOT NULL,
[Cost] [decimal](18, 2) NOT NULL CONSTRAINT [DF_Assembly_Cost] DEFAULT ((0)),
[lft] [bigint] NOT NULL,
[rgt] [bigint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Assembly] WITH CHECK ADD CONSTRAINT [FK_Assembly_Products] FOREIGN KEY([AssemblyID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[Assembly] CHECK CONSTRAINT [FK_Assembly_Products]
I have developed code to correctly insert and retreieve nodes.
The question I'm interested in regards summing up the "Unit Price" for a given Node:
Let's say that we have assembly named "Assembly1" which consists of 3 units of Product1, which costs 10$ and 12 units Of Product2 which costs 5$ a piece, and 2 units of Product3, which in turn is again an assembly built from 1 unit of Product4 (10$) and 2 Units of Product5 (2.5$)
Simple mathematics tells me that the price of Assembly1 should be: 3x10+12x5+2x(1x10+2x2.5)=30+60+2x15=120$
How do I create a function that would enable me to do so in T-SQL
Thanks
March 11, 2008 at 5:22 am
Good morning, I would suggest a little different data model.
Since Assemblies contain other assemblies its a little awkward.
Plus the way you were joining products to assemblies was not the way you would need that to work. The proper way to model products to assemblies is to have a products table, an assemblies table and an assemblyproducts table. The assembly products table would hold the assembly id, product id, and the count of that product in the assembly.
I have added the concept of a Project here, which will contain Assemblies, which in turn are made up of Products. If you model Product 3 as Assembly 2, you can create Project 1 containing Assembly 1 and 2 units of Assembly 2. I modeled the relationship of Project to Assembly the same as I modeled Assembly to Product.
Here are sample table layouts and a query to get the kind of results you want.
USE [SQLHelp]
GO
/****** Object: Table [dbo].[Assembly] Script Date: 03/11/2008 06:10:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Assembly](
[AssemblyID] [int] IDENTITY(1,1) NOT NULL,
[AssemblyName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lft] [bigint] NOT NULL,
[rgt] [bigint] NOT NULL,
CONSTRAINT [PK_Assembly] PRIMARY KEY CLUSTERED
(
[AssemblyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [SQLHelp]
GO
/****** Object: Table [dbo].[Products] Script Date: 03/11/2008 06:11:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Unit] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UnitCost] [money] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [SQLHelp]
GO
/****** Object: Table [dbo].[Project] Script Date: 03/11/2008 06:11:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Project](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [SQLHelp]
GO
/****** Object: Table [dbo].[ProjectAssembly] Script Date: 03/11/2008 06:11:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProjectAssembly](
[ProjectAssemblyID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NULL,
[AssemblyID] [int] NULL,
[AssemblyCount] [int] NULL,
CONSTRAINT [PK_ProjectAssembly] PRIMARY KEY CLUSTERED
(
[ProjectAssemblyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProjectAssembly] WITH CHECK ADD CONSTRAINT [FK_ProjectAssembly_Assembly] FOREIGN KEY([AssemblyID])
REFERENCES [dbo].[Assembly] ([AssemblyID])
GO
ALTER TABLE [dbo].[ProjectAssembly] CHECK CONSTRAINT [FK_ProjectAssembly_Assembly]
GO
ALTER TABLE [dbo].[ProjectAssembly] WITH CHECK ADD CONSTRAINT [FK_ProjectAssembly_Project] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Project] ([ProjectID])
GO
ALTER TABLE [dbo].[ProjectAssembly] CHECK CONSTRAINT [FK_ProjectAssembly_Project]
USE [SQLHelp]
GO
/****** Object: Table [dbo].[AssemblyProduct] Script Date: 03/11/2008 06:10:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AssemblyProduct](
[ProductAssemblyID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[AssemblyID] [int] NULL,
[Units] [int] NOT NULL,
CONSTRAINT [PK_ProductAssembly] PRIMARY KEY CLUSTERED
(
[ProductAssemblyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AssemblyProduct] WITH CHECK ADD CONSTRAINT [FK_ProductAssembly_ProductAssembly] FOREIGN KEY([AssemblyID])
REFERENCES [dbo].[Assembly] ([AssemblyID])
GO
ALTER TABLE [dbo].[AssemblyProduct] CHECK CONSTRAINT [FK_ProductAssembly_ProductAssembly]
GO
ALTER TABLE [dbo].[AssemblyProduct] WITH CHECK ADD CONSTRAINT [FK_ProductAssembly_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[AssemblyProduct] CHECK CONSTRAINT [FK_ProductAssembly_Products]
--And the query
SELECT ProjectName,
AssemblyName,
AssemblyCount,
ProductName,
AssemblyCount*Units ProductUnitsNeeded,
UnitCost,
AssemblyCount*UnitCost*Units AS ProductCost
FROM project p
INNER JOIN ProjectAssembly pa ON p.projectid = pa.projectid
INNER JOIN Assembly a ON a.assemblyid = pa.assemblyid
INNER JOIN AssemblyProduct ap ON ap.assemblyid = a.assemblyid
INNER JOIN Products pd ON pd.productid = ap.productid
October 5, 2011 at 8:02 am
Spend some money and buy a copy of 'Trees and Hierarchies in SQL for Dummies' by Joe Celko - it covers this topic in some detail.
October 6, 2011 at 9:13 am
david-1019744 (10/5/2011)
Spend some money and buy a copy of 'Trees and Hierarchies in SQL for Dummies' by Joe Celko - it covers this topic in some detail.
You do realize this thread is more than 3 years old?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply