Como comentaba en la entrada anterior, estaba realizando la carga de códigos postales de México. Encontré en la página de correos de México una base de datos en excel, que después convertí en csv y realice la carga por medio de un microservicio en spring boot. El objetivo no es explicar cómo cree el microservicio – tal vez en otra entrada realice alguna explicación de cómo funciona spring-, pero sí explicaré como normalice la base de datos de códigos postales.
La tabla de códigos postales sin normalizar se ve de la siguiente manera -según los datos que encontré en el excel- :
CREATE TABLE `tc_codigo_postal_sepo` (
`id_sepo` int(11) NOT NULL,
`codigo_postal` varchar(128) DEFAULT NULL,
`asentamiento` varchar(128) DEFAULT NULL,
`tipo_asentamiento` varchar(128) DEFAULT NULL,
`municipio` varchar(128) DEFAULT NULL,
`estado` varchar(128) DEFAULT NULL,
`ciudad` varchar(128) DEFAULT NULL,
`clave_cp` varchar(128) DEFAULT NULL,
`estato_abre` varchar(128) DEFAULT NULL,
`oficina` varchar(128) DEFAULT NULL,
`tipo_asentamiento_abre` varchar(128) DEFAULT NULL,
`municipio_abre` varchar(128) DEFAULT NULL,
`id_asentamiento_cp` varchar(128) DEFAULT NULL,
`zona` varchar(128) DEFAULT NULL,
`clave_ciudad` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
Para empezar con el proceso de normalización, se requiere una tabla para almacenar los estados y municipios.
CREATE TABLE `tc_municipio` (
`id_municipio` int(11) NOT NULL,
`id_estado` int(11) NOT NULL,
`descripcion` varchar(128) NOT NULL,
`id_estatus` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `tc_estado` (
`id_estado` int(11) NOT NULL,
`descripcion` varchar(128) NOT NULL,
`id_estatus` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
Mi tabla principal de códigos postales queda de la siguiente forma:
CREATE TABLE `tc_cp` (
`id_cp` int(11) NOT NULL,
`id_municipio` int(11) DEFAULT NULL,
`cod_postal` varchar(7) NOT NULL,
`id_estatus` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
De esta manera si me envían un código postal puedo obtener el municipio al que pertenece y a través de la tabla municipios puedo saber a qué estado pertenece.
Además, un municipio puede tener varias ciudades, que podemos obtener, también por medio el id del municipio:
CREATE TABLE `tc_ciudad` (
`id_ciudad` int(11) NOT NULL,
`descripcion` varchar(128) NOT NULL,
`municipio` int(11) NOT NULL,
`id_estatus` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
Para finalizar, contamos con una lista de asentamientos, como pueden ser colonias, fraccionamientos, poblados, etc.
CREATE TABLE `tc_tipo_asentamiento` (
`id_tipo_asen` int(11) NOT NULL,
`nombre` varchar(128) NOT NULL,
`id_estatus` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
CREATE TABLE `tc_asentamiento` (
`id_asen` int(11) NOT NULL,
`id_cp` int(11) NOT NULL,
`id_tipo_asen` int(11) NOT NULL,
`nombre` varchar(128) NOT NULL,
`id_estatus` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;
Ahora que ya tenemos la definición de nuestras tablas, es momento de crear los primary key y los auto_increment.
ALTER TABLE `tc_asentamiento`
ADD PRIMARY KEY (`id_asen`);
ALTER TABLE `tc_ciudad`
ADD PRIMARY KEY (`id_ciudad`);
ALTER TABLE `tc_codigo_postal_sepo`
ADD PRIMARY KEY (`id_sepo`),
ADD KEY `estato_abre` (`estato_abre`);
ALTER TABLE `tc_cp`
ADD PRIMARY KEY (`id_cp`);
ALTER TABLE `tc_estado`
ADD PRIMARY KEY (`id_estado`);
ALTER TABLE `tc_municipio`
ADD PRIMARY KEY (`id_municipio`);
ALTER TABLE `tc_tipo_asentamiento`
ADD PRIMARY KEY (`id_tipo_asen`);
ALTER TABLE `tc_asentamiento`
MODIFY `id_asen` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tc_ciudad`
MODIFY `id_ciudad` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tc_codigo_postal_sepo`
MODIFY `id_sepo` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tc_cp`
MODIFY `id_cp` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tc_estado`
MODIFY `id_estado` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tc_municipio`
MODIFY `id_municipio` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tc_tipo_asentamiento`
MODIFY `id_tipo_asen` int(11) NOT NULL AUTO_INCREMENT;
