Basic table design Q

Question

I am trying to understand this concept. For example: I have two tables City and Country.

Country
-------
id  
abbreviation   
name   


City
-----
id   
name   
Country (name or id, or both? - This is the question)

To reference and keep a particular city in sync with the country it belongs to I guess this will be reference to country.id as a FK. This means an example of the city table will be: (200, New York, 19) - where 19 = USA in country table. But this doesn't help a person viewing the table because he wont know what 19 is without looking up in country table what 19 is.

So I want to add the country name also to city table so it reads: (200, New York, USA). I don't need the 19 to display because 19 is of no use to the reader but is only used in back to connect the tables.

So what should my tables colunms / FK look like to i can store in city table rows like this (200, New York, USA), yet ensure New york will always reference to USA in the USA lookup and keep the 19 which is the primary key for USA out of the city table so the tables look clean and easy to understand? And I assume if these are referenced, tomorrow if i update USA to be 20, it will update in the city table on its own, and same way if I rename USA to US it will update on city table on its own?

• My DB is in MySQL

Answer 1

You don't ... if you need to have "usable" tables in the DB, so someone can easily view something useful with select * etc. (or to make programing the SQL by hand easier). Then you create the tables as above, in normal form, and then create a VIEW which combines the tables.

Answer 2

Tables aren't supposed to be viewed by people: some application should be accessing the tables to present data to people in a way they can parse it. The reason you want to have country.id as a FK in your table is so that you don't have a million rows whose country name is "USA", because then all kinds of problems can occur, like what happens when you mistype and "US A" lands in one of the fields? Or what if you want to change what the user sees from "USA" to "United States"?

The right way to handle it is to use the country.id as you initially suggest, and then use a JOIN statement to present the data, like this:

SELECT city.name, country.name
  FROM city JOIN country ON country.id = city.country

My syntax could be off, but that's in essence what you want.

Answer 3

Why not use ISO 3166 country codes (2 character or 3 character) as the country ID? This leaves you with recognizable codes in the city table; you can map to the full name in the country table.

As for viewing the data, use a VIEW to create a good looking table:

CREATE VIEW CityInfo(CityID, CityName, CountryID, CountryName) AS
    SELECT ci.id, ci.name, ci.country, co.name
      FROM City AS ci JOIN Country AS co ON ci.Country = co.id;