Tables Hierarchy in a database
I am not sure if this is the right forum to ask this question but am assuming it is a technical question so posting here.
It was asked during an interview that;
We have 4 tables.. Country, City, District, Sub District. How to make hierarchy of them in database ( One way is adding foreign reference of parent in child table... but what's the other way?)
I am not able to find any other way but to add all the information in one table but the question was 'how to make hierarchy of them' ? which means multiple tables are involved.
Could anyone please suggest what could be the other way?
Tgere are a lot of approaches. Some of them good from the normalisation point of view, some from performance and easy to use point of view. Some of them:
- Self reference:
- Code holds information. for example 123000 - is a district, 123001 - 001 sub district of district 123
- The most normalised way (link district-city can be changed to subdistrict-city)