Una aplicación web en la que estoy trabajando ha encontrado un 'error' inesperado: la base de datos de la aplicación tiene dos tablas (entre muchas otras) denominadas 'Estados' y 'Ciudades'.

'Estados'campos de tabla:

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

'estados de id'es una clave principal que se incrementa automáticamente.

'Ciudades'campos de tabla:

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

'idAreaCode'es una clave principal que consiste en el código de país + el código de área (por ejemplo, 91422, donde 91 es el código del país para la India y 422 es el código de área de una ciudad en la India). 'estados de id'es una clave foránea derivada de'Estados'Mesa para asociar cada ciudad en el'CiudadesMesa con su correspondiente Estado.

Pensamos que la combinación de código de país + código de área sería única para cada ciudad y, por lo tanto, podría usarse de manera segura como clave principal. Todo estaba funcionando. Pero una ubicación en la India encontró un "fallo" inesperado en el diseño de la base de datos: la India, al igual que los Estados Unidos, es una democracia federal y está dividida geográficamente en muchos estados o territorios sindicales. Los datos de los estados y territorios de la unión se almacenan en el 'Estados' mesa. Hay, sin embargo, una ubicación -Chandigarh - que pertenece a DOS estados (Haryana yPunjab) y es también un territorio de unión por sí mismo.

Obviamente, el diseño actual de db no nos permite almacenar más de un registro de la ciudad 'Chandigarh'.

Una de las soluciones sugeridas es crear una clave principal combinando las columnasidAreaCode'y'estados de id'.

¿Me gustaría saber si esta es la mejor solución posible?

(FYI: estamos usando MySQL con el motor InnoDB).

Más información:

La base de datos almacena información meteorológica para cada ciudad. Por lo tanto, el estado y la ciudad son el punto de partida de cada consulta.Todos los días se insertan datos nuevos para cada ciudad mediante un archivo CSV. El archivo CSV incluye una columna idStates (para el estado) y idAreaCode (para la ciudad) que se usa para identificar cada registro.La normalización de la base de datos es importante para nosotros.

Nota: la razón para no usar una clave primaria de incremento automático para la tabla de la ciudad es que la base de datos se actualiza todos los días / horas utilizando un archivo CSV (generado por otra aplicación). Y cada registro en el archivo CSV se identifica por las columnas idStates e idAreaCode. Por lo tanto, es preferible que la clave principal utilizada en la tabla de la ciudad sea la misma para todas las ciudades, incluso si la tabla se elimina y se actualiza nuevamente. Los códigos postales (o códigos pin) y los códigos de área (o códigos STD) cumplen con los criterios de ser únicos, estáticos (no se cambian con frecuencia) y una lista lista de ellos está fácilmente disponible. (Decidimos los códigos de área por ahora porque India está en proceso de actualizar sus códigos PIN a un nuevo formato).

lossolución decidimos que era manejar esto en el nivel de la aplicación en lugar de hacer cambios en el diseño de la base de datos. En la base de datos solo almacenaremos un registro de 'Chandigarh'. En la aplicación, hemos creado una marca para cualquier búsqueda de 'Chandigarh, Punjab' o 'Chandigarh, Haryana' para redirigir la búsqueda a este registro. Sí, no es lo ideal, pero es un compromiso aceptable ya que esta es la ÚNICA excepción que hemos encontrado hasta ahora.