storing disparate products in a database
-
Hi folks,
I have been trying to implement a database solution that will sell lots of different, disparate, products.The problem is that some of the products can be sold with additional components installed.(I have already suggested that maybe we should sell the components as a separate entity, but they weren’t too keen on this 🙁
I am unsure how to solve this issue.I believe that it would not be good practice to set up a different table for each product?
I have set up a table to deal with the generic attributes of all products.
tblBaseProducts
baseProductId
categoryId
capacity
length
width
height
weight
Now, as we may be selling the same product to different clients
At different prices, I set up the following table, which will allow me to do this.
tblProductSales
baseProductId
productId
unitPrice
leadTime
Now, in order to deal with the additional components, would it be a good idea to set up a new table for each additional component that a product has eg, a “shopping cart” that has a “babychair” component
tblProductBabychair
baseProductId
productId
isBabyChair (bit value)
As you can see, I am a bit confused.But I feel that I understand database concepts pretty well.If anyone can help, I’d really appreciate it.
Many thanks,
Yogi.
-
I would recommend maybe a product component table. This would be a 1 to many relationship that can tie additional components to a given product. This table could have baseProductId and a ComponentProductId. If your saying that the components need the same characteristics as the products then the componentID is a product ID. I would NOT use something like isBabyChair. Way too limiting. Use something like ComponentTypeCd and componentValue. THen you could have a ComponentType table with something like Type = "BC", Decode = "BabyChair". Let me know if this makes sense.
Darren
Darren -
Hi Darren,
Thanks for the reply.
I have created a table tblProductComponent
It has the following fields
tblProductComponent
baseProductId (references tblProduct.baseProducId)
componentProductId (references tblComponent)
So I can now run the follwing storeProc that will return all the trolleys that have a “babychair” component
CREATE PROCEDURE spGetAllBabyChairTrolleys
(
@componentCategoryId INT
)
AS
SELECT p. baseProductId, p.shortDescription
FROM tblProducts p, tblComponent c, tblProductComponent pc
WHERE c.categoryId = @componentCategoryId
AND pc.componentId = c.componentId
AND p. baseProductId = pc. baseProductId
GO
Do you think that this is the best solution to use?Would this be what you would do?
Also, this storedProc is hard-coded to only bring back a product that contains a single component.
Would there be any way to have a storedProc that potentially received MULTIPLE input parameters, and returned all the trolleys that satisfied this criteria?
e.g , when User selects multiple component CategoryId’s from a dropdown list?Sorry to ask, you’ve already helped more than you know.
I feel as if i am almost there.Can anyone suggest anything?
Cheers,
yogi
-
Hi Darren,
Thanks for the reply.
I have created a table tblProductComponent
It has the following fields
tblProductComponent
baseProductId (references tblProduct.baseProducId)
componentProductId (references tblComponent)
So I can now run the follwing storeProc that will return all the trolleys that have a “babychair” component
CREATE PROCEDURE spGetAllBabyChairTrolleys
(
@componentCategoryId INT
)
AS
SELECT p. baseProductId, p.shortDescription
FROM tblProducts p, tblComponent c, tblProductComponent pc
WHERE c.categoryId = @componentCategoryId
AND pc.componentId = c.componentId
AND p. baseProductId = pc. baseProductId
GO
Do you think that this is the best solution to use?Would this be what you would do?
Also, this storedProc is hard-coded to only bring back a product that contains a single component.
Would there be any way to have a storedProc that potentially received MULTIPLE input parameters, and returned all the trolleys that satisfied this criteria?
e.g , when User selects multiple component CategoryId’s from a dropdown list?Sorry to ask, you’ve already helped more than you know.
I feel as if i am almost there.Can anyone suggest anything?
Cheers,
yogi
-
Sorry folks, I accidentally posted the reply twice. yogi
-
yogiberr, This is not unlike a Part's relationship to its Bill of Materials. Each Part can be made up of many other Parts. In theory, you'll only need a tblPart, tblBOM, and tblBOMPart.
tblPart:
PartID,
PartNumber,
.
.
.
BOMID
tblBOM
BOMID,
.
.
.
tblBOMPart
BOMID
PartID
.
.
.
You can now enforce integrity of the Bill of Materials by making sure nothing gets into tblBOMPart that isn't already in Part and making sure that no Part in tblPart gets killed if it resides in a BOM (tblBOMPart). This model also suggests the capability for 'exploded' BOMS in which a single end-product is made up of multiple sub-assemblies and/or parts, and each sub-assembly is made up of multiple sub-assemblies and/or parts, etc..., etc... (it's turtles all the way down
).This is largely the same as your proposal, with the exception that we've removed the necessity of a separate Component table.
HTH,
SJTerrill
-
Hi SJ,
Thanks for the reply.I have studied the example but am still a bit confused.I have included my questions next to your post.I hope you don't mind. 🙂
In fact maybe if I left an example.
Eg, if I have a part, with a partId of 1, which is made up of two parts: partId = 2, and partId =3
Please do not think that I am being lazy, I feel that I almost grasp it, but maybe if you could fill in the example..I'd be really grateful.
tblPart:
PartID,
PartNumber, ( do I need this field?)
.
.
.
BOMID (
tblBOM
BOMID, (do I insert a random number here?)
. (what other fields would I need?)
.
.
tblBOMPart
BOMID
PartID (does this reference tblPart.PartID)
.
.
Many thanks,
yogiberr
-
yogiberr, no fear! You've not been interpreted as lazy! Following are some brief responses. If this remains clear as mud, I'll try and dig up a real-world example from my bag of tricks.
tblPart:
PartID,
PartNumber, ( do I need this field?) --SJT--Strictly speaking, No. However, I'm of the school that use of a simple datatype for a primary key (e.g., int) is preferable to other methods. The PartID is assumed to be an int PK. The PartNumber is how the part is represented to humans.
.
.
.
BOMID (
tblBOM
BOMID, (do I insert a random number here?) --SJT--No. This follows the same principle as the PartID, above. You could use an identity column, but I'd be interested it what others think about using identities for PKs... anyone?
. (what other fields would I need?) --SJT-- How about: BOMName, BOMRevision, EffectivityDate, ExpiryDate. It REALLY depends on your needs. Since this sounds like a sales order management system, the BOM data may not need to be highly robust.
.
.
tblBOMPart
BOMID
PartID (does this reference tblPart.PartID) --SJT--You get it exactly!
.
.
Regards,
SJTerrill
-
Hi SJ,
Thanks for the reply.I think I am following.
Eg, if I had a part, partId 1, which was made up of 2 other parts,
Eg, partId 2 and partId 3
Would the tables be as follows?
tblPart:
PartID 1, 2, 3
.
.
BOMID 1
tblBOM
BOMID 1
.
.
tblBOMPart
BOMID 1, 1
PartID 2, 3
Is this correct? In tblBOMBPart, are both fields primary keys? Is it true that the PartID of the of the non-constituent Part should NOT appear in tblBOMPart?
Even if by some miracle I have managed to get this right, is there any chance you could include a small example?I am under serious pressure at this stage. I understand that you might not have time to include an example.But if you could tell me that I have followed you correctly, that’d be great.
Many thanks.
yogiberr
-
yogiberr, You have indeed followed correctly. You may want to use table & column names more familiar to users of an order management system: Part or Product for Part, Kit (maybe) for BOM, and KitPart for BOMItem.
It is the combination of the BOM (Kit) ID and Part ID columns that comprise the primary key in the BOMItem (KitPart) table. You understand exactly!
I'll post a sample after I get off work today. That'll be evening EDT sometime.
HTH,
SJTerrill
-
Hi SJ, that's a great relief to know that I am getting there.Thanks for the help.If you do manage to get time to post a small example, I'd really appreciate it.
All the best from sunny scotland,
yogiberr
-
yogiberr, I just scripted out this structure (SQL2K). Only three tables and barebones columns, but I hope it helps. Just create an empty database and run this.
The structure explicitly requires a Part record before any other record may be populated in any other table. Obviously, you'll have to insert a BOM record before a BOMPart, as well.
Regards from the US,
SJTerrill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblBOMPart_tblBOM]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblBOMPart] DROP CONSTRAINT FK_tblBOMPart_tblBOM
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPart_tblBOM]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPart] DROP CONSTRAINT FK_tblPart_tblBOM
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblBOMPart_tblPart]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblBOMPart] DROP CONSTRAINT FK_tblBOMPart_tblPart
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblBOM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblBOM]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblBOMPart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblBOMPart]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPart]
GO
CREATE TABLE [dbo].[tblBOM] (
[BOMID] [int] NOT NULL ,
[BOMName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblBOMPart] (
[BOMID] [int] NOT NULL ,
[PartID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPart] (
[PartID] [int] NOT NULL ,
[PartNumber] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BOMID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblBOM] WITH NOCHECK ADD
CONSTRAINT [PK_tblBOM] PRIMARY KEY CLUSTERED
(
[BOMID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblBOMPart] WITH NOCHECK ADD
CONSTRAINT [PK_tboBOMPart] PRIMARY KEY CLUSTERED
(
[BOMID],
[PartID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPart] WITH NOCHECK ADD
CONSTRAINT [PK_tblPart] PRIMARY KEY CLUSTERED
(
[PartID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblBOMPart] ADD
CONSTRAINT [FK_tblBOMPart_tblBOM] FOREIGN KEY
(
[BOMID]
) REFERENCES [dbo].[tblBOM] (
[BOMID]
),
CONSTRAINT [FK_tblBOMPart_tblPart] FOREIGN KEY
(
[PartID]
) REFERENCES [dbo].[tblPart] (
[PartID]
)
GO
ALTER TABLE [dbo].[tblPart] ADD
CONSTRAINT [FK_tblPart_tblBOM] FOREIGN KEY
(
[BOMID]
) REFERENCES [dbo].[tblBOM] (
[BOMID]
)
GO
Edited by - TheWildHun on 04/15/2003 4:57:26 PM
-
Hi SJ,
magic.It's the breakthru I was needing.I'm now in the process of running queries which return all the products that compose the main product.
Many, many thanks to yourself and also to Darren.
Ta,
yogiberr
).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply