I have an app where I can some entities attached to an address. An address can be any address in any country. At the moment I have this scheme, simplified:
Entities: id title address_id Addresses: id street_id address_txt Streets: id name city_id Cities: id name country_id Countries: id name
In order to get a full address in Entity, I have to do 4 joins. It's too much.
I wonder, is there a way to simplify and improve this? So that there's still data correctness but fewer joins. Or whatever improvement is possible.
By data correctness I mean that now there's no way to create an address with, say, city New York in Malaysia.
However, if I go with this scheme:
Entities: id title address_id Addresses: id address_txt street_id city_id country_id
this won't prevent me from such a mistake. Namely I might create an Entity in country A and city B which isn't located in country A. The same goes for streets in a city.
Only major countries, streets and cities are in this application, not all ones that exist on Earth. But potentially any street, country, city can be added, with moderation.