Zydeceltico
Registered User.
- Local time
- Today, 07: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
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