CODEWITHSUNDEEP
mysqldatabase
DragonFire
DragonFire

Reputation: 4092

Country State City Table - ID or name

Hi I am relooking at the design of my mysql dbase for efficiency..

currently i have 3 tables

tble country : country id, country name

table state : state id, state name, country id

table city : city id, city name, state id

I am thinking whether it is better to have ...

  1. country name instead of country id in table state

  2. state name instead of state id in table city

this is because everywhere in my code i have to run extra queries to convert country id, state id and city id from numbers to alphabets (eg. 1 to USA)... wouldn't it be better to just reference alphabetically.. (less queries)

Upvotes: 0

Views: 2823

Answers (2)

SIDU
SIDU

Reputation: 2278

The whole world has roughly 

260 country/regions
5000 states
many many cities

Design varies based on what you need.

1 - For the purpose of tiny storage:

country(id,country)
state(id,state,country_id)
city(id,city,state_id)

2 - For the purpose of quick query:

city(id,city,state,country)

3 - For the purpose of middle way:

country(code,country)
state(code,country) -- you might merge country and state into one table based on code
city(state_code,city)

You might be interested to have a look at the iso codes:

https://en.wikipedia.org/wiki/ISO_3166-1 eg US

https://en.wikipedia.org/wiki/ISO_3166-2 eg US-NY

As a result iso state code contains iso country code.

UPDATE as per more info from you:

If you are designing property websites for USA.

1 - You do not need a country table, most likely all properties are within USA

2 - There are less than 60 states within USA, so you can use enum to save sates. As nearly all of you will understand NY = New York, as a result you do not need a state table.

3 - So you need a city table. As you will use city_id for more than 10,000,000 property records.

usa_cities(
  id int PK
  state enum('NY', 'LA', ...)
  city varchar
  ...
)

properties(
  id int PK
  city_id int,
  ....
)

AS property table is usually very big, you might skip state table, and de-normalize design to speed up query for less joins:

properties (
  id int PK,
  state enum('NY', 'LA',...)
  city varchar
  ...
)

You might be able to use enum for city as well, I m not sure how many cities in usa, but it is not encouraged at my first thought.

Upvotes: 1

gongsun
gongsun

Reputation: 544

If you want less query there is some technique call denormalization.

You can weight what most important and fit to your need.

for more about demonalization meaning from techopidia and wikipedia

Upvotes: 0

Related Questions