Organising iso country codes in a database

Posted on 2009-02-11
Medium Priority
Last Modified: 2012-05-06
I have data on country, region, subregion codes and names in an xls file (see attachment)
What would be the best way to store this data in a database (mysql)?
I'm also going to be adding a further column with cities.

e.g. should the countries, regions, subregions, cities each have their own table and be linked through a foreign key?

or should all the data be in the same table?

Does it make sense to hold this data in the DB in the first place as opposed to in arrays in php files?
I'll be doing up to 20 lookups on a single page where i will have, for example, GB-BNE ... and I'll need to fetch that it's Barnet in England in United Kingdom so I'm looking for an efficient way to do this.
Question by:phpd
  • 4
  • 2
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23612435
I would most certainly put this into an array in php, NOT in a database where every query costs time.


Author Comment

ID: 23612571
the array would have several thousand values ... including a php file with such an array would probably also eat up quite a bit of memory.
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23612677
Let's say 10,000 values, each with 30 characters, that's 300,000 bytes, or 300K, not very big in terms of memory size.

If you structure the array(s) well, you can make the programming very easy, too.  Probably a simple function or two, which you call with the base value to get the rest from the array(s).
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

LVL 23

Expert Comment

by:Tony McCreath
ID: 23620526
If you put it in a database, I would construct a single query to load all the countries for a page in one go. Probably by using the IN syntax.

Personally I would normalise the data into multiple tables with foreign keys and indexes.

I don't know much about php. Do searches in php arrays scale well? Would a page have to load and construct the array then iterate through every value to find matches?
LVL 27

Accepted Solution

Cornelia Yoder earned 2000 total points
ID: 23621504
@tiggerito.  No, php arrarys are some of the best organized in any programming language I've ever used.  The functions available for sorting and retrieving data are very versatile.  

If he were to construct a large query and bring in all the information from a database, it would still have to be processed via the resultset and the row arrays, and put into another array for searching.   One well-constructed array already in php would be much more efficient.

Using a database as you suggest, with multiple tables, foreign keys and indexes, and complex queries is killing a flea with a cannon.
LVL 23

Expert Comment

by:Tony McCreath
ID: 23623075
I looked at the docs and now realise an "array" in php is what I would call a dictionary or hash table. i.e a collection of key/values where the key is indexed to create fast access.

You still have to be careful to not re-load this array on every request. Having an include file that is 1000s of lines long can't be good for speed. Does php have a good place to store data in memory, like a session, cache or global store?

As a php array is a set of key/value pairs with extra data to optimise random access, it will take more memory than the basic cost of the data itself (300k). It would still be a small amount, but not something you want to repeatedly load every request.

Sorry about these sub questions, I hope they will contribute to providing a better solution in the end.
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23623148
Including a script is far more efficient than accessing the database every time would be.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question