Shop - Calculate All Product Variants

[RFH Reseller: creativeworks]

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).

[Staff: Warren Ashcroft]

I had a look at this yesterday, but it has been playing on my mind since! I was thinking of all kinds of crazy SQL including recursive queries but it turns out you can just do it with a few joins:

SELECT * FROM x_Products INNER JOIN x_Attributes ON x_Attributes.ProductID = x_Products.ProductID INNER JOIN x_AttributeOptions ON x_AttributeOptions.AttributeID = x_Attributes.AttributeID

That query is based on the following schema which I believe you're using:

CREATE TABLE [dbo].[x_Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[ProductName] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[x_Products] ([ProductID], [ProductName]) VALUES (1, N'T-Shirt')
INSERT [dbo].[x_Products] ([ProductID], [ProductName]) VALUES (2, N'Jumper')

CREATE TABLE [dbo].[x_Attributes](
	[AttributeID] [int] IDENTITY(1,1) NOT NULL,
	[ProductID] [int] NOT NULL,
	[AttributeTitle] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[x_Attributes] ([AttributeID], [ProductID], [AttributeTitle]) VALUES (1, 1, N'Size')
INSERT [dbo].[x_Attributes] ([AttributeID], [ProductID], [AttributeTitle]) VALUES (2, 1, N'Colour')
INSERT [dbo].[x_Attributes] ([AttributeID], [ProductID], [AttributeTitle]) VALUES (3, 2, N'Fabric')

CREATE TABLE [dbo].[x_AttributeOptions](
	[AttributeOptionID] [int] IDENTITY(1,1) NOT NULL,
	[AttributeID] [int] NOT NULL,
	[AttributeOptionValue] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (1, 1, N'Small')
INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (2, 1, N'Medium')
INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (3, 1, N'Large')
INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (4, 2, N'Red')
INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (5, 2, N'Blue')
INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (6, 3, N'Cotton')
INSERT [dbo].[x_AttributeOptions] ([AttributeOptionID], [AttributeID], [AttributeOptionValue]) VALUES (7, 3, N'Wool')

Depending on your usage/products it might be an idea to have the attributes independent from the products and a fourth table specifying which attributes each product has; this way you can reuse single attributes across several products.