Address (street, city, country) + Entity --> is there a way to improve my scheme?

by Kommi   Last Updated September 13, 2017 13:05 PM

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.

Your ideas?

P.S.

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.



Related Questions




Enforcing constraints across databases

Updated August 17, 2017 16:05 PM