Database Design: Composite key vs one column primary key

Question

A web application I am working on has encountered an unexpected 'bug' - The database of the app has two tables (among many others) called 'States' and 'Cities'.

'States' table fields:

-------------------------------------------
idStates   |   State   |   Lat   |   Long
-------------------------------------------

'idStates' is an auto-incrementing primary key.

'Cities' table fields:

----------------------------------------------------------
idAreaCode   |   idStates   |   City   |   Lat   |   Long
----------------------------------------------------------

'idAreaCode' is a primary key consisting of country code + area code (e.g. 91422 where 91 is the country code for india and 422 is the area code of a city in India). 'idStates' is a foreign key derived from 'States' table to associate each city in the 'Cities' table with its corresponding State.

We figured that the country code + area code combination would be unique for each city, and thus could safely be used as a primary key. Everything was working. But a location in India found an unexpected 'flaw' in the db design - India, like the US is a federal democracy and is geographically divided into many states or union territories. Both the states and union territories data is stored in the 'States' table. There is, however, one location - Chandigarh - which belongs to TWO states (Haryana and Punjab) and is also a union territory by itself.

Obviously, the current db design doesn't allow us to store more than one record of the city 'Chandigarh'.

One of the solutions suggested is to create a primary key combining the columns 'idAreaCode' and 'idStates'.

I'd like to know if this is the best solution possible?

(FYI: we are using MySQL with the InnoDB engine).

---

More information:

• The database stores meteorological information for each city. Thus, the state and city are the starting point of each query.
• Fresh data for each city is inserted everyday using a CSV file. The CSV file includes an idStates (for state) and idAreaCode (for city) column which is used to identify each record.
• Database normalization is important to us.

---

Note: The reason for not using an auto incrementing primary key for the city table is that the database is updated everyday / hourly using a CSV file (which is generated by another app). And each record in the CSV file is identified by the idStates and idAreaCode column. Hence it is preferred that the primary key used in the city table is the same for every city, even if the table is deleted and refreshed again. Zip codes (or pin codes) and area codes (or STD codes) meet the criteria of being unique, static (don't change often) and a ready list of these are easily available. (We decided on area codes for now because India is in the process of updating its pin codes to a new format).

The solution we decided on was to handle this at the application level instead of making changes to the database design. In the database we will only be storing one record of 'Chandigarh'. In the application we've created a flag for any search for 'Chandigarh, Punjab' or 'Chandigarh, Haryana' to redirect search to this record. Yeah, it's not ideal, but an acceptable compromise since this is the ONLY exception we've come across so far.

Answer 1

I recommend adding a new primary key field to the Cities table that will be simply auto-incremental. The KISS methodology (keep it simple).

Any other solution is cumbersome and confusing in my opinion.

Answer 2

Having a composite key could be problematic when you want to reference that table, since the referring table would have to have all columns the primary key has.

If that's the case, you might want to have a sequence primary key, and have the idAreaCode and idStates defined in a UNIQUE NOT NULL group.

Answer 3

Definitely use an identity column:

id int IDENTITY(1,1)

I'm not sure how many cities will eventually be in your database, but if you add a separate primary key, it may make sense to have it NONCLUSTERED for performance reasons.

If your db access pattern is usually through the state, I'd create a clustered index on the idStates so that you don't decrease in performance from your previously well indexed table.

Answer 4

If you go with adding an additional column to the key so that you can add an additional record for a given city, then you're not properly normalizing your data. Given that you've now discovered that a city can be a member of multiple states, I would suggest removing any reference to a state from the Cities table, then adding a StateCity table that allows you to relate states to cities (creating a m:m relationship).

Answer 5

It sounds like you are gathering data for a telephone directory. Are you? Why are states important to you? The answer to this question will probably determine which database design will work best for you.

You may think that it's obvious what a city is. It's not. It depends on what you are going to do with the data. In the US, there is this unit called MSA (Metropolitan Statistical Area). The Kansas City MSA spans both Kansas City, Kansas and Kansas City, Missouri. Whether the MSA unit makes sense or not depends on the intended use of the data. If you used area codes in US to determine cities, you'd end up with a very different grouping than MSAs. Again, it depends on what you are going to do with the data.

In general whenever hierarchical patterns of political subdivisions break down, the most general solution is to consider the relationship many-to-many. You solve this problem the same way you solve other many-to-many problems. By creating a new table, with two foreign keys. In this case the foreign keys are IdAreacode and IdStates.

Now you can have one arecode in many states and one state spanning many area codes. It seems a shame to accpet this extra overhead to cover just one exception. Do you know whether the exception you have uncovered is just the tip of the iceberg, and there are many such exceptions?

Answer 6

Imtroduce a surrogate key. What are you going to do when area codes change numbets or get split? Using business keys as a primary key almost always is a mistake.

Your above summary is another example of why.

Answer 7

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.

So, I suggest you to create these table:

countries:

+------------+--------------+
| country_id | country_name |
+------------+--------------+

states:

+------------+----------+------------+
| country_id | state_id | state_name |
+------------+----------+------------+

cities

+------------+----------+---------+-----------+
| country_id | state_id | city_id | city_name |
+------------+----------+---------+-----------+

data

+------------+----------+---------+---------+----------+
| country_id | state_id | city_id | data_id | your_CSV |
+------------+----------+---------+---------+----------+

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.

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.

If you only have one data per city, you can omit data table and move CSV_data to cities table like this:

cities

+------------+----------+---------+-----------+----------+
| country_id | state_id | city_id | city_name | CSV_data |
+------------+----------+---------+-----------+----------+