Database Design – Creating Products Database with Multiple Color Variations

database-design

I found this Q&A already, that describes my problem and a more or less satisfying answer:
Create products with color variants
Basically, I want to set up a database for products with different color variations. Here the solution is given of setting up a product-table, a color-table, and a product-color-table as a combination of both.

Unfortunately, most of my products are available in ~180 different colors and we have about 8000 different articles. Additionally, every product has at least one standard color (up to ~5 standard colors) out of the 180 colors in which the product is cheaper than in other colors.

Do I really have to set up a database as described before, which would lead to over 1 Mil articles, or is there another way?
I thought of setting up a product-color-table in the sense, that there are 180 columns for each color and I just assign them values like "available, not-available, standard" or so. And then all the other information that don't really affect the price of the products in yet another table.

But since I'm new to setting up databases I don't really how I can realise it. Would that actually work? Can anyone give me any tips on how I can solve that and set up my tables?

Thanks in advance

PS: In case my description was confusing here is an example of an onlineshop I found that has the exact thing I want to do:
When you tap on "Ciew colour range" you can see all the different colors. But then there are also other articles that are available in less colors or different standard-colors.

Best Answer

Firstly I would advise strongly against having a column for each colour - This will cause very slow queries and will scale poorly (There is also a data size limit on the ammount of data in a single row in Microsoft SQL Server so this could cause you problems if your number of colours grows).

To represent the many-to-many relationship you have described I would use an intermediary table like this:

CREATE TABLE [Product]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Name NVARCHAR(250)
)

CREATE TABLE [Colour]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Colour NVARCHAR(250)
)

CREATE TABLE [Product_Colour_Availability] -- Many to Many relationship table
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Product_ID INT
,Colour_ID INT
,Available bit -- 1=available, 0=not available
)

--
-- Insert some data for a single product (T-Shirt) that will be both red and blue.

INSERT INTO [Product] (Name)
VALUES ('T-Shirt')

INSERT INTO [Colour] (Colour)
VALUES ('Red'), ('Blue')

INSERT INTO [Product_Colour_Availability] ([Product_ID], [Colour_ID])
VALUES (1,1,1), (1,2,1)

-- Get availability info about a specific product:

SELECT P.[ID] AS 'Product ID'
,P.[Name] AS 'Product Name'
,C.[Colour] AS 'Product Colour'
,PCA.[Available] AS 'Availability'
FROM [Product_Colour_Availability] PCA
LEFT JOIN [Product] P ON PCA.Product_ID=P.[ID]
LEFT JOIN [Colour] C ON PCA.Colour_ID=C.[ID]
WHERE P.[ID] = 1
Related Question