V
V
Valeriu Vodnicear2019-05-10 13:44:13
Database design
Valeriu Vodnicear, 2019-05-10 13:44:13

What is the right way to implement the product location storage architecture?

There is a table with goods.
There is a table with addresses where each product is available.
Example

  • iPhone sold in France and Germany
  • iPad only sold in Paris
  • iMac sold only in Berlin

When adding a product, we make a request to Nominatim OSM and get the ID of France and the ID of Germany, and save them in the database for the iPhone. (and so for all products)
5cd54f5381b9d098888174.png
I think one of these IDs should be kept, but for now I don’t know how they differ.
# Example 1 A
buyer is looking for an iPhone in Paris (we get an ID from Nominatim).
Problem: how to understand that the ID of Paris is a child of the ID of France?
# Example 2 A
customer is looking for an iMac in Germany (get ID from Nominatim).
Problem: how to understand that ID Germanium is the parent ID of Berlin?
# Example 3 A
customer is looking for an iPad in France (get ID from Nominatim).
Problem: how to understand that the ID of France is the parent ID of Paris?
And there are many such examples.
I think somehow you need to store the relationship between the IDs of countries, cities and towns.
Only the problem arises that the data in OSM changes, and over time, garbage accumulates in the database.
Or another solution: get a polygon from the boundaries of each feature on the map.
And then, through PostGis, look for the occurrence of the polygon in other polygons.
Only here again is the problem, because the borders of one country have several thousand coordinates, and I think it will be difficult for PostGis to cope with such tasks.
Goods will be in many countries, cities and towns (possibly).
It is necessary to use OSM because you have already tried to keep a list of all cities and countries, and it is not very convenient. The data needs to be constantly updated. In general, this solution is not suitable.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question