MySQL inserts iso country or country id from another table

Question

I want to insert each visitor country into my database. Maxmind returns 2 ISO countries ISO, which I could store in VARCHAR(2) , which will use 2 bytes, or, alternatively, I can use UNSIGNED TINYINT , which will use 1 byte, and will be the identifier from the table with all countries .

However, I hit a punch; I need a MyISAM mechanism for quick inserts, but MyISAM does not support FOREIGN KEYS , so I assume that for each insert I will need to make a selection in the country table to get the country identifier.

I do not know what is the best option, I absolutely need to use MyISAM , since there will be many inserts, but I do not want to do SELECTs to get the country identifier.

Answer 1

If you only need a 2-letter ISO country code (and not a country name, language or other information), I would say that saving it as CHAR (2) without an external table would be less resources -interesting than storing it as SMALLINT (TINYINT will not be enough to cover all countries) to find an additional table.

Note: in this case there is no need for VARCHAR (2), CHAR (2) will be more efficient.

Answer 2

You can create an associative array from a country table. Enter the country identifier as the key for this array. Then make it viewable. Thus, with the help of the views, you can view the array of the country and display the corresponding country. eg

 <?php echo $country[$customer['country_id']]['country_name']; ?> 

Answer 3

The only difference between supporting FK and not having it is integrity. If you want to use an engine that supports FK, it will not allow you to set a country without a record identifier.

However, the number of countries is semi-static, and we are only talking about hundreds ... you could keep all this in mind and have an identification number.

Alternatively, you can make a choice during insertion, for example:

INSERT INTO sometable (col1,col2,country_id) VALUES('val1','val2', (SELECT id FROM countries WHERE iso_code = ?))

? it will be your ISO value.