Data Design Question (Zip codes) 2

[skiflyer]

I'm developing a database, part of which involves the normal everyday personal information part.

I'm planning on either finding a database of zip-codes to cities, or having my program build one as it goes.

My question is this...

From real world examples, is this relationship unique? Can I just store the zip code in my user table as a foreign key to a table which holds zip/city/state? Or will I be asking for trouble?

Obviouslly I'd be violating normal forms to just copy the data over to the user form, but I'm a little worried there may be some cases in which the user says, hey that match is wrong for this one guy who lives on the edge of town but is in this zip code anyway, now I need to modify his town name.

Thinking I'll be ok to just foreign key it the more I type, but I'd like to hear any arguments in the negative.

Thanks,
Rob

 

[r937]

storing just the zip as a foreign key works fine, and you can probably obtain a zip lookup table which translates into city/state, and it would certainly take care of the problem of ensuring consistency (preventing wrong city for the zip)...

... however, if you do that, then you should still have form fields for city and state, because fer shure you'll have users don't know their zip, and you should let them enter data anyway

rudy

 

[olded]

Rudy:

I agree with you - that's why you get the star. I've seen this one before.

Ed

 

[skiflyer]

So a lookup is the way to go then, makes good sense... in theory the information should never change and if it does chances are I'm not going to want to update everyone's towns on them automatically.

Thanks for the insight.

-Rob

 

[sfvb]

Hi Rob,

Can I just store the zip code in my user table as a foreign key to a table which holds zip/city/state? Or will I be asking for trouble?

Our company has come across two different states that have 2 different cities with one zipcode. One I remember is in the vicinity of Boulder, CO. and the other one is out east somewhere (NJ, I think). If you like, I can give you the cities names when I'm at work tomorrow, so that you can verify this.

Depending on how localized your application is, you may be able to get by with just the zipcode as the primary key. But, if you run across the situation that we have run across, then there is no way to enter the other city, because the zipcode is the primary key. In that case you either need a surrogate primary key or a compound primary key consisting of the zip and city.

Steve

 

[skiflyer]

Thanks Steve! This is exactly the kind of thing I suspected but just couldn't verify.

I can't assume any localization in my app. I think I'm going to go the route of a lookup table, so in those 1% of ambigous situations the user will just have to modify the city by hand, it's not that big of a concern, the whole feature isn't really that big of a concern to be honest, just a nice add on so why not.

Thanks again,
Rob