Table Setup--Normalize

[lmcc007]

I am looking at the example database from Access 2007 Inside Out. When you enter a zip code it will enter the city and state. There are several modules in this database, but, of course, I do not understand any of it. Sorry, but I couldn't upload the db--the zip file is still too big.

I am trying to understand the setup. I see on the example that tlkpStates and tlkpZips are not joined—no relationship set up. NY and PR in the state field are repeated many times.

tlkpStates
StateCode Text 2-character unique state code PrimaryKey
StateName Text State Name



tlkpZips
Zipcode Text Primary Key
City Text Primary Key
State Text
County Text
A/C Text
Type Text


My question is: is this setup normalized?

 

[David Eagar]

I guess it depends on the purpose of the database -what is the scope of the data.

I had a go at something similar and what you have will quickly fail if you need to take more than 1 country into account. I ended up with:
1 Country table, linked to Many State table, linked to Many Towns table with the zipcode fieldin the Town table. More than 1 town can have the same zipcode.

With this setup, you can still search by zipcode and retrieve Town, State and if needed Country

 

[lmcc007]

I guess it depends on the purpose of the database -what is the scope of the data.

I had a go at something similar and what you have will quickly fail if you need to take more than 1 country into account. I ended up with:
1 Country table, linked to Many State table, linked to Many Towns table with the zipcode fieldin the Town table. More than 1 town can have the same zipcode.

With this setup, you can still search by zipcode and retrieve Town, State and if needed Country

It has a Country table in there also, but none of them are linked together in the Relationship windows. That what I am confused about because City, State, Zip, and Country will be repeated over and over again.

Is it because the code in the modules instruct it to enter the city and state when you enter a zip code?

 

[David Eagar]

Is your purpose to build a databse or learn how modules work? If it's modules, I'll butt out as you wont learn anything from me!

 

[lmcc007]

Is your purpose to build a databse or learn how modules work? If it's modules, I'll butt out as you wont learn anything from me!

The purpose is to build a database correctly. A lot of the teachings are confusing and the examples are hard to follow.

I decided not to follow the ZipCode form example because it has too much code and I can't figure it out. Basically, the zip code table is used to lookup a zip code’s corresponding City and State. You have to create a form. On the form create a zip code control. In the zip code control’s AfterUpdate event, if it’s a US address, invoke the GetCityState method to retrieve the zip code’s primary City and State values.

Since most of my data is from the same city and state, and the zip codes are repeated. I decided to continue designing the database as I was. Meaning, tlkpCities, tlkpStates, tlkpCountries, and tlkpZipCodes. In tblAddresses I added CityID, StatesID, CountryID, and ZipCodeID; each linking to its table with a one-to-many relationship.

I hope I am on the right track.

 

[David Eagar]

I think you've got too many tables, but it's your choice. You might be able to save yourself a lot of data entry by downloading a US zip codes list - there are several freely available

 

[lmcc007]

I think you've got too many tables, but it's your choice. You might be able to save yourself a lot of data entry by downloading a US zip codes list - there are several freely available

Okay,

I have a zip code list. I am confused on how to set it up. How shall I set it up correctly?

 

[David Eagar]

Can you attach the zip code list you are using?

 

[lmcc007]

Can you attach the zip code list you are using?

The zipcode db is too large to upload, so I copied to another db and delete half the records. But this shows how the table is set up.

 

[David Eagar]

if you zip up the zipcodes file, will it attach?

 

[lmcc007]

if you zip up the zipcodes file, will it attach?

it's too huge

 

[David Eagar]

Not to worry - If it were me, I would set it up as attached

 

[lmcc007]

Not to worry - If it were me, I would set it up as attached

Okay, I added my tblAddresses table so you can see how it is set up.

I am getting confused.

Should I setup State and Zipcode fields to lookup their values from States and ZipCode tables?

 

[David Eagar]

One of the things I learned from the experts at this forum is NOT to create combo boxes in tables - it apparently increases the risk of database crashes as they get bigger and more complex. Let me have a play & I'll attach when finished

 

[lmcc007]

One of the things I learned from the experts at this forum is NOT to create combo boxes in tables - it apparently increases the risk of database crashes as they get bigger and more complex. Let me have a play & I'll attach when finished

Gotcha!

I am confused because you say I have too many tables--that is, tblCities, tblStates, tblZipCodes, tblCountries.

I am trying to normalize the data, not have repeated rows of text.

 

[David Eagar]

My mistake, now I have seen the db, I can see that you are on the right track

 

[David Eagar]

I'm now remembering why this is such mongrel of a project - something that should seem easy ends being quite complex. Unfortunately I am away this week, so won't be able to spend any time on this - hopefully others can help you along. I hope I haven't wasted your time, will get back to it next week if not resolved by then

 

[lmcc007]

My mistake, now I have seen the db, I can see that you are on the right track

Meaning, with the tblCities, tblStates, tblZipCodes, and tblCountries?

 

[lmcc007]

I'm now remembering why this is such mongrel of a project - something that should seem easy ends being quite complex. Unfortunately I am away this week, so won't be able to spend any time on this - hopefully others can help you along. I hope I haven't wasted your time, will get back to it next week if not resolved by then

No problem. I got off track trying to follow the examples in the books.

Anyway, I went to commercial database to see how it's set up. Like, the voter registration database. It has the following fields:

Street Number
Pre-direction
Street Name
Street Type
Post-direction
City
Zip
Precinct

And, the other databases I looked at all have separate fields for street number, street name, and so on.

After looking at them and going back to my database I see why. When I sorted my database with the Address being one field, the sorting was off. Meaning it sorted: 1, 10, 100, 10000, 1001..., which makes it harder to find a address when sorting.