What could be the best way to deal with addresses in terms of database structure and perfomance?
I'm creating an app for my company. In that application there are a lot of addresses because for Owner, Store, Landlord, etc needs to have address.
At first I was thinking to create separate models like you can see in the image. In the front-end I was thinking to show to the user dropdowns with "Country", "State/Province", "City". Each of this will be filtered in order to show the corresponding info, for example if the user select "USA", the second dropdown wil show up just "USA States", after select "California", the next dropdown will show up the cities just for California.
But after check a couple of sites I noticed that all sites let the user type everything but the Country. So, I want to know what do you think about it?.
We are talking around 20 country right now, with all theris states/provinces and all the cities. So for me is important to keep in mind the databases queries and performance.
Other idea that I have is create a "main model" for addresses and in that model place all necessary field like state/province, city, zip, address_1, address_2, etc. So all the other model will be able to query that specific model.
Thanks in advance.
I have quite a strong opinion when it comes to data modelling. I'll present it here but keep in mind that others may have different views - I'm just one commentator!
To me it looks both overcomplicated and unnecessary. Database query performance should be the last thing on your mind when writing business applications. Design for a good user experience instead, optimise later. All that clicking sounds like a recipe for user frustration and broken inputs. A high performing system that no-one uses or everyone dislikes is not a good outcome.
Addresses are extremely human data structures. They map badly into relational structures. Any one location can have many different representations depending on the individual and the context and the timeframe. Some countries have very unusual ways of representing addresses, so any assumptions you make now about relational data structures are likely to be wrong in future. The task of maintaining and curating a database of all the states, cities and provinces of 20 countries sounds impossible. Even if you have a full-time master data management team, I can guarantee your dataset will be incorrect from day one onward.
I suggest using an address validation service instead to normalize and pinpoint addresses. They do the extremely complex and time-consuming job of geographical master data management, so that you don't have to. If you then need to search by region, use GIS tools designed for that purpose e.g. PostGIS.
