# Shop - Calculate All Product Variants

• ### Question

• User-474965322 posted

I've got myself into a logic pit.

I'm creating a bespoke shop, and I'm going down the route of a product having multiple dynamic attributes (ie, Colour, Size, Texture, etc).

My problem comes after assoicating the product with all its attributes. I can't get my head round calculating all the unqie product variants - for stock control, and individual pricing if needed.

Preferably I'd like to create a sproc using T-SQL and MS SQL to create a relational table full of all the variants. But it's a head scratcher, trying to spit out all the unique possibilities... I can easily work out the total possibilites (Style options x Colour options x Size options) - but after that it starts to get confusing when you introduce a third attributes table...

Eg,

My product: Manly Shirt

Style:
Long Sleeved
Short Sleeved

Colour:
Blue
Green

Size:
Small
Medium

Has the following variants:
Long Sleeved | Blue | Small
Long Sleeved | Blue | Medium
Long Sleeved | Green | Small
Long Sleeved | Green | Medium
Short Sleeved | Blue | Small
Short Sleeved | Blue | Medium
Short Sleeved | Green | Small
Short Sleeved | Green | Medium

Any links or previous experience would be appreciated.

Current DB Logic:
My database holds the logic of a 'products' table, with a relational 'attributes title' table (with sort order), and the 'attributes title' table has a 'relational attributes options' table (also with sort order).

Wednesday, March 3, 2010 6:20 AM

• User-952121411 posted

I have created an inventory system similar to the one you are describing, and I have a couple of comments-

I can't get my head round calculating all the unique product variants - for stock control, and individual pricing

For stock control, the variation "Long Sleeved | Blue | Small" should correspond to an "Item" ID or similar in the database.  You ask, "How does that get created?"  Well typically by the administrator choosing the combinations 1st, and then adding stock to that combination.  Thus in turn generating an ID for that combination.  All of the individual variations can be in their own lookup table, that then make an ItemID in the Items table that references each of the different variation categories.  As long as your variation categories are mostly static (i.e. size, color, description, etc.) then this is not too difficult to accomplish.

Price would follow suit to the above ItemID.  Once it is created, then you can assign a price to that ItemID.  You don't have to worry about assigning price to just "Blue" because that would not make sense.  But "Long Sleeved | Blue | Small" comprises an item and can have an associated price.

As for just determining every possible combination whether it is in stock or not is typically not important, unless somebody wants a report of everything that 'could' exist in inventory.  Typically the report should center around which 'Items' have been received into inventory and their stock count.

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, March 3, 2010 10:48 AM
• User-389939489 posted

I'm creating a bespoke shop, and I'm going down the route of a product having multiple dynamic attributes (ie, Colour, Size, Texture, etc).

The canonical approach goes the other way round:

An "armani's long sleeved, blue, small, xyz shirt" (meaning the name/description for a specific item) *is* a product, with an SKU (or product ID). That is what you price, and that is what you have in stock. It is at *presentation* level only -say, in a printed or web catalogue- that you group products under a "macro-product with variants".

In general, defining presentation attributes for products and macro-products is a manual process performed by a content administrator. In most cases, the products base is not completely heterogeneous: with some analysis it is possible to sort out things like "product types", or "product templates", etc. in order to automatize as much as possible of that process: yet, that is only about how products are presented.

HTH,

-LV

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, March 4, 2010 10:40 AM

### All replies

• User-952121411 posted

I have created an inventory system similar to the one you are describing, and I have a couple of comments-

I can't get my head round calculating all the unique product variants - for stock control, and individual pricing

For stock control, the variation "Long Sleeved | Blue | Small" should correspond to an "Item" ID or similar in the database.  You ask, "How does that get created?"  Well typically by the administrator choosing the combinations 1st, and then adding stock to that combination.  Thus in turn generating an ID for that combination.  All of the individual variations can be in their own lookup table, that then make an ItemID in the Items table that references each of the different variation categories.  As long as your variation categories are mostly static (i.e. size, color, description, etc.) then this is not too difficult to accomplish.

Price would follow suit to the above ItemID.  Once it is created, then you can assign a price to that ItemID.  You don't have to worry about assigning price to just "Blue" because that would not make sense.  But "Long Sleeved | Blue | Small" comprises an item and can have an associated price.

As for just determining every possible combination whether it is in stock or not is typically not important, unless somebody wants a report of everything that 'could' exist in inventory.  Typically the report should center around which 'Items' have been received into inventory and their stock count.

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, March 3, 2010 10:48 AM
• User-389939489 posted

I'm creating a bespoke shop, and I'm going down the route of a product having multiple dynamic attributes (ie, Colour, Size, Texture, etc).

The canonical approach goes the other way round:

An "armani's long sleeved, blue, small, xyz shirt" (meaning the name/description for a specific item) *is* a product, with an SKU (or product ID). That is what you price, and that is what you have in stock. It is at *presentation* level only -say, in a printed or web catalogue- that you group products under a "macro-product with variants".

In general, defining presentation attributes for products and macro-products is a manual process performed by a content administrator. In most cases, the products base is not completely heterogeneous: with some analysis it is possible to sort out things like "product types", or "product templates", etc. in order to automatize as much as possible of that process: yet, that is only about how products are presented.

HTH,

-LV

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, March 4, 2010 10:40 AM
• User-1028074742 posted

Thursday, March 4, 2010 11:24 AM