DB: Change region and province format

[levogirar]

Currently we are using the code for each country. This could cause problem when expanding to different countries. I propose using the ISO3166 for the region.

ISO 3166 for Spain

• Use REGIONID for location filter
• Use PROVINCEID for display in the company card and profile page
• Use Postal code to assign the right province

👉🏼 GOOGLE SHEET

[levogirar]

Problem

Currently, we do not know the region of the companies in our database. For now, we assign them randomly. We need to change that if we want our website to be useful.

Solution

I want to use this new standard as ID for regions and provinces in Spain.

How it works?

We are able to detect the postal code from the website of some companies. The first two digits of the postal code in Spain determine the province of the company. From these postal code, I want to assign the correct province to the companies and use the region to filter the provinces.

Flow

1. The scraper detect the postal code and store it.
2. Look at the first two digits, determine the province, and assign PROVINCEID to the company. 2.1 If no postal code is detected, assign 'ES' to the company.
3. In the homepage, use REGIONID as filter. When user click on a filter, only show companies with PROVINCEID inside the REGION.

What we need?

1. A field in the company profile page to add or modify the postal code in admin mode (once #121 is completed).
2. A new location table for the database following ISO 3166 standard (see above for more details).
3. A connection between REGIONID, PROVINCEID, and POSTAL CODE.
4. In the company card and company profile page, display the province names (currently we display the region).
5. In the region filter section, display the region names
6. In the table, we have ID and display name. Show the display name for each language following the order English, Spanish, French, and Chinese. For example: if the website is in Spanish, show "Madrid, Comunidad de" but if the website is in French, show "Communauté de Madrid".
7. Create a new TSV in the 'default data' so they will all have access to our new implementation. (path /Models/DataAccess/default data).
8. Right now, we are not storing the postal code anywhere. We need a new variable in CompanyData.php for postal code and change the variable REGION for PROVINCEID.

Q&A

1. What happens when we did not detect a postal code? We show the company with the ID 'ES' for Spain. This will allow us to manually write the postal code later.
2. Will companies with 'ES' show on the homepage? Yes, under 'Show all region' option. If the user select a region, then we will only show companies in that region.
3. Some names are not translated in Chinese, let's use the name as it is shown right now. For small provinces, there are no translation.

N.B: this is not exhaustive. I might be missing something, but the general idea should be here.

[kakaye-mkubwa]

At the moment, is the scrapper able to detect the postal code from the sites, or this is part of what should be done?

[levogirar]

The scraper is able to detect the postal code already. In the Models/Scraper/WebsiteCrawler.php, there is a private variable called $postalCode.