Table Design Multiple same name cities in state (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Hi All -

I'm curious how others have solved table and relationship designs for a situation similar to trying to record addresses across country, state, and city - - -given that some states might have two cities/towns named Jacksonville or something similar.

If that were not the case and not possible, table design and PK/FK relationships would be simple: tblCountry, tblState, and tblCity with simple realtionships between the three.

I know that in this example adding the zipcode sort of solves for that but it also potentially adds a new level of complexity depending upon the usage requirements and ultimate goals of the DB. But let's go ahead and say that we add the field zipcode. Let's also go one step further towards the absurd and say that within a State there can be two different locations with exactly the same value for Zip.

My point is that four fields - not one - or even two - are required. I know some will immediately point to Normalization. I truly don't believe that is the case. I have worked through this many times on paper and lived with our real-world process for well over a year and the example I am trying to explain is best for our needs.

(Quick aside: I have junction tables and composite keys and how to utilize them on my inexperienced-with-Access mind).

So - let's say that I have created a DB in which I want to record the amount of rainfall; days of sunshine; heat index; etc. in towns across the country knowing that at some later date I am going to want to (as absurd as it sounds in this example) run a report showing annual rainfall in all towns across the country named "Jacksonville?" How would I setup my tables and relationships to accomplish that?

Theoretically I already have tblCOuntry, tblState, tblCity, tblZip, tblJunuctionTable, and tblVariousRecordedValues where data values are recorded using the ID PK value from tblJunctionTable as a unique identifier for data collection.

What are some potential considerations for designing data entry forms to gather that data in the first place


I know that I can create a junction table to accommodate the many-to-many relationships. I also know that that the junction table would have a unique autonumber field as a primary key. In other words, the junction table would have fields like: ID (PK), Country_FK, State_FK, City_FK, and Zip_FK (or maybe the zip goes in the junction table instead of there being a tblZip).

Regardless, at a later date on a data entry form, I am going to want to have three cascading combo boxes: cboCountry; cboState, and cboCity which as I said filter in descending order. Finally - when a city is selected in cboCity I would like to have a list of potential data points (e.g., annual rainfall, days of sunshine, etc) "appear" on a form with a control available for each to "data enter" a value to be recorded in another standalone table called tblVariousRecordedValues. Maybe this final step "appears" after update as a continuous sheet with yes/no check boxes. Maybe a popup form "appears" with all potential data collection points represented in bound or unbound controls. I don't know.

My main question has more to do with how do I - in general - approach data entry in a form when I need to associate any given data entry with the aggregate of Country/State/City/Zip? Especially remembering that many states have two or more cities/towns within the state that have the same name.

A different way of asking: My form has 4 unbound controls: one each for Country, State, City, Zip. Once all of those selections are made via my unbound cascading combo boxes, how do I retrieve the ID for the record in my junction table that represents the aggregate of those four values; "grab" the value of the PK ID for that record and create a new record in tblVariousRecordedValues that uses the acquired value as an FK identifier for more data entry in the new record?

Like I said, I know my example is weird but it is actually very parallel to our real-world scenario in the shop - at least in process.

Looking forward to you thoughts and insights.

Thanks,

Tim
 

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
tblCountry, tblState, and tblCity with simple realtionships between the three.

You're over-normalizing things, you don't need those tables to relate.

Presumably, in whatever table you are storing your actual data, you would have a CityID field which is a foreign key to tblCity.CityID. Instead, in that table you should just have text fields and input the City name and State Name/code.

A case could be made for tblState to relate to tblCountry, but have a table for cities and relating it to tblStatere is just over kill and not effecient. If/When you want all Springfield totals you would simply do an aggregate query from your data table and GROUP BY CityName and State.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
You're over-normalizing things, you don't need those tables to relate.

The only thing is that I really do need to have State filter on Country; City to filter on State, and Zip to filter on City because each combination/aggregate of those four values has a different end set of data collection variables to be recorded. Some data entry fields are the same in many aggregate combinations of the Main Four (Country, State, City, Zip) but it is also true that each unique aggregate of the Main Four will have some fields that are unique to that aggegate ID. That is why I am relating all four together. I have another junction lookup table that lists all possible data collection items related to every combination of Country, State, City, Zip. The idea is that after I have selected the Main Four, I will be presented with a list/datasheet of all data collection fields associated with the ID of the aggegate Main Four from the junction table relating them all to each other. The reason is that I have a couple hundred data entry fields and but any given "Zip" only has a handful associated with the Zip.

I suppose if I knew how to collect the value of the ID for the Main Four and then somehow lookup the record in my "junction" table I could retrieve the ID for the aggegate. But - I of course - am unclear how to go about that.

If/When you want all Springfield totals you would simply do an aggregate query from your data table and GROUP BY CityName and State.

Does this still apply given what I explained above?

I am very, very "good" at over-complicating things. :)

