I'm attempting to appreciate this concept. For instance: I've two tables City and Country.


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

To reference and a specific city synchronized using the country it goes to I suppose this is mention of the country.id like a FK. What this means is a good example of the town table is going to be: (200, New You are able to, 19) - where 19 = USA in country table. But this does not help an individual viewing the table while he wont understand what 19 is without searching for in country table what 19 is.

So I wish to add the nation title and to city table therefore it reads: (200, New You are able to, USA). I do not require the 19 to show because 19 is useless towards the readers but is just utilized in to connect the tables.

What exactly should my tables colunms / FK seem like to i'm able to store in city table rows such as this (200, New You are able to, USA), yet ensure New you are able to will invariably mention of the USA in the united states research and the 19 the primary key for USA from the city table therefore the tables look neat and clear to see? And That I assume if they are recommended, tomorrow basically update USA to become 20, it'll update within the city table by itself, and same manner basically relabel USA to US it'll update on city table by itself?

  • My DB is within MySQL

Why don't you use ISO 3166 country codes (2 character or 3 character) because the country ID? This leaves you with identifiable codes within the city table you are able to map fully title in the united states table.

For viewing the information, make use of a VIEW to produce a attractive 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;

You do not ... if you want to have "functional" tables within the DB, so someone can certainly view something helpful with choose * etc. (in order to make programing the SQL manually simpler). Then you definitely produce the tables as above, in normal form, after which produce a VIEW which mixes the tables.

Tables aren't said to be seen by people: some application ought to be being able to access the tables to provide data to individuals in ways they are able to parse it. The main reason you need to have country.id like a FK inside your table is to ensure that you do not have millions of rows whose country title is "USA", because then a myriad of problems can happen, like what goes on whenever you mistype and "US A" lands within the fields? Or what if you wish to change exactly what the user sees from "USA" to "U . s . States"?

The proper way to handle it is by using the nation.identification while you initially suggest, after which make use of a JOIN statement to provide the information, such as this:

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

My syntax might be off, but that is essentially what you would like.