What is the best way of dealing with multi-tier dependent picklists?

Question

I have this requirement to create picklists like so :

1. Destination Zone (~600 picklist values and is a controlling field)

2. Province (~40 picklist values, dependent on 1)

3. District (~6000 picklist values, dependent on 2)

4. City (~200 picklist values, dependent on 3)

and worst part is the business stakeholder would like to add/delete/change the picklist values every 2 weeks or so.

Considerations to take note:

I am aware that a controlling field can have up to 300 values and that if a field is both a controlling field and dependent picklist, it can’t contain more than 300 values (where 1 & 3 is the issue).

I am also aware that maximum picklist value amount is only up to 1k.

Solution workarounds

1. I heard of approaches of using VF page + Platform Cache to auto complete/suggest as they type but I feel this is way overkill?
2. LWC.. but unsure if it can handle this amount of permutations?
3. I also heard of the custom metadata type approach of linking each custom metadata type to each other but updating picklist value changes all the time doesn't seem feasible?
4. One way I thought of is using a concat of all 4 picklist fields and inputting them as records in a custom object to validate against based on what they select for all 4 picklists but limitation is that user won't be able to know they selected the correct combination.

Have anyone dealt with this before?

Answer 1

A far easier approach for you here may be to instead represent these selections via data. You can create an object for each level, and then lookups to each. You can use Lookup Filters to make sure you are only able to select children which correspond to the parent. I can say from experience that getting this approach to work with LWC will be fairly low effort if you have any dev resources available.