Complex DB design for product catalog

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

SQL Server 2000 Forums

SQL Server Development (2000)

Complex DB design for product catalog

Author

Topic

DougFord99
Starting Member

10 Posts

Posted - 2002-09-15 : 07:09:44

We are designing the database schema for a product catalog that will eventually become the clients e-commerce catalog. The database schema is not all that complicated, in fact the base schema is derived from the "Beginning E-Commerce" book by Wrox Press. The schema allows the catalog administrator to build their own product types (like car, motorcycle, boat, etc) and assign certain attributes to each product type . For example, for a product type of car we may create an attribute to hold the number of doors, engine size, VIN, etc. The schema is very scalable. The complexity is in supporting attributes that have a finite set of values. For example, a product of type shirt would only have S, M, L, XL values. So, we want to continue supporting dymanic attributes, but also support setting a finite set of available options for some attributes. Here is what we are working with so far:

CREATE TABLE tblProduct (
ProductID int IDENTITY(1,1),
CategoryID int NOT NULL,
...
)

CREATE TABLE tblAttribute (
AttributeID int IDENTITY(1,1),
AttributeStructureID int NOT NULL,
ProductID int NOT NULL,
Long_value int,
String_value varchar(256),
Double_value int,
Date_value datetime,
Boolean_value bit
)

CREATE TABLE tblAttributeStructureID (
AttributeStructureID int IDENTITY(1,1),
ProductTypeID int NOT NULL,
Datatype int NOT NULL,
...
)

Using the above schema we can create an attribute structure, using tblAttributeStructure, that will define the attributes for a given product. A value for each attribute can then be added to tblAttribute under the appropriate datatype.

How do I add the ability to create a finite set of values for the attribute based upon the product. For example, some shirts come in S, M, L and other shirts are available in S, M, L, XL and XXL. I want the catalog admin to be able to add as many attribute options as he/she wishes based upon the product.

I hope I have explained this well enough. If not, I would be happy to try again... just let me know! Thanks for your expertise and time!

Sincerely,

Doug F.

Doug Ford
MCSE,MCT

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-15 : 08:33:03

try adding a parent reference to the tblAttribute table that way ... where the parentID is null then those are your master elements. ... if the DataTypeID is set to a 'list' then you would select all elements from that table where the parent id is equal to that element ... i've done this before and its pretty simple... let me know if you need more info...

DougFord99
Starting Member

10 Posts

Posted - 2002-09-15 : 19:53:37

I wrote this question at 3AM. When I read it today I realized I had more explaining to do. The product catalog will be written in ASP using a custom built COM+ component written in VB. When we generate the e-commerce product catalog we want visitors to be able to add items to their shopping cart and choose product attributes from combo boxes when necessary. For example, a visitor may buy a bike that has a variety of a finite size attributes (16in, 18in, 20in) and color attributes (black, white, silver, orange). Before adding the product to their shopping cart they would need to select an item from each of these lists of attributes. Other attributes associated with the product are simply defined within the product catalog and listed as part of the description of the product like bike model, components, etc. So, when the catalog administrator is creating the catalog, they need the ability to create an attribute structure to support static attributes (model, braking components, etc) AND attributes that have a finite number of options like color and size. I sure hope I am explaining this clearly. I really need some advice, if I am being unclear, please let me know and I will try to clear up my intentions.

Best regards,

Doug

Doug Ford
MCSE,MCT

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-15 : 21:00:57

my method will work for you then ... you need to read up on heirarchies within SQL...

basically you have a bunch of attributes ... and then some attributes may be a list ... for those that are lists allow children elements that have a reference to the list element so you can pull all its children. then store the child elements id with the item ... i'm sure there are plenty of examples on how to do this out there ... just look a little more ...

some ddl for you to play with

CREATE TABLE Attributes
(
AttributeID INT IDENTITY,
ParentID INT NULL REFERENCES Attributes(AttributeID),
DataType CHAR(1) NOT NULL CHECK(DataType IN ('S', 'L'),
Label VARCHAR(256) NOT NULL CHECK(LEN(Label) > 0)
)

-- S=Simple, L=List

INSERT INTO Attributes (ParentID, DataType, Label) VALUES (NULL, 'S', 'Standard Radio')
INSERT INTO Attributes (ParentID, DataType, Label) VALUES (NULL, 'L', 'Color') -- ASSUMING AttributeID will be 2 with this INSERT INTO Attributes (ParentID, DataType, Label) VALUES (2, 'L', 'Red')
INSERT INTO Attributes (ParentID, DataType, Label) VALUES (2, 'L', 'Green')
INSERT INTO Attributes (ParentID, DataType, Label) VALUES (2, 'L', 'Blue')

then when you do a select for that attribute (attributeID 2 the color list) you would do

SELECT DataType, Label FROM Attributes WHERE ParentID = 2

hope this helps ... must go take care of a screaming child now :) teething days ... wonderful for late night coding...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources