Results 1 to 3 of 3
  1. #1
    DorkyDuvessa is offline Novice
    Windows Vista Access 2003
    Join Date
    May 2011
    Posts
    2

    Normalizing various City/County/State combinations (w/out zip)

    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

  2. #2
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,446
    Here is a db that I got from someone else that may help you to get started.

    Alan

  3. #3
    DorkyDuvessa is offline Novice
    Windows Vista Access 2003
    Join Date
    May 2011
    Posts
    2
    Thank ya kindly, Alan, for taking the time to respond. But...and I hope I don't sound rude or ungrateful...I'm not sure how the example you provided addresses my issue (nor do I think its a good example of a normalized database). As I said, all I've been able to find are examples where relationships are based on zip codes for each associated record. My data does not include zip codes *and* can vary in "format" - i.e. sometimes the location entered will be at the county level (i.e. Jefferson County, Kentucky), sometimes at the city level (i.e. Louisville, Jefferson County, Kentucky), and sometimes even at just the state level (i.e. Kentucky).

    Again, thank you for your time =) I can be spastic and long-winded, but I make a conscious effort not to be =) I'll try to be more clear about my needs in the future.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. City, State Zip lookup
    By garywmcp in forum Access
    Replies: 1
    Last Post: 04-24-2011, 06:15 PM
  2. Normalizing a Database
    By tehbrosta in forum Database Design
    Replies: 4
    Last Post: 12-05-2010, 03:12 PM
  3. Assistance with normalizing tables
    By racheliza79 in forum Database Design
    Replies: 3
    Last Post: 08-26-2010, 04:20 PM
  4. Normalizing a table
    By racheliza79 in forum Database Design
    Replies: 3
    Last Post: 08-20-2010, 08:40 PM
  5. Replies: 1
    Last Post: 03-15-2010, 02:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums