What database architecture is better to use in this case?

Question

Database design

Short description:
There are 1000 products. There are 500 shops. Each of the goods may be sold in each of the stores and have a price there, or may not be sold. How best to organize the storage of data about the prices of goods in all stores, so that you can quickly and efficiently build a list for comparing prices in all stores for all goods in the form of a plate
|---------| shop 1 | shop 2 | shop 3 | ... | shop N |
| item 1 | price 1 | price 2 | price 3 | ... | price N |
| item 2 | price 1 | price 2 | price 3 | ... | price N |
| item 3 | price 1 | price 2 | price 3 | ... | price N |
...
| item N | price 1 | price 2 | price 3 | ... | price N |
I implemented the storage of all data in one table in the form (product id, store id, price) with a unique index on a couple of columns (product id, store id), but now I understand that in order to build a pivot table for all products across all stores - it is necessary to execute the number of requests to the database equal to the number of goods, and this is not at all correct (as it seems to me). Spreading data over a bunch of tables (one table per store) and then gluing it all together with joins is also wrong, it seems to me.
Therefore, the question arose - how to do this correctly?
Thanks in advance for your replies.
For links to books / resources on designing the structure of databases (you can also in English) I will be very grateful.

Answer 1

You did everything right, this is the most normal form. Don't be afraid of JOINs. Databases are designed for tables to be joined.
Might be worth adding a price date. To always have a history of prices, not the last one. But that's another question.

Answer 2

3 tables
Goods
Stores
Price connection table
for a pivot table in MS SQL, for example, there is a PIVOT operator

Answer 3

You now have a suitable storage structure. If the task is to find a store with a minimum price for a product, then your structure will do just fine.
But "it was possible to quickly and efficiently build a list to compare prices" is not the same as building a table with 500 columns that is impossible to use in practice. The result for human viewing, as a rule, should be more aggregated.

Answer 4

Thanks to Alex 's answer and googling, I found a very interesting thing (for Postgres): colpivot
This function completely solved my problem.