2

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.

Improve this question

1 Answer 1

Reset to default
1

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
Improve this answer
1
  • 1
    To add on to this for future learning, this makes the tables narrower, with fewer columns, and with more rows. It's easier for database software to search through a million rows with 4 columns than it is to search through a couple thousand rows with 500 columns. It's also a lot easier to add a new color to the dictionary table and just start using it, rather than trying to modify the table structure because now you have a new shade of red.
    – John Herbert
    Commented May 12, 2020 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.