Approach to database to search price

Question

I am about to build a backend for a platform to search for the best prices of vendors of vegetables. In short, customer is able to filter which vegetables or fruits he is interested in, and then the backend is able to response with JSON that will return the Name of the vendor and calculated price. Here is example of database that I am considering following Vendors Table:

+----+----------+--------+--------+--------+
| ID |   Name   | Potato | Tomato | Apple  |
+----+----------+--------+--------+--------+
|  1 | Example1 |      5 |      5 |      6 |
|  2 | Example2 |        |      2 |      3 |
|  3 | Example3 |      8 |      9 |     10 |
+----+----------+--------+--------+--------+

Where values in Potato, Tomato and Apple are FK's to following tables (for example Potato Table:

+----+-------+
| ID | Price |
+----+-------+
|  5 |    10 |
|  8 |    20 |
+----+-------+

So in short, when the value in first table of FK Potato is null, it means that vendor doesn't offer this product.

My question is: is this good approach of DB structure to then easily add new products that vendors may sell, and is it fast approach - will it need a lot of time when main table will have up to 10,000 records? If not, what changes would I need to implement?

I am considering SpringBoot as my backend. For algorithm search, I am considering querydsl.

Answer 1

You have a Many-To-Many relationship between vendor and vegetable, So you need another table to break down this relation, let us name it vendor_vegetable which may contains the price for each new relation between the main 2 tables.

Each record in vendor_vegetable expose new price, You may need to add more fields to this table like the date of putting the new price

Edit:

Your design not so good nor dynamic, Consider you need to add a new kind of fruit or vegetable (for ex. banana), Then you must alter Vendors table to add new column named banana, and then add a new table named banana.

Also what if you decide to change the name of banana ?

In SQL principles your problem is a known one: Many-To-Many relationship. In this design you have only to add a new record to vegetable table, Then when you need to link the price of vendor with this new kink you have only to add new record to vendor_vegetable table.