First Schema for Location and Countries

Question

This is my first MYSQL schema for caching location data (co-ordinates to place names) and referencing it with a country.

I was wondering what everyones feedback was on it, did I do a good job?

CREATE TABLE location (
id INT AUTO_INCREMENT PRIMARY KEY,
locality VARCHAR(20),
administrative_area_level_1 VARCHAR(20),
administrative_area_level_2 VARCHAR(20),
administrative_area_level_3 VARCHAR(20),
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE country (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
iso VARCHAR(20),
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Answer 1

For all it's worth, and this is pretty old but for the sake of clearing unanswered reviews, here is my take:

• id INT AUTO_INCREMENT PRIMARY KEY,

  I find that being more specific in column names makes the code easier to work with, especially when you are using JOIN and table aliases. Suppose the following:

  SELECT l.*, c.*
  FROM location AS l
  LEFT JOIN country AS c
  ON l.loc = c.loc
  

  The result set would have column headers as l.id and c.id unless you took the time to make column aliases for everything. If your columns were instead named location_id and country_id the result set would be much easier to read to someone who is not a database programmer.

• name VARCHAR(20)

  I think 20 may be a bit short for country name. For instance:

  SELECT CHAR_LENGTH('United States of America');
  

  Result: 24

Other than that I think it looks fine.