Having an abstract task at this point of time:
We have a list of products for country A and a list of products for country B, they sometimes are the same, sometimes products from country A are different to what is offered in country B.
So my current task is to create and elaborate on multiple solutions for database design. Definitely to make it scalable for 17,000 - 1,000,000 products in the future.
What should be taken into consideration when developing the products database?
Few thoughts are:
=> A product table should be specific for each market, for example AUSTRALIAN_PRODUCTS, US_PRODUCTS, where id of a product is a unique identifier
=> To have an multiple schemas for each market
=> To have multiple database instances for each market
When you say "multiple schemas" or "multiple DB instances", I think you mean "unique per country". I would avoid that as very complex long term. Assuming that the same product_id might occur for different products in two different countries, I think you will find that keeping to a single DB with a combined, unique key of country_code, product_id would be most flexible in the long run.