creating a address database

Answer 1

Looking at the diagram you have, the state table is the only one of the 4 outside tables that is really necessary. Lookup tables with just an ID and a single value aren't worth the effort. These relationships are designed to make a single value in the main table (ziptocities) refer to a set of related data in the lookup table (states).

Answer 2

I don't know the US ZIPcode and territorial devision system well, but I assume it's somewhat like the German one.

• A state has many counties.
• A county has many cities.
• A city has many zip codes.

Hence I would use the following schema.

ZipCodes          CityZipCodes
------------      ----------------      Cities
ZipCode (PK) <─── ZipCode (PK)(FK)      -----------
                  City    (PK)(FK) ───> CityId (PK)
                                        Name
                                        County (FK) ───┐
                                                       │
                                                       │
                                     Counties          │
                                     -------------     │
              States                 CountyId (PK) <───┘
              -----------------      Name               
              StateId      (PK) <─── State    (FK)
              Name
              Abbreviation

Fixed for multiple cities per ZIP code.

Answer 3

One thing you should be aware of is that not all cities are in counties. In Virginia you are in either a city or county but never both.

Answer 4

You'll need to ask yourself why you care about counties. In many states in the US, they have little importance beyond tradition and maps.

The other question will be how important will it be that the address be accurate? How many deaths will there be if important letters are not delivered in a timely manner (possibly many if the letter is about prescription drug recalls!)

You probably want to think about using data from the Postal Service, possibly using a product that corrects addresses. That way, when you get a good address, you'll be certain the mail can be delivered there - because the Postal Service will have said so!

Answer 5

There seem to be flaws in both your process and your logic.

I suggest that you stop thinking about tables and relationships for a moment. Instead, think about facts. Make a list of valid addresses that your database needs to support. Many surprises await you.

Don't confuse an address with a mailing label. They're not at all the same thing. Consider modeling carriers, too. In the US, whether an address is valid depends on the carrier. For example, my PO box is a valid address when the carrier is the USPS, but not when the carrier is UPS.

To save time, you might try browsing some international address formats on bitboost.

Answer 6

Will your logic work if two countries happen to have the same zip code? These two would be pointing to different cities in that case. here are some points to consider

1. Do you want to use zipcode as a kind of primary key into address? (at lease the city, state and country fields). In that case, you can have zipcode, city,state,country in one table. Create indexes on city, state etc.. (you have a functional dependency of the form zipcode->country,state,city . This as i said may not be true across countries.
2. If auto populating is your only concern, create a materialized view and use it.

I would recommend reading 'Data Model patterns' by David C. Hay.

Answer 7

But not every person who has a valid medical claim is required by law to remain in the US until the claim is settled. People move.

Answer 8

San Francisco is a city in California; it's not a city in Alabama. Does your design prevent nonsense entries like "San Francisco, AL"?

Answer 9

A five-digit ZIP is not a good key because two place names will use the same ZIP and one ZIP can refer to two city names. See http://semaphorecorp.com/cgi/zip5.html