How to link a customer to a country when the city/state isn't known

Question

Suppose I have database tables called Customers, Countries, States and Cities. An individual customer would have a CityID field, which links to the Cities table. Each city would have a StateID field, which links to the states table, and similar for countries.

This is all straightforward when we know the full address of the customer, but sometimes we only have their country, and sometimes only the sate. We don't always have the city.

How do we handle this? We want to be able to save the country, but can't if we don't know the city.

We could add StateID and CountryID fields to the customers table, but that smells like poor design, and could lead to inconsistent data.

Anyone any suggestions? I'm sure this is a pretty standard question, but I can't find a good answer to it.

P.S. In answer to Jaffar's comment below, the reason for doing this is that we need to do some analysis of where our customers are distributed. The client sells hugely expensive medical scanners to hospital groups, and doesn't always know which site will take the scanner when it's ordered. Therefore, we need to be able to specify as much info as we can, which may only be the country, may be the state, or may be the city.

We currently only need to do this for the US, but would prefer to provide a flexible approach in case the client wants to expand the analysis to other countries.

Answer 1

If you look at any data model pattern book, you will find that they abstract geopolitical areas.

Use table inheritance.

Country extends Geopolitical Area, and so does State/Province, County, City (though not Postal Code or Continent).

You can now point a customer at any Geopolitical Area using one column with a foreign key. If you point it at a city, you can derive the state, country. If you point it at the country, then at least you know the country.

This is also useful for tracking tax rates by county, state, country.

Answer 2

As Richard suggested, I asked on dba.stackoverflow.com, although I posed the question slightly differently. I suggested three solutions, the three-table approach (that Richard favours), a self-referencing Locations table approach (that I thought was going to be complex to query) and Juhana's approach of using three tables, but including a blank entry (which seemed to me to be the easiest).

Following the two replies there (follow the link if you want to see them in full), I tried the self-referencing Locations table approach, and found it to be much easier than I had thought. It has the most flexibility of all approaches, as it allows me to link to any level of region, including extra levels not considered yet, doesn't require multiple links from the customer table, but doesn't involve complex queries.

I don't know if it would be as easy using this approach with pure SQL data access, but as I'm using an ORM, the child locations were materialised as a collection property on the entity, making navigation really simple.

Thanks to everyone who replied. I hope this helps someone else.

Answer 3

I think your design is wrong. You should not require a CityID in order to get a state or a StatID in order to get a Country.

What if people live in a part of a state that isn't in a city? What if they live in part of the country that isn't part of a state (or in a country that doesn't have states)? What if they live in a CityState, where the city,state, and country are all the same?

[UserID]
[Address Line 1]
[Address Line 2]
[Address Line 3]
[Address Line 4]
[CityID]
[StateID]
[CountryID]
[Zip Code]

Make them all Foreign Keys.

Also, if you're trying to be as flexible as possible, requiring a City, State, and Country is definitely the opposite of that approach.

A great post on the subject lives here: Is there common street addresses database design for all addresses of the world? Also, valid combinations exist for city, country, and no state.

[Edit]
Since there are so many nay-sayers to what should be standard database design...

Address to Vatican City (No City, No State)

His Holiness Pope Francis
Apostolic Palace
Vatican City

Address to Hard Rock Cafe, Guam (No State)

Hard Rock Cafe Guam
1273 Pale San Vitores Road
Tamuning, Guam 96913

The only way to represent these two addresses is to have three, non-linked fields:

[City] [State] [Country]

This is the standard design for an address table.

Answer 4

Make one row per state in the cities table where the name of the city is empty, and one row per country where the city and state are empty. Use them when the city or state is unknown.