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

This is the way I do mine...

tblStates
sStateID (PK - Text) (Is there a reason *not* to use the standard two-character postal state/territory abbreviation here instead of an autonumber? Yes, because those two numbers are the FIPS State identifier which is linked to Zip Codes.)
sName

tblCounties
cCountyID (PK - Text) I use the FIPS County ID's beacuse the same County name can be found in several States
cName
cStateID (FK - tblStates)

tblCityStateZipCode
cszCityID (PK - Autonumber bad really useless except as an identifier)
cszName
cszCountyID (FK - tblCounties)
cszZipCode

Since cszCounty will be tied to tblCounties then really no need to store State unless you want to. I then use DLookup's to populate the fields based on the selection in Combo Boxes which are also limited by which State choosen (if choosen first.)

Thank you so much for your reply Gina!!

The problem I have is that I will not have zip codes. In some cases I will not even have the city. I will always have the county and state.

The database will contain newspaper articles, historical and modern, often printed in papers that are no longer published. Each newspaper itself is printed in a location - usually a particular city (never referring to a zip code), but sometimes the location will refer only to the county and state. In the case of the newspaper table and associated details, each newspaper will only have one print location associated with it.

Further, each article entered from said newspapers will refer to one *or more* locations. Never at the zip code level, often at the city level, always at the county and state level.

For example:

Los Angeles Times
printed in Los Angeles, Los Angeles County, CA

one of the many attached articles:

Red River Woman Kills Lover
Woman described as from Frenchburg, Menifee County, KY - lover described as from Wolfe County, KY - body found in Slade, Powell County, KY

Then just remove the Zip Code field or leave it blank, It takes up no speace in the table if it is empty and you are just using the CityName field.