Country and Zip tables

[oaskedal]

Hi!

Ok, here's my problem:
How should I put up a COUNTRY and ZIP code table to best avoid duplicate records from my related USER table.

And this is where I am so far:
I've tried to combine both foreign key attributes 'countryCode' and 'zip' from USER tab as the PRIM KEY of my ZIP table. In this way I thought they could identify a spesific city and avoiding duplicate entries. But now the relation doesn't work. I cannot DELETE a user from USER tab as long as I've set these foreign keys, as I have to deal with the PRIM KEY in ZIP as well.

How should I put up the relation and tables? Should I avoid making a relation between USER and ZIP? 😕

Am I right both ZIP and COUNTRY tables should be predefined look up tables, and not manipulated by user entries (i.e. a user defines his 'zipcode' correct, but his 'city' wrong, and all users from this city gets 'city' misspelled)

Any thoughts on how to deal with ZIP and COUNTRY issues in databases? 🙂

[mrbaseball34]

They should be only lookup tables as the zip may change for any user, even if they do not move. Then you'd have even more problems.

Go ahead and put an index on them so the lookups will be faster but other than that, no relationships.

[oaskedal]

Great! Thank you, I'll try that :-)