0

I have a table that looks like the following:

enter image description here

I want to split this to 3 different tables with a PK-FK relationship. Essentially a province can have many state, a state can have many cities. What is the best way to do this in one shot using a SQL query if it's even possible? Or using tools like phpmyadmin.

adit
  • 32,574
  • 72
  • 229
  • 373

2 Answers2

1

Province table:

Province_ID (PK)
Province_Name

State table:

State_ID (PK)
State_Name
Province_ID (FK)
AreaCode

Citi table:

Citi_ID (PK)
Citi_Name
State_ID (FK)
Citi_Code

I don't think there's any tools that can help you normalize this table automatically. So you have to create these tables by code then using sql query to insert values into it

Doan Cuong
  • 2,594
  • 4
  • 22
  • 39
  • Can you at least give me an example, once I have the tables set up on the query to populate the content? – adit Apr 02 '13 at 10:16
  • Getting the province table set up is pretty much easy. But not sure about the 2nd table. – adit Apr 02 '13 at 10:24
  • The table structure is just like i described in my answer. About the query, you can reference to http://stackoverflow.com/questions/9285551/inserting-values-from-one-table-into-another-table – Doan Cuong Apr 02 '13 at 11:40
0

You may Partition It as follow :

Prov Table with Prov_ID as PK.

State Table with State_ID as PK and Prov_ID as FK

City Table with City_ID as PK and State_ID as FK and Prov_ID as FK

Blood-HaZaRd
  • 2,049
  • 2
  • 20
  • 43