We help IT Professionals succeed at work.

Organising iso country codes in a database

phpd
phpd asked
on
Medium Priority
548 Views
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.
scrn.png
Comment
Watch Question

Commented:
I would most certainly put this into an array in php, NOT in a database where every query costs time.

Author

Commented:
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.

Commented:
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).
Tony McCreathTechnical SEO Consultant

Commented:
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?
Commented:
@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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Tony McCreathTechnical SEO Consultant

Commented:
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.

Commented:
Including a script is far more efficient than accessing the database every time would be.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.