Optimal database structure. How to organize correctly?

Question

PHP

Friends. Now the data of each user is written to the database in the standard way:
id info skype adress - and so on.
The site is narrowly thematic, so it is necessary that each user can fill out his own price list. Rather, simply indicate the prices for the list proposed in advance
. Here is the question. I suppose you need to create a new table like:
id (user) item_name1 (product name) item_price1 (price) item_name2 (product name) item_price2 (price)
The difficulty is that there are more than 200 elements (item_name). You will have to inflate the table or you can make it more optimal ?
Thank you.

Answer 1

There is no such thing as an optimal data structure at all. In this case, you can, for example, create two additional. tables:

item_name (id, name);
item_user_price(id_user, id_item, price);

The first will have "more than 200" entries (which, by the way, can be deleted / added over time), the second - as many as prices will be entered by users (I doubt that users will be happy to enter all 200 prices at a time; or maybe someone In general, there are not all prices).
Such a structure will give minimal redundancy. But whether it will be "optimal" in terms of speed and memory consumption depends on the specific requests that will be executed on it.

Answer 2

Separate table of users.
Separate table of products.
A third table that combines user id, product id, plus price.