How would one structure a database where you have City, State, and Country, and Cities sometimes have states and sometimes don't? Would you simply put State_ID (default NULL) and Country_ID in City or is there a better way to approach it?
Add a comment
|
3 Answers
Your approach seems spot on. If there is no enforceable hierarchy, then you're not left with much choice.
When the real world doesn't conform to our schema, then we've got no choice than to make our schema conform to the real world.
Comments
If your DBA's a stickler for really tight normalization, an alternative would be to have special "No State" records in the State table. You would need one of these for each Country (at least, each one that has "stateless" cities).
5 Comments
Ben G
Kind of weird to call the lack of a State a State, is it not?
Mac
Depends on how you look at it, I guess. I wouldn't prefer to do it, but as I said, it gets around the normalization issue of having the country_id stored in two places.
Mike Sherrill 'Cat Recall'
@babonk: No weirder than calling a province, a prefecture, or a territory a State.
Ben G
@Catcall: All of those are at least synonyms for a political entity between the country and city levels
Mike Sherrill 'Cat Recall'
@babonk: Well, country is also a political entity between the country and city levels. The United Kingdom is a country. It consists of the four countries England, Scotland, Wales, and Northern Ireland.
Going to accept the answer offered. But just want to note that one other approach not mentioned here would be a City_States many to many table. This would be an especially useful approach for some cities which are in two states.