How to manage Countries in database

Question

I know my question see a bit vague, but I feel like managing the countries/addresses is actually something pretty common, so I would like to get some advices on my actual setup.

I have a database with a "country" column, previously it was a medium int type, acting as Foreign Key to another table with the actual information about the countries (id, name and ISO3166-1 alpha2 code mainly).

After some testing and benchmarking I ended up having all the countries information in a php file array instead, including/requiring it when needed, and it was like one or two orders of magnitude faster than querying the database. (they are 278 countries).

So apparently it's a better approach but I feel like there's something wrong because people normally tend to do this kind of stuff reading from a table instead from a file, but I can't figure out what would it be, is it easier to maintain or something like that?

Also, I was thinking of having the 2 letters ISO code as the key instead of a numeric id, it would be more human-readable and they are unique anyway. I see no noticeable performance loss in the 400.000 rows table, will this end up being an error if my database grows ?

Answer 1

In general, you want to keep things together that change together. So, if your main data lives in a SQL database, keeping the country data in the database helps avoid craziness like someone changing the PHP lookup array without realizing there's a bunch of data in a SQL database.

It also helps avoid duplication - if you build a second application for your system (e.g. an administration system), you don't end up with 2 copies of the country lookup PHP files. Again, duplication creates the opportunity for bugs, with a developer changing the database and one of the lookup files, but not the other.

All of this is rather defensive - but applications tend to evolve in unexpected ways, and avoiding bugs is usually a good idea.

In my experience, using a join between two tables has almost no measurable performance impact on a well-tuned system with the numbers you mention - did you optimize the SQL before moving the lookup to PHP?