0

I have one table 

Table 
Email_Address
Name
contact_No
city
Pincode
state
country

I divided into two parts

Table1
Email_Address(PK)
Name
contact_No
city

table2    
city
Pincode
state
country

how i can maintain the relationship between this table.

Thilo
  • 257,207
  • 101
  • 511
  • 656
Hari
  • 1
  • 1

1 Answers1

1

City would be the primary key of table2 and a foreign key in Table1. (But that requires city names to be globally unique and have only a single pincode each).

You need to be sure that a single city has only one Pincode. If that is not the case, probably use the Pincode as primary/foreign key instead of city.

Update: Since apparently there are cases where some pincodes span multiple cities, you probably need to live with the redundancy or introduce an artificial key. Also see the links to similar questions here in the comments.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • 1
    No on that first paragraph, otherwise you're going to get into trouble between Richmond in Virginia and Melbourne, or Perth in Western Australia and Scotland, or Springfield in a dozen different places in the US. The primary key needs to be _unique._ – paxdiablo Dec 16 '10 at 06:12
  • In the US, some zip codes (pincodes) cover several cities; other cities have multiple zip codes. My zip code is used by two cities, both of which also have other zip codes. And my zip code straddles a county boundary. I believe that there are zip codes that straddle two states. Addresses are hard to handle (even in a single country, let alone internationally), mainly because humans are so inventive. See also [SO 310540](http://stackoverflow.com/questions/310540/best-practices-for-storing-postal-addresses-in-a-database-rdbms) Best Practices for Storing Postal Addresses in a Database. – Jonathan Leffler Dec 16 '10 at 07:36
  • Alternative question with better answers: [SO 929684](http://stackoverflow.com/questions/929684/is-there-common-street-addresses-database-design-for-all-addresses-of-the-world) Is there a common street address database design for all addresses of the world? – Jonathan Leffler Dec 16 '10 at 07:55