Hello,

I'm learning ASP.NET 2.0 in order to build an e-commerce website. The site uses VB and an MsSQL database. I'm confused as to how product attributes in an online store are handled in the SQL database; I have been poring over a number of ASP.NET 2.0 books in search of an answer, but to no avail.

For example, this link to a hiking shoe on the LLbean website

http://tinyurl.com/y6cs4l

shows three pull-down menus on the right column, under "Step 2: Select Options": Width, Shoe Size, and Color/Style. Each pull-down menu contains a number of product attributes, so that the customer can order a shoe in "Medium D, Size 9, Ash Bark" or some other color and size combo.

Up one level, on the page that displays all the hiking shoes in this product group:

http://tinyurl.com/y8hw59

...these options don't appear. Each shoe appears only once as a thumbnail image, and the color and size options only appear when the product detail page for each shoe is opened.

I am building a similar sort of product database, containing many items that have multiple sizes, colors, and attributes. Each product size and color variation has its own SKU (product code #). However, this means that on my product display pages, each size and color option for a product appears seperately as it's own product page and thumbnail, which clutters the page and looks unprofessional. That is, one will see thumbnails of a product appear in size 7, size 8, size 9, etc., instead of just appearing once with the attributes accessable only on the product detail page, collapsed into the pull-down menus.

My SQL database now has columns for size and color, but this means that each color and size option has its own row in the database, and its own SKU. How can I structure the database to get the result described above, which most e-commerce sites seem to use?

Thank you in advance for any help!

Nostromo,

 

What you need are Parent/Child Tables to establish relationships.  You will have one record in a products table, then a related record in an options table for each of the available options.  I have a Visio Diagram that has an e-commerce database scheme laid out on a blog post at http://aspadvice.com/blogs/schenz/archive/2006/04/12/16386.aspx.  You can download the diagram close to the bottom of the post.

 

---

Brandon Schenz
www.schenzcustomdesigns.com

The diagram is extremely helpful - thank you.

I'd be interested to see the documentation for the POS Base Classes, but the link http://schenzcustomdesigns.com/files/POSBaseClassesv2.doc is coming up as 404
Do you have it posted anywhere else?

Thanks again! 

Sorry about that.  It is posted, and the link works again.

 

---

Brandon Schenz
www.schenzcustomdesigns.com