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?

1. Store StateID only?
Since state has foreign key country.

2. Store CountryID & StateID?
Since a Country may have no states. (Common)

3. Store a 4th table "Location"
1 entry for each Country-State combination,
and at least 1 entry for Country with no state.