April 5, 2008 at 6:13 am
Hi guys,
I'm looking for some help in designing a database schema. Im planning to build my own online clothing store selling t-shirts, jeans, sneakers, tops etc. Mainly clothing but also some other cool bits, for an idea of the sort of products I plan to sell check out http://krudmart.com/.
I have some database design experience from my software engineering degree but thought I'd try and get some advice as I try to come up with a good design.
My basic issue is handling the various attributes my products could have. I plan to have a fair amount of detail for each product and need a searchable, scalable, efficient way of handling this.
My main problem is that different products will have different attributes that i need to record.
E.G. T-shirts will have dimensions that i need to record such as length, width, neck width etc as well as other attributes such as colour, they might also have a variable number of images associated with them as some t-shirts have more details that need to be shown
Jeans/trousers might need attributes such as waist size, length, material
trainers might need uk size, us size, euro size, colour scheme
*MY THOUGHTS*
One way i thought of approaching this is to have a high level products table with attributes common to all products - price, sku, category, onSale
And then have sub-tables for more specific product types such as a t-shirts table, trousers table, trainers table
My other thought is the possibly of using XML within the database, I plan to develop this in .Net and SQL Server as I have to use these technologies at work.
Any ideas or useful material will be massively appreciated!
Cheers
Sam
April 7, 2008 at 1:41 pm
Before I used a sub-table for each different product type, I would use a single 'ProductAttributes' table, and an attribute lookup table to hold an instance of each attribute.
The 'ProductAttributes' table would be an associative table between the attribute lookup table and the products table, and would have the ProductID, the attribute-id, and the value of the attribute.
The biggest pro of doing this is flexibility. If you add a new product line that contains attributes you don't have, you simply add the new attributes to the attribute lookup table, and then make the necessary data entries to the 'ProductAttributes' table. If you use separate sub-tables, you have to build a new table for each new product. Also I can imagine the queries to return the data from different sub-queries would be more complex.
Like you I would keep the common attributes in the main Products table.
XML is an option, as you have said, and that gives you lots of flexibility as well. Using XML will depend on your comfort-level with XML. I can't comment too much on the XML as my experience was using it to stream the data to and from the database, not to store the XML in the database. The one thing I liked most about XML was the ability to represent hierarchical data (parent-child) and to render it on the GUI.
Hope this helps!
If it was easy, everybody would be doing it!;)
September 30, 2008 at 5:00 am
Hi Samaspin
Did you ever get anywhere with this? I'm designing a website that sells products with multiple attributes and was thinking of doing a similar design:
1 main product table that contains productID, product name, details, base price and a productvariant combination column.
1 child table that contains product variants. This product variant table will contain a productvariantID, a productFK, an XML string that contains the variant details and possibly details such as price difference and number in stock.
The site will sell hockey equipment so for example, there will be a couple of rows in the main product table as follows:
ProductID, ProductName, ProductDetails, BasePrice, ProductVariantCombo
1001, "Malik Predator", "This is a good hockey stick", 150.00, "Length_Weight"
1002, "TK Hockey Shirt", "This is a great shirt", 90.00, "Colour_Size"
The product variant table will contain rows similar to this:
1, 1001, "<XML><Length>36.5</Length><Weight>Light</Weight></XML>", 0, 5
2, 1001, "<XML><Length>36.5</Length><Weight>Medium</Weight></XML>", 0, 3
3, 1001, "<XML><Length>37.5</Length><Weight>Medium</Weight></XML>", 0, 1
4, 1001, "<XML><Length>37.5</Length><Weight>Heavy</Weight></XML>", 0, 6
5, 1002, "<XML><Colour>Blue</Colour><Size>S</Size></XML>", 0, 3
6, 1002, "<XML><Colour>Blue</Colour><Size>M</Size></XML>", 0, 6
7, 1002, "<XML><Colour>Blue</Colour><Size>L</Size></XML>", 0, 2
What I hope to have on the website is a page that displays the general product info from the main product table for an individual product, and then underneath that to have a table that displays the variants available, where a user can select which variant of this particular product they wish to add to their shopping cart.
I think this is a pretty good way of storing the information. Where I fall down is designing an admin console that I can use to update the information. I'm working on that, and if I manage to get it sorted and you're interested, let me know and I can forward the code on.
Regards
Steven
November 10, 2008 at 6:31 am
What you guys are talking about is EAV (Entity Attribute Value) patterns. Since you're only dealing with products, you EAV design will be simple and not have a lot of the complexities other EAV models have. Either way you do it, Xml or another ProductAttributes table, it's still EAV. Now a days you can easily report on Xml columns splitting out the internal data into Columns for reporting if you ever need to see "what are all the [Jeans] I am selling that come in [Red] or [Green]?". However, if all your Product Attributes do not have the same Xml Schema, and you can't bind an XmlSchema to the columns, then your performance will suffer - for reporting only. Don't let anyone tell you Xml is not performant, it all depends on how you do it.
Usually in my EAV models I will make 3 different "Value" columns. One for simple scaler values, another for Xml, and another for any binary data. This allows you to use the most efficient data type for what you're storing. For example, let's say you have an Image for each color combination of Jeans, these images could be stored as "Attributes" and use the "ValueBinary" column to store the Image data (keep in mind you'll need to store something to tell you the MimeType of the Image unless you're hard-coding it). Or maybe you want to store descriptions of each Product in many different languages, these again are just Attributes with a specific Culture code.
ProductAttributes: - (Product & Attribute IDs make up Primary Key)
ProductID
AttributeID
Culture (varchar(5) - ex. en-US, es-ES, or Null for all Cultures)
Value (sql_variant or varchar(8000))
ValueXml (xml)
ValueBinary (varbinary(MAX))
November 3, 2023 at 12:19 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply