How to design a database such as monster.com or jobstreet.com, each domain specific for one country and has its own regional data (e.g. jobstreet.com.sg showing Singapore jobs), but user details are shared between the domains (e.g. registered account in jobstreet.com.sg can login into jobstreet.com.my).
What I want to achieve:
- User details are shared between different website (User doesn’t need to register in different region website)
- Regional data (SG data only showing in SG)
- Cross multi region data (SG data can show in MY if the user specific it)
Solution 1:
- One database for all the regions and specific where the data should show. E.g.
- Job (id)
- job_region (id, job_id, region)
- In application, filter job where region in (SG)
Problems for solution 1:
- If the application go popular, then will need to filter more data and performance might be affected
- Require large amount storage for every database instances in each availability zones
Solution 2:
- One schema for user profile related data. E.g.
- database_user
- User_profile (id, name, email)
- Working History (id, user_profile_id, position)
- database_user
- And one schema for each region
- database_sg
- job
- database_my
- job
- database_sg
Problems for solution 2:
- If the job ad is posted in both SG and MY, then it will need duplicate the data and insert into two different schema
- Extra configuration to change database connection to retrieve data
Comparing solution 1 and 2, seems like solution 2 is much more acceptable but is there any others solution for these kind of scenarios?