This is a very broad / common scenario and I’m hoping there is a best practice here.
No doubt @r937 would know this one if he’s still on these forums (I’ve been gone a while!)
I have 3 database tables: Country , State & Event - described below.
Country = list of countries worldwide,
State = list of states worldwide, foreign key CountryID
Event = list of events, which have a location (State/Country).
What is the database best practice for storing a “location” pointer in the Event table?
-
Store StateID only?
Since state has foreign key country. -
Store CountryID & StateID?
Since a Country may have no states. (Common) -
Store a 4th table “Location”
1 entry for each Country-State combination,
and at least 1 entry for Country with no state.
