SQL structure help for custom products

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

SQL structure help for custom products

Author

Topic

WidgetMan
Starting Member

1 Post

Posted - 2009-07-28 : 18:27:54

I'm having a heck of a time trying to figure out the best table structure for a site I'm working on.

The site will be selling products that are highly customizable - we'll call them widgets. Each widget can have one or more colors, varying widths and lengths, and one or more accessories. Pricing for the widgets depends on these factors as well as the quantity ordered. So it's not as simple as a table with product - quantity - price.

What's the best way to store all of this information? Should each element be separated into its own table or should there just be a generic "options" table that can store all the different elements of each widget? Or is there a better way?

Thanks!

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 08:52:55

I would favor creating separate tables for each attribute provided that there are known number of attributes. I might even create them in their own schema if there are a bunch of them:

CREATE SCHEMA Attributes
GO
CREATE TABLE Attributes.Color (ColorId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Color_Hex VARCHAR(6))
GO
INSERT Attributes.Color VALUES ('FFF'), ('000000')
GO
CREATE TABLE Product (ProductId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(256) NOT NULL)
GO
CREATE TABLE ProductColor (ProductId INT NOT NULL, ColorId INT NOT NULL)
GO
ALTER TABLE ProductColor ADD CONSTRAINT PK_ProductColor PRIMARY KEY (ProductId, ColorId)
GO
ALTER TABLE ProductColor ADD CONSTRAINT FK_ProductColor_Product
FOREIGN KEY(ProductId) REFERENCES Product(ProductId)
GO
ALTER TABLE ProductColor ADD CONSTRAINT FK_ProductColor_Colors
FOREIGN KEY(ColorId) REFERENCES Attributes.Color(ColorId)

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources