2

My diagram

I want to record addresses in my database. The layout I have right now is visible in the diagram below:

enter image description here

Considerations

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.

Questions

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?

  • Not every country has the same division of territory. Create subdivision record for each country nevertheless (for countries which have no them it can be NULL, empty string or some predefined literal like 'no subdivision'). This allows you to avoid FKs triangle. Entering the address for the countries without a subdivisioning may have special handler for skipping subdivision (hide or disable its field). – Akina Oct 1 at 12:56
  • Not every address has a city either, in some rural areas of North America you might have an address like "Rural Route #2, Some County, Province/State, Country. Addresses are really hard mjt.me.uk/posts/falsehoods-programmers-believe-about-addresses – kevinsky Oct 1 at 14:06
  • Why do you need to normalize addresses? Can you simply store them as free text (and use something like Google Maps API to canonicalize them if truly needed)? – mustaccio Oct 1 at 15:46
  • @Akina, what do you mean with a FK triangle? I'd like to look this up but google isn't helping that much. Ik can indeed foresee a Subdivision table between Country and City but I need to be able to have a variable amount of Subdivisions between Country and City. Having 1 Subdivision with NULL won't do the job in this case right? – Krowi Oct 2 at 9:42
  • what do you mean with a FK triangle? I mean 3 tables (country, subdivision, city) joined with 3 FKs, each pair have FK link. Altogether they forms a triangle (do not pay attention to the directions). – Akina Oct 2 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.