Thanks for you patience.
 
Last edited:

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
Some data entry fields are the same in many aggregate combinations of the Main Four (Country, State, City, Zip) but it is also true that each unique aggregate of the Main Four will have some fields that are unique to that aggegate ID.

I can't see your idea through the forest of jargon you've used. Perhaps you can dumb that down or demonstrate your issue via sample data.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
I can't see your idea through the forest of jargon you've used. Perhaps you can dumb that down or demonstrate your issue via sample data.

Believe it or not while typing my repsonse to your post I got an idea that might work. I shall report back later - sans "forest of jargon." :)
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Jan 23, 2006
Messages
15,364
I agree with plog --sample data with clear description of usage and expected result would help with context. Also, writing to communicate with others can often lead to further analysis and thought.
Good luck Tim.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
I agree with plog --sample data with clear description of usage and expected result would help with context. Also, writing to communicate with others can often lead to further analysis and thought.
Good luck Tim.

So - for the sake of conversation - I've attached my current iteration.

I hope you'll find my organization of the objects helpful and informative and not confusing.

I'm also attaching a snapshot of the Relationships window the way I look at it b/c I have noticed that sometimes when compressing these db files the saved layout goes out-the-window.

In a nutshell:
1) tblInspectionEvent is where I record general data about the inspection;
2) tblInspectionEventRecordedValues is where I - - -- record the inspection data values
3) tblInspectionItemsJunction is where I combine the FKs from tblInspectionTypes, tblSubInspectionTypes, and tblItems.

I would first look at the pic of the Relationships window.
Then I would look at tblItems and open any subdatasheet of any record. Record number 9 ("parts fit") is particularly representative as you will see given the number of Inspection/SubInspection combinations it can appear in. If you then click THAT windows subdatasheet button you will see how this all relates to tblInspectionEventRecordedValues.

NOTE: in tblItems I have a field called "Datatype" and a lot of items are currently marked as "text" but say y/n in the Notes field. These are just placeholders at the moment. I strongly suspect that everything is going to end up as a text string with a form-based value list but that's not important right now.

I'm super curious what insights and feedback you have given how I want/need to collect data.

I've attached a pic mocking up a form (frmItemsToInspectMOCKUP) but I'm afraid that it may be a bit misleading but maybe not.

I also attached a pic (InspectionItemsValuesEnteredInTable) of what tblInspectionEventRecordedValues would look like if the data in the pic of frmItemToInspectMockup were actually entered.

I just don't know how to functionally get from "here" to "there" - specifically how to get the value of InspectionItemsJunction_FK entered into tblInspectionEventRecordedValues given the cascading combo boxes idea on frmItemsToInspectMOCKUP? I don't know.

Does this make it any clearer at all?

tblInspectionItemsJunction should hopefully show how I have combined InspectionTypes, SubInspectionTypes, and Items.

Thanks,

Tim
 

Attachments

  • QCDB-EAV-mockup.zip
    99.6 KB · Views: 136
  • EAVTableRelationships20190823.JPG
    EAVTableRelationships20190823.JPG
    79.6 KB · Views: 133
  • frmItemsToInspectMOCKUP.JPG
    frmItemsToInspectMOCKUP.JPG
    75.2 KB · Views: 121
  • InspectionItemsValuesEnteredInTable.JPG
    InspectionItemsValuesEnteredInTable.JPG
    42.7 KB · Views: 118

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
Your drowning me. 709 words in your first post, 256 words in your second post, 338 words in your latest post. Worse, this is the first sentence of your first post:

I'm curious how others have solved table and relationship designs for a situation similar to trying to record addresses across country, state, and city...

No where in the relationship screenshot you provided are any tables or fields dealing with City/State/Country.

I'm bowing out.
 

mike60smart

Registered User.
Local time
Today, 17:42
Joined
Aug 6, 2017
Messages
1,899
Hi Tim

I think you need Cascading Combobox's for Inspection Types and SubInspectionTypes.

Would you want to be able to select an Inspection Type and then only see those Sub Inspection Types associated with the Inspection Type selected??
 

mike60smart

Registered User.
Local time
Today, 17:42
Joined
Aug 6, 2017
Messages
1,899
Hi Tim

I also don't think you need tblInspectionEventRecordedValues
 

mike60smart

Registered User.
Local time
Today, 17:42
Joined
Aug 6, 2017
Messages
1,899
Hi Tim

I believe your process would be:-

1. Select a Job
2. Record Date of Inspection
3. Select Type of Inspection
4. Select SubInspection Value
5. Enter Inspected Value
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Your drowning me. 709 words in your first post, 256 words in your second post, 338 words in your latest post. Worse, this is the first sentence of your first post:



No where in the relationship screenshot you provided are any tables or fields dealing with City/State/Country.

I'm bowing out.

Uhm……howabout……OK.

Lol………first time I was called out for being verbose was 7th grade. I still don’t care.

If I gave you 30 words in my first post you would ask for more explanation. But I gave you AN EXAMPLE - an analogy - - a parallel - ach du lieber.

And you plug have done this holier-than-thou crap many times before.

I’m actually grateful that there is someone more intelligent than me that cares so much to say “you’re an idiot for trying and you are beneath my intellect” because then I know where we both stand.

Good on you for being you.

Thanks,

Tim

Ps. Kick me off. Fine.
 

Micron

AWF VIP
Local time
Today, 13:42
Joined
Oct 20, 2018
Messages
3,476
tblCountry, tblState, and tblCity with simple realtionships between the three.
If I get the idea on what these are for, then I see no need to relate them at all. They appear to be lookup tables to feed combos and such tables seldom need to be related. That's just my opinion. tblCountry is likely a simple and unique index since AFAIK, there are not 2 countries with the same name. In tblState, tblCountryPK and State value could be a composite index since again, it is unlikely that any one country has 2 states with the same name. I don't know if it is possible that there are 2 cities in the same State with the same name but I suspect not, so same for that table (composite on State and City). A Unique values query should be able to return only single instances of a State (same for City) for cascading combos, based on the parent value. That is, based on a single State value, show me all cities in that state. If there are 2 Springfields in a State, then the City combo can list each along with that State. Something tells me this situation doesn't exist though, so it ought to be simpler than just described. My main point is I'm not seeing a need for setting up relationships on lookup tables, assuming that's what they are.

P.S. it's getting late here so I confess to not having looked at any attachment you might have posted. I just wanted to throw that out there in case it helps at all.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Feb 19, 2002
Messages
42,971
Although zip codes can cross state lines, cities can't. Zip codes are used to make mail delivery more efficient but cities are political entities. There are cases where there are metro areas that cross state lines but they have different political leadership whether or not they have the same name. Kansas City comes to mind.

Anyway, because of the anomalies with zip code, you need to keep city/state (either separately or as one ID) as well as zip code if you want to be able to report by city or state.

I keep a state table, a city table with a StateID as a foreign key but I've never had a reason to keep a zip code table. Typically, you would probably need some kind of mass mailing application to keep a zipcode table and justify the time to keep it updated. My zip code changed at least 10 years ago and I still occasionally run into issues with web sites that complain but most of them are smart enough to let me override.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 28, 2001
Messages
26,999
Zydeceltico:

The problem you might have with same state, duplicate city names SHOULD be resolved by including ZIP code because I'm pretty sure you won't have the same city name twice in the same ZIP code. (Or postal code if applicable.) You can index on city name but it can't be a prime key. Couldn't have been a PK anyway for structural reasons - because city-name duplicates exist in different states - vis-a-vis Springfield as the worst of the cases.

Try not to let plog get to you. He (and others) sometimes comment about my verbosity as well, but the forum is a bunch of good people anyway.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Feb 19, 2002
Messages
42,971
You also cannot have the same city name twice in the same state. So although I would use an autonumber as the ID for the city table, I would have a unique index on StateID + CityName to prevent duplicates.
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 10:42
Joined
Aug 22, 2012
Messages
205
Not to sound like a know it all a$$, but I (quickly) did some research and there are a few examples of same city names in a single state. Seems that Springfield, PA exists in two separate counties.

Lots of other examples that are very similar, but not exact.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 28, 2001
Messages
26,999
Yes, unfortunately that city duplication happens now and then. Could be worse, though. You could have to try to spell some of the Louisiana town and city names that are French spellings of Choctaw names.

My comment about ZIP codes also has an issue since if the city is large enough, it might cross into multiple ZIP code areas. So you would have to list only the code for that city's Town Hall or City Hall or Municipal Office building or whatever they call it. Otherwise you would have New Orleans with not less than a dozen different codes at once.
 

Micron

AWF VIP
Local time
Today, 13:42
Joined
Oct 20, 2018
Messages
3,476
Seems that Springfield, PA exists in two separate counties.
Out of curiosity I checked but cannot find a Springfield PA that is not in Delaware county. Do you have any links that expose States with duplicate city names?
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 10:42
Joined
Aug 22, 2012
Messages
205
Sorry, but no links handy. But I believe it was Delaware county and Montgomery county. One was directly west of Philadelphia, the other was north.

Another (possible) is Red Bank, AL. But I have NOT researched that example.
 

Users who are viewing this thread

Top Bottom