0

I am designing a database for localization standardizations. It contains Languages, Regions, Cultures, etc. For the region, I have a hierarchical structure that contains a name and a parent which is a parent name. Name in this case is the primary key.

The parent region is the region that contains the given region. Each region has a parent, except "001 - World".

The query looks like the following

SELECT
    `Name`,
    `EnglishName`,
    `Parent`
FROM
    `locales`.`Regions`
WHERE
    `Name` in ('GU', '057', 'US', '021', '019', '009')
ORDER BY
    `Parent`

And query result looks like the following

Name EnglishName Parent
009 Oceania 001
019 Americas 001
057 Micronesia 009
021 Northern America 019
US United States 021
GU Guam 057

In the following example, the parent region for Guam is Micronesia. Geographically that makes sense. Let's say if software is licensed for Micronesia, it should be available in Guam.

However, in terms of jurisdiction, it does not make sense. If software is licensed to the US, it should operate in Guam as well.

Unfortunately, this is a base table that will be used in different scenarios so I can't refer correct meaning from the use case.

What do you think that current value is and why?

1 Answer 1

3

Unfortunately, this is a base table that will be used in different scenarios

Whoops. Somebody screwed up doing the requirements analysis - welcome to the real world, where physical geography and legal jursidiction do not overlap in neat ways.

Sounds like you need to go back to your product owner/client/etc, explain that there is an issue and you are going to need to redesign things.

2
  • How would you suggest to redesign things?
    – TIKSN
    Commented Nov 28, 2023 at 16:25
  • I can't answer that question because I don't know your business requirements. Commented Nov 28, 2023 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.