I want to record addresses in my database. The layout I have right now is visible in the diagram below:
Continent, Country, Subdivision and City will be pre-filled, Address will be filled in by the user that will allow him four lines to fill in what he wants.
The question is not about the four lines in the Address table but rather the Subdivision table. Not every country has the same division of territory. US has states, Belgium has regions and provinces (two layers) and other might have more or less. Therefore I've created the Subdivision table which has a foreign key (FK) to itself and a FK to the Country.
Let's take a fictional country, it has five regions and six provinces:
- Country: X
- Region: Y
- Province: Z
This would give me a row in the Country table and two rows in the Subdivision table. Subdivision Y would have FK Country filled in and referencing to X. Subdivision Z would have FK Subdivision filled in and referencing to Y.
I have some questions about the design I came up with because I'm not sure if this is a good approach. It would allow me to get everything higher and lower in the tree but having two FK's in the Subdivision table where only one is used at a time looks to me as a bad design.
The other thing about this is that querying everything might be hard to do. Am I correct?
Is there a better approach to not necessarily recording addresses but rather to variable layers of data?