1
\$\begingroup\$

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
);
\$\endgroup\$
2
  • 1
    \$\begingroup\$ are these two tables linked? I don't see a Foreign key or anything to link the tables together. I do see 2 columns that have the same name in both tables? \$\endgroup\$
    – Malachi
    Oct 17 '13 at 17:26
  • \$\begingroup\$ Idem. Your query is ambiguous for Code Review. I would personally expect a country.id column as foreign key in location but may or may not be the case depending on your schema design. \$\endgroup\$
    – Phrancis
    May 21 '14 at 5:09
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.