What is the best way to link online store products to cities?

Question

Data Structures

There is a database of goods divided into categories. It is necessary to bind each product to a specific city, depending on which city the user has chosen, to display the necessary products.
The catch is that depending on the city, the cost of delivery is calculated. Also, in addition to the city, there may be a settlement within the city, which cannot be registered in the database in any way, because there may be N number, or may not be at all. The same product may have a different cost, depending on the city. A person who has chosen a city may not be directly related to it, but be located 2-3 kilometers from the city.
Essence of the question:
What is the best way to record and select goods? Create a product for each city and store it separately, or link the product to several cities and check if there is a city id in the binding field when selecting? Or are there other options that I haven't seen?
PS The engine is self-written and finding some ready-made solutions is not an option.

Answer 1

Judging by the description of the task, 2 logical entities can be distinguished here:
* goods - a set of properties that describe a specific product
* city
* product offer - this is essentially a connection between a locality and a product, in which, in addition to links to the first two objects, there is also a delivery cost. You can also add an additional field here - a locality, which will reveal a specific area, place within the city, for which this delivery price is valid.
Well, that's all.
When extracting, join products into product offers, and filter by City ID.
Or take this information out in a denormalized form into a commodity, but then separate it separately for its consistency

Answer 2

If there is an unambiguous connection between the goods and the place of their receipt (city, settlement, etc.), then it can be fixed, as Igor Yatsevich suggested - but this is only 1 part of the task. You can fix not the city but the region, which includes certain cities. Territorial division. And the second, as I see it, is the connection between this region (city) and the client’s place of residence (Sometimes situations arise when the final destination is almost equidistant from two large cities where there is a conditional pick-up point, and the user must decide for himself where it is more convenient for him to receive the goods) - on At this stage, calculate the second part of the shipping cost (if necessary).
Creating a separate product is a very strange decision, I would not recommend it.