database - learning - ¿Vale la pena desglosar la información de la dirección en una tabla de base de datos separada?

normalization forms in dbms

Question

Tengo una tabla llamada "Persona" con los siguientes campos

• Id (Clave principal)
• Nombre de pila
• Apellido
• Fecha de nacimiento
• Ciudad
• Estado
• País

Si las cosas como Ciudad, Estado o País se normalizan y dividen en su propia tabla y luego esta tabla tiene columnas CityId y StateId. Teníamos un debate sobre si esta era una decisión buena o mala.

Para agregar, tengo una tabla de Ciudad y Estado (por otros motivos no relacionados con esta tabla de personas). Tengo curiosidad por las respuestas con o sin este hecho adicional.

Answer 1

Antes de comenzar, quiero señalar que {ciudad, estado, país} no es una dirección.

Si las cosas como Ciudad, Estado o País se normalizan y dividen en su propia tabla y luego esta tabla tiene columnas CityId y StateId. Teníamos un debate sobre si esta era una decisión buena o mala.

La normalización es buena. Casi siempre estoy abogando por la normalización.

Pero usar números de identificación en lugar de texto no tiene nada que ver con la normalización. Sustituir "CityId" por "City" y "StateId" por "State" no tiene efecto en la forma normal de la tabla. Si estuvo en 3NF antes de ese cambio, todavía estará en 3NF después de ese cambio.

Puede aumentar la integridad de los datos con una referencia de clave externa. La integridad de los datos también es buena. Pero esto, como muchas otras decisiones de diseño de bases de datos, no tiene nada que ver con la normalización.

La forma más sencilla de aumentar la integridad de los datos para las ciudades sería seleccionar ciudades distintas en una nueva tabla. (Sintaxis de PostgreSQL.)

select distinct city, state, country
into new_table
from person;

Necesita la ciudad, el estado y el país para representar el "nombre completo" de una ciudad. También necesitas una llave.

alter table new_table
add primary key (city, state, country);

Ahora puede declarar una restricción de clave externa para garantizar que {ciudad, estado, país} siempre hará referencia a una sola fila en esa nueva tabla.

alter table Person
add constraint city_state_country_from_new_table
foreign key (city, state, country)
references new_table (city, state, country)
on update cascade;

No me preocuparía por el rendimiento de las actualizaciones en cascada para este tipo de tabla. (A menos que estuviera usando Oracle; Oracle no admite actualizaciones en cascada). Estos tipos de nombres cambian rara vez, y sé que PostgreSQL puede realizar actualizaciones en cascada de 3 millones de filas en una tabla de 50 millones de filas en menos de 3 segundos en mi escritorio. Mi escritorio no es nada especial, y ejecuta 3 sistemas de administración de bases de datos y dos servidores web. Si tuviera mesas más grandes y necesitara más tiempo, programaría el cambio durante una ventana de mantenimiento.

Puede aumentar la integridad de los datos para los estados de la misma manera.

select distinct state, country
into another_new_table
from new_table;
etc., etc.

Habiendo dicho todo esto, agregar una clave sustituta a la tabla nueva es una decisión de diseño defendible, pero solo si pasas un tiempo pensando en ello. (No pensar nunca es defendible.)

El efecto más inmediato de reemplazar {ciudad, estado, país} con una clave sustituta es que ahora necesita una combinación en cada consulta en una tabla que no requirió uniones antes. Puede probar el efecto sobre el rendimiento con datos de muestra aleatorios. Hasta que tenga muchos millones de filas, probablemente encontrará que la clave natural es más rápida que una combinación en la clave sustituta. Eso es lo que encontré cuando lo probé.

Answer 2

Consideraría dividir la ciudad, el estado y el país en una sola tabla de "dirección (o ciudad)" que contenga el estado y el país replicados en las filas. Para el número de ciudades únicas en el mundo, esto no es un costo de consulta de base de datos real.

También depende de la cantidad de registros que espera tener: si el recuento total de personas siempre será menor que, por ejemplo, 100.000, ¿realmente vale la pena el esfuerzo por normalizar los datos?

Tener una estructura de datos plana hace que las consultas y las pruebas sean mucho más sencillas, por lo que, a menos que exista un problema de rendimiento o de espacio en el disco, tal vez sea mejor "mantenerlo simple".

Answer 3

Creo que el nivel de normalización realmente depende de cuán grande sea la aplicación. Como mínimo, al menos tendría una tabla de direcciones para que CRUD pueda realizarse en las direcciones sin estar acoplado a los usuarios. Probablemente desee desglosarlo más si hay planes en la interfaz de usuario para tener menús desplegables que enumeren ciudades o estados o que estén proporcionando servicios web. Se vuelve un poco más complicado si necesita tener en cuenta las direcciones externas y APO/FPO . Los objetivos de normalización enumerados en la página de wikipedia podrían valer la pena para ver si alguno de los escenarios se debe tener en cuenta en su proyecto. Haz tu mejor esfuerzo para no duplicar datos o esfuerzos sin sobre diseñar.

Quería proporcionar información adicional que su equipo podría considerar:

Luke W. tiene una gran información sobre el diseño de la interfaz de usuario para las direcciones.

• http://www.lukew.com/ff/entry.asp?605
• http://www.lukew.com/ff/entry.asp?968

Si está implementando vía web, hay muchas API de servicios web que ya administran los datos de ubicación.

• https://www.usps.com/business/web-tools-apis/list-of-apis.htm
• http://www.zippopotam.us/

Si es necesario mantener los datos internamente o si no le gusta confiar en servicios externos, utilice uno de los orígenes de datos abiertos , como GeoNames . Los datos son un archivo de texto separado por tabulaciones, pero se pueden analizar fácilmente con un script para cargar los datos automáticamente.

Answer 4

Depende de dónde obtengas los datos de Ciudad, Estado y País.

Si su aplicación le permite al usuario ingresar esta información pero lo obliga a seleccionar estos valores de los menús desplegables que se completan con sus datos maestros, será una buena idea contraer estos tres campos a algo como "locationId" y tener una tabla qué registros almacenados (city_id, state_id, country_id). No necesita estos tres ID en su tabla de Persona ya que la combinación cambiará muy raramente.

Por el contrario, si está permitiendo que sus usuarios ingresen los valores de Ciudad, Estado y País, entonces puede ser difícil separarlos en una tabla separada debido a los valores escritos de forma diferente para la misma ciudad / estado / país.

Answer 5

Desde mi experiencia, sí.

1 La ciudad, el estado y el país son entidades en el mundo real, por lo que es bueno tenerlas como entidades en su modelo de base de datos. Mantiene los nombres consistentes como los otros respondedores ya han mencionado

2 Puede rellenarlos y validarlos desde fuentes externas abiertas o cuerpos de estándares. Por ejemplo, para países es la norma internacional ISO3166.

3 En sus versiones actuales o futuras de su aplicación, incluso puede conectarse directamente a fuentes externas para mantenerlas.

4 Si alguna vez va en varios idiomas, ya tendrá los nombres para traducir todo en un solo lugar.

5 Si alguna vez intercambias o conectas datos con otras partes o aplicaciones, necesitarás las clasificaciones comunes

Answer 6

El problema con {país, estado, ciudad} es que parecen ser una clave candidata para la tabla a la que se hace referencia. En SQL, {país, estado, ciudad} no puede ser una clave candidata (ni siquiera una clave principal), si puede faltar el estado (o país) o NULL. (Esto podría evitarse permitiendo una cadena vacía para ellos, que es diferente de NULL, pero sería un truco feo, OMI). Lo mismo iría para el código postal, que solo podría convertirse en una clave candidata agregándole un country . . Y ambos pueden faltar, ser desconocidos o NULL.

La única forma de evitar las claves candidatas inutilizadas sería degradarlas a índices (no únicos) y agregar una clave principal sustituta, como en:

CREATE TABLE cities
    ( city_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_name varchar -- you _could_ squeeze this out into a separate "countries" table
    , state_name varchar   -- you could even squeeze this out, but it would need a composite FK
    , city_name varchar NOT NULL
    );

CREATE TABLE adresses
    ( person_id INTEGER NOT NULL PRIMARY KEY -- could be a serial
    , last_name varchar NOT NULL
    , first_first_name varchar
    , gender CHAR(1)
    , dob DATE
    , city_id INTEGER references cities(city_id) -- could be NOT NULL
    );

WRT {city,state} : podría exprimirlos en una tabla de unión (esto básicamente es un problema de BCNF, quizás incluso un problema de 4NF, si todos los campos de unión no fueran NULABLES) como en:

    --
    -- Plan B:
    --
CREATE TABLE country2
    ( country_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_name varchar NOT NULL
    , country_iso varchar
    -- ...
    , UNIQUE (country_name)
    );

CREATE TABLE country_state2
    ( cs_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_id INTEGER NOT NULL REFERENCES country2(country_id)
    , state_name varchar
    );
CREATE TABLE cities2
    ( city_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , cs_id INTEGER REFERENCES country_state2(cs_id)
    , city_name varchar NOT NULL
    );

CREATE TABLE adresses2
    ( person_id INTEGER NOT NULL PRIMARY KEY -- could be a serial
    , last_name varchar NOT NULL
    , first_first_name varchar
    , gender CHAR(1)
    , dob DATE
    , city_id INTEGER references cities2(city_id) -- could be NOT NULL
    );

Si realmente debe hacer esto es una cuestión de gusto (vea la respuesta de @Joel Brown). La normalización sin duda ayudaría en caso de operaciones masivas de cambio de nombre, como la fusión de los municipios en la OQ. Para conjuntos pequeños de direcciones (hasta unos pocos miles), la complejidad adicional probablemente costaría más de lo que ganaría. Esta complejidad es particularmente costosa para las aplicaciones frontales que se utilizan para mantener los datos. Para el DBMS, unas cuantas combinaciones no costarían mucho (para tamaños pequeños) e incluso podrían ayudar al rendimiento (para tamaños más grandes). La normalización no es mala para el rendimiento.

ACTUALIZACIÓN (después del comentario de Mike Sherill catcall):

Si pudiéramos imponer restricciones NOT NULL en {país, estado, ciudad} (o hay ids), también podríamos imponer restricciones ÚNICAS en las claves candidatas (compuestas) de las que forman parte: - Plan C: - CREAR TABLA country3 (country_id INTEGER NOT NULL PRIMARY KEY - podría ser una serie ..., country_name varchar NOT NULL, country_iso varchar, UNIQUE (country_name));

CREATE TABLE country_state3
    ( cs_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_id INTEGER NOT NULL REFERENCES country3(country_id)
    , state_name varchar NOT NULL
    , UNIQUE (country_id,state_name)
    );
CREATE TABLE cities3
    ( city_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , cs_id INTEGER NOT NULL REFERENCES country_state3(cs_id)
    , city_name varchar NOT NULL
    , UNIQUE (cs_id,city_name)
    );

CREATE TABLE adresses3
    ( person_id INTEGER NOT NULL PRIMARY KEY -- could be a serial
    , last_name varchar NOT NULL
    , first_first_name varchar
    , gender CHAR(1)
    , dob DATE
            -- allowing NULL here allows for ''embryonic'' records without city/state/country info.
    , city_id INTEGER references cities3(city_id)
    );

Aunque esta restricción NOT NULL evitará duplicados en {ciudad, estado, país}, también los impondrá a NOT NOT NULL, obviamente. Esto podría ser imposible o no válido en otros países (que no sean Canadá o los EE. UU.). En los Países Bajos, no tenemos state o county ; tenemos provincie , que apenas se usa (solo para desambiguar, si es necesario) Similar para los departements franceses, IIRC.

Answer 7

Normalizar la dirección en una jerarquía es una proposición cuestionable. Realmente depende de lo que quieras hacer con tus datos de dirección.

La idea de normalizar para evitar anomalías de actualización es un poco dudosa. ¿Con qué frecuencia las ciudades, estados o países cambian realmente de nombre? Además, si esto sucediera, ¿qué tan probable sería que el cambio fuera al por mayor? (Es decir, cada instancia del antiguo nombre X cambia al nuevo nombre Y). Puedo decirles que lo que sucedió en la práctica en Canadá cuando hubo una avalancha de amalgamas municipales en la década de 2000 fue que se rediseñaron los límites y que muchos nombres antiguos se quedaron, solo con territorios más pequeños que antes.

El hecho es que cosas como los nombres de los municipios pueden definirse libremente. Por ejemplo, donde crecí, mi dirección tenía tres nombres de municipios oficialmente reconocidos de acuerdo con la autoridad postal: WILLOWDALE, NORTH YORK, TORONTO, todos ellos eran opciones válidas, aunque una era "más oficial" que las otras. El problema es que todo Willowdale está en North York, pero North York también contiene "Downsview" y otros.

Otros argumentos frecuentes para normalizar las direcciones incluyen: garantizar la ortografía adecuada y proporcionar una base para la gestión del territorio. Dados los caprichos de la calidad de los datos de direcciones, estos argumentos no son convincentes.

La mejor manera de garantizar la calidad de los datos de las direcciones es mantener sus direcciones en una estructura relativamente simple y relativamente plana, y emplear una o más herramientas de calidad de direcciones que utilicen datos de autoridad postal para comparar y estandarizar sus direcciones. Mantenga la ciudad, el estado y el código postal en sus propios campos, por todos los medios, pero no los mantenga en tablas distintas. En realidad, esto es más flexible que una estructura normalizada y produce resultados más confiables en general.

Del mismo modo, la gestión del territorio se realiza mejor a un nivel más granular que el municipio. Algunos municipios son enormes y los nombres pueden ser ambiguos. En su lugar, utilice un código postal o ZIP + 4 (según la jurisdicción). Esto es mucho más granular e inequívoco. Nuevamente, una herramienta de calidad de datos de dirección garantizará que tenga la codificación postal correcta en sus direcciones.

Answer 8

Sí, casi seguro. Si un país o ciudad cambia un nombre, lo cambia en un lugar y todas las referencias se actualizan automáticamente.

La división también le permite agregar otros atributos a un país o ciudad, es decir, el continente en el que se encuentra, etc. No puede hacer esto fácilmente sin una tabla separada.

Finalmente, si desea una lista de países (para rellenar un cuadro de lista, por ejemplo), tiene un solo lugar al que hacer referencia. (De lo contrario, terminaría haciendo algo de SELECT DISTINCT en la tabla de personas, lo cual es dudoso).

Answer 9

Yo diría que sí, pero solo por ciudad / estado / país a menos que esté planeando analizar / agrupar personas por sus nombres.

Cree índices en las columnas de identificación que resulten, y también en las columnas de texto en las tablas de búsqueda. Esto resultará en una creación más fácil de las opciones desplegables para formularios y también en tiempos de búsqueda más rápidos cuando el tamaño de su base de datos aumenta.

Esto también acelerará el tiempo de escritura del registro si está indexando la columna ciudad / estado / país, ya que una escritura de índice numérico corto es mucho más rápida que un índice de texto completo.

Answer 10

• Si esta es una base de datos relativamente pequeña y planea permitir que el usuario ingrese la dirección por sí mismo, debe dejar la tabla tal como está. Aunque aumentará el tamaño de la tabla (en bytes), debido al almacenamiento duplicado de los nombres de ciudades, estados y países.

• Si esta es una base de datos relativamente grande y desea que el usuario seleccione los nombres de ciudad, estado y país de una lista, entonces necesita separar estas columnas a otra tabla. Además, para que esto funcione, debe rellenar esta tabla usted mismo. Las ventajas serían una tabla más pequeña para usuarios y direcciones también.