Need normalization and design advice on the following (Thanx a bunch in advance!)
tblArticles will refer to place(s) ~ (places noted in the article - *may have MULTIPLE values*)
tblNewspapers will refer to place(s) ~ (place published)
tblPeople will refer to place(s) ~ (places of birth, death, etc.)
In some cases, the "place(s)" referred to in tblArticles will be states, sometimes counties/states, sometimes cities/counties/states. Further, some articles in tblArticles will refer to more than one "place."
What is the best way to set up and normalize these relationships and maintain referential integrity? I've found a plethora of templates/instructions based on zip codes, but if I'm running across anything that should clarify this matter for me, my mind is over-complicating to the point of oblivion (which often is the case) and I'm not recognizing it.
It should also be noted that for the purposes of this database, each county can only belong to one state, but each city can belong to *more than one* county (there are cities like this).
I thought of maybe creating a Locations table that separately identified each state, state/county, and state/county/city combination as a separate "Location" identified by autonumbered LocationID primary key. Should I do this? If I do, is this sufficient:
tblStates
StateID (PK) (Is there a reason *not* to use the standard two-character postal state/territory abbreviation here instead of an autonumber?)
StateName
tblCounties
CountyID (PK - Auto)
CountyName
StateID (FK - tblStates)
tblLocations
LocationID (PK - Auto)
CityTownName (Which would be left blank for county/state and state locations)
CountyID (FK - tblCounties, left blank if location refers only to a state)
StateID (FK - tblStates, would need to be disabled if a CountyID is chosen - CountyID already has a relationship with the StateID through tblCounties)
Many thanks in advance for any guidance with this.
Duv