Data Model - Regions and Countries

Question

We have divided the world map into 6 regions and each region will have list of countries.

We have products which we mark to make sure which are all countries has it.

How we model this as we have more than 200 countries and marking this country list for each of these products will create a huge number of rows in the table.

The data will be created through product API where we take an array of regions where the product is applied and then we either take included countries or excluded countries within each region parameter to identify which are all countries it is applicable. In case when both included countries and excluded countries are missing for a region, the default assumption is that all countries of that region are part of included countries for that product.

How to data model this scenario? We do not want to map products to excluded countries. We probably need to provide an API that can get us the list of products for the list of countries provided in filter criteria. Do we need a separate table to maintain a list of region and country mapping and then perform the computation on the incoming data before identifying countries for product?

Or

a better idea is to always get the list from the API itself either through included and excluded parameters.

| seqId | Region  | Country   | ProductId
|:----  |:------: | :-----:   | ----:
| 1     | America | US        | 10001
| 2     | America | Canada    | 10001
| 3     | America | Argentina | 10001
| 4     | Asia    | India     | 10001
| 5     | MEA     | Egypt     | 10001

Sample API Request for Product Creation:

{
  "productId": 10001,
  "regions": [
    {
      "name": "America",
      "includedCountries": [
        "US",
        "Canada"
      ]
    },
    {
      "name": "Middle East"
    },
    {
      "name": "Asia",
      "excludedCountries": [
        "Nepal",
        "Japan"
      ]
    }
  ]
}

score 0 · Answer 1 · answered Mar 03 '21 at 17:24

Imagine that you have this scenario :

You have a table with your 6 regions :

RegionID RegionName
1        Western Europe
2        Central and Eastern Europe
3        Asia
4        Africa
5        Mediterranean & Middle East
6        Americas

and a table containing the countries and their related regions :

CountryID RegionID  CountryName
11         1        Republic of Moldova
12         1        Romania
13         1        Russia
14         1        Serbia

11 = CountryID = RegionID + CountryID to make it lisible and understand that Republic of Moldova belongs to Western Europe if you are bad in geography.

and for the product table will be like below :

ProductID ProductName CountryID
1001      AAA         11
1001      AAA         12

Which means that the Product having 1001 as an ID is available in Republic of Moldova and Romania only, in the Western Europe region specifically.

Data Model - Regions and Countries

1 Answers1