Hello, I'm working on an e-commerce site and I need to create product attributes that can be assigned to products.  I'm only poking around in the dark as I'm not sure how it should be done but the following is what I have:

Product Table, ProductAttributes Table, ProductsWithAttributes Table.

- The product table just list the products.

- The ProductAttributes table allows the creation of reusable attributes like color, size, weight etc. (any number of attribute labels with a short decription - optional).

- The ProductsWithAttributes table list the ProductID, AttributeID, AttributeValue, and AttributePrice.

Am I going in the right direction with this? I need to design a flexible Attribute system that can be applicable no matter what the store sells. Any ideas or suggestions are welcome.

Thank you!

how about database load.. i feel it is better to use FileSystem and Database togeather.

Like Filesystem for Master Settings and Database to get the coredata.

or you can desingn your database carefully also... i see some drawback in your design..

like tagging Attribute and WithAttribute places.. in long run it may increase complexity

The scenario you've described is often a good fit for an XML column.  That should also allow you to combine your three tables into one.  One advantage of XML is that it allows recursive properties (properties on properties).

saburius:

Am I going in the right direction with this? I need to design a flexible Attribute system that can be applicable no matter what the store sells. Any ideas or suggestions are welcome.

Yes the design is fine, because you have normalized the tables correctly. This is a good solution for handling many-to-many relationships in the data model. One column which you can add is IsActive (to activate/de-activate the fields).

HTH,

Vivek

Thank you for all the responses. One of the problems I've realized is the rlationship between the attributes table and the products with attributes table. What happens if an attribute is deleted? Are the attributes that are referenced in productswithattributes become orphaned? I quite confused on this. Also, what is the difference between product attributes and product variants?

It would be awesome to see some kind of article or example of the architecture and methodology.

Thank you!

saburius:

Thank you for all the responses. One of the problems I've realized is the rlationship between the attributes table and the products with attributes table. What happens if an attribute is deleted? Are the attributes that are referenced in productswithattributes become orphaned?

If an attribute related to some product(s) is selected for deletion, you should notify the user with something like "The selected attribute has associations with some products, deleting it will delete those associations also. Press OK to go ahead, else select Cancel".

If the user selects Ok, then you need to delete all associations in the mapper cross table.

saburius:

Also, what is the difference between product attributes and product variants?

Attributes are like properties of a product, where was a product variant can be another type of product, like a Honda SUV and a Sedan are both variants of Honda, but might have same attributes like Color (black).

HTH,

Vivek

saburius:

Thank you for all the responses. One of the problems I've realized is the rlationship between the attributes table and the products with attributes table. What happens if an attribute is deleted?

I think that if there is at least one sale for product with specific attribute you shouldn't allow deletion of this product. I'm almost sure that users of you e-commerce site can see their shopping history. If you show attributes there you have to be sure that you don't change the data that user once accepted before paying.

Which version of SQL Server are you using?

You might like to look at versioning the Product table by splitting it into a ProductRoot, ProductLeaf so that once a ProductLeaf is referenced by an OrderLine then any attempt to change a referenced ProductLeaf causes a new Leaf to made. Once the changes to the new Leaf are complete then the new Leaf is activated and the old deactivated.