0

I need to manage countries, states/province and cities name information. All information will display on webpage with drop down list or other ways. Currently I am designing my database. I have two ideas to design tables

1. use only one table

countries, states/provinces and cities are usually very stable information, so I want to use only one table to manage all information and make it reference to itself such as:

id  name           parent_id type
--+--------------+----------+--
1   USA            null      1
2   California     1         2
3   Los Angeles    2         3
4   San Francisco  2         3

type: 1 for country, 2 for states or provinces and 3 for cities

2. use separate tables

I will use one table to manage country, and one table to manage states/province and one table to manage cities. then make them reference between each other.

So which one is better? and please explain your reason.

user3587602
  • 1
  • 1
  • 3

4 Answers4

1

I don't think "speed" is the problem here, like @fanfan1609 said, but if you use one table, you would save "different" things in one table. A state is not a country and so on. Think of database-normalization.

What if a city will get a postalcode, because you need it? Would you change your monster-table to have a postalcode column, just for one type? Then you begin to add a "meta" column of type varchar, and let the crap begin.

Michael Freund
  • 234
  • 2
  • 12
0

I think #2 is better.

With separate tables, you can easily manage records. For SELECT query; it's is faster

Example If you want select country only ,

1# : SELECT * FROM countries WHERE type = 1 ;

2# : SELECT * FROM countries;

Of course; 2# is faster because data in tables is less than 1#

fanfan1609
  • 179
  • 2
  • 8
  • but what will be happen if I try select all cities in California? – user3587602 May 06 '14 at 07:08
  • Assume you have these tables as countries, cities, provinces. Your sql may be `SELECT * FROM cities AS c JOIN provinces as p on p.id = c.province_id WHERE p.name = "California";` – fanfan1609 May 08 '14 at 07:10
0

You should certainly choose table-splitted solution. It is so due extensibility issue. Once you need to extend your country or state dataset stored, you'll see how inefficient it would be.

xacinay
  • 881
  • 1
  • 11
  • 29
-2

#1 is Better than #2
also, you can create recursive view
Take a look here:
How to do the Recursive SELECT query in MySQL?

Good Luck !

Community
  • 1
  • 1
4EACH
  • 2,132
  • 4
  • 20
  • 28