How to clearly design a database?

Question

PHP

Hi people.
When designing my online store, I ran into a problem.
I write data from the form to the database (name, description, price). The problem is that these products need a color, and each product will have a different number of colors and the colors themselves, I created a separate color table, but I don’t know the exact number of columns (because I don’t know how many colors the product will have), if the columns are added according to means ALTER, then I will have cells with NULL, I also want to link the tables of colors and products with the Many-to-Many relationship and get the result in the 3rd table, but again, everything rests on the fact that I can’t clearly create a table without having the exact number of colors .
What do you propose to do?
Thanks in advance.

Answer 1

EAV
Let's dance from the 5th normal form and then optimize as necessary.

Answer 2

Question number one: do you need a selection like "all red products"? If not, then you can stupidly shove an array of colors of a particular product into one field.
If necessary, then we have three tables:
Product / ProductID, ColorID / Color
In general, "clearly" in the database is only when it is simple, in kind. And when it's more difficult, clarity is atstoy. You have to think with your head from the task.
I give you a tooth.

Answer 3

Products
List of product options
Linking table
Personally, I am currently developing an online store on Laravel, the links are trivially simple (a couple of belongsToMany for options and categories, respectively).
If you write in pure PHP / MySQL (this is of course still a pleasure), here are the resulting SQL queries: