StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think it is best to add another table, countries. Your problem is an example why database normalization is important. You can't just mix and match different keys to one column. </p> <p>So, I suggest you to create these table:</p> <h3>countries:</h3> <pre> +------------+--------------+ | <b>country_id</b> | country_name | +------------+--------------+ </pre> <h3>states:</h3> <pre> +------------+----------+------------+ | <b>country_id</b> | <b>state_id</b> | state_name | +------------+----------+------------+ </pre> <h3>cities</h3> <pre> +------------+----------+---------+-----------+ | <b>country_id</b> | <b>state_id</b> | <b>city_id</b> | city_name | +------------+----------+---------+-----------+ </pre> <h3>data</h3> <pre> +------------+----------+---------+---------+----------+ | <b>country_id</b> | <b>state_id</b> | <b>city_id</b> | <b>data_id</b> | your_CSV | +------------+----------+---------+---------+----------+ </pre> <p>The bold fields are primary keys. Enter a standard country_id like 1 for US, 91 for india, and so on. city_id should also use their standard id.</p> <p>You can then find anything belongs to each other pretty fast with minimal overhead. All data can then entered directly to data table, thus serving as one entry point, storing all the data into single spot. I don't know with mysql, but if your database support partitioning, you can partition data tables according to country_id or country_id+state_id to a couple of server arrays, thus it will also speed up your database performance considerably. The first, second, and third table won't take much hit on server load at all, and only serve as reference. You will mainly working on fourth data table. You can add data as much as you wish, without any duplicate ever again.</p> <p>If you only have one data per city, you can omit data table and move CSV_data to cities table like this:</p> <h3>cities</h3> <pre> +------------+----------+---------+-----------+----------+ | <b>country_id</b> | <b>state_id</b> | <b>city_id</b> | city_name | CSV_data | +------------+----------+---------+-----------+----------+ </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload