SQL DB Design for product builder

0
votes

We're adding functionality to allow product building.

For example, user can configure different attributes which can either be a plain value (height, color etc) or an existing product (there can be a choice of knobs for a piece of furniture).

I'm now constructing the DB design, and I'm looking for suggestions. What I came up with is the following.

Products - existing products table Ex: ProductA

ParentProducts - idParentProduct Ex: ProductB

ParentProduct_Attributes - idParentProduct, idAttribute, Type (Product/Value) Ex: Height, Knob

ParentProduct_Attribute_Values - idAttribute, idValue, Value Ex: 10"

ParentProduct_Attribute_ChildProducts - idAttribute, idChildProduct, Type (Reg. Product / Child Product) Ex: ChildProductA, ProductA

ChildProducts - idChildProduct

This just seems like a lot of steps to get the actual attribute value.

For example, I have a piece of furniture, ProductB. ProductB has height and Knob. The choices for height are 10", 12" (values), and the choices for knobs are ChildProductA, ProductA (Products) - each coming from different tables. ChildProductA is a childproduct, while ProductA is a regular product, so again, two separate tables.

Any input on this? Different ideas?

Hope I was clear.

Thank you.

2
sqldatabase-design
Is there a definite hierarchy 'depth' in place for each product? In other words, does each Child belong to one Parent, or can a Child product have other Children? – TelJanini
Let's assume just one level down for now - a product can't have grand-children. – Rivka

2 Answers

0
votes
ParentProduct_Attributes - idParentProduct, idAttribute, Type (Product/Value) Ex: Height, Knob

ParentProduct_Attribute_Values - idAttribute, idValue, Value Ex: 10"

instead of creating a separate table for values(ParentProduct_Attribute_Values ) it should be in one table. Denormalization help to fasten queries in some condition. since one join will be reduces for every query Select values table .

0
votes

I would suggest the following structure:

Products - idProduct, type (Regular / Parent / Child)
Product_Attributes - idAttribute, idProduct
Product_Attributes_Values - idValue, idAttribute, value, idProduct

This way you are left with only 3 tables. The Product_Attributes_Values table has the fields value and idProduct which are both NULL-able. If value is set, then it is a simple value. If idProduct is set, then it's a product and it should be a foreign key for Products.

The type differentiation between Regular, Parent and Child products is made through the type field in Products. If needed, you can make additional checks and/or validation in your application, outside the database.