with tutorial the para latest framework español applications database-design

database design - tutorial - ¿Cómo diseñar mejor las ubicaciones de direcciones en cualquier base de datos SQL?



the django project (5)

Visión de conjunto

Estoy trabajando en una aplicación de informes y mapeo de Servicios de Emergencia para California (algo raro, considerando los incendios allí, en este momento ...). Necesitamos mapear datos demográficos y de emergencia para una unidad gubernamental interna.

Lo que tenemos son todas las calles, ciudades y vecindarios en California. Cada vecindario también tiene su shapefile relevante (lat de largo que define sus límites). Esto nos lo dio el sitio web de la Junta de Censo de los Estados Unidos (todo el material de dominio público).

Problema

No estoy seguro de cómo diseñar mejor las tablas de DB. No nos han dicho qué tipo de base de datos necesitamos usar ... por lo que estamos abiertos a sugerencias si eso ayuda. Tenemos experiencia con MS SQL 2005 y 2008 (y lo espacial en ''08).

Podemos tener los siguientes datos legítimos.

  • Calle, Ciudad, Estado
  • Estado de la Ciudad
  • Vecindario, estado
  • Estado

La razón por la cual el Estado es un lugar legítimo es porque nos dicen que esto podría venderse a otros estados, por lo que debemos planearlo ahora.

Entonces, originalmente, pensé en esto ...

  • LocationId INTEGER PK Identity
  • Calle NVARCHAR (100)
  • Barrio NVARCHAR (100)
  • Ciudad NVARCHAR (100)
  • NVARCHAR del estado (100)
  • Latitude VARCHAR (15)
  • Longitud VARCHAR (15)
  • Shapefile

Ninguno de ellos es nulo, por cierto. Pero después de un tiempo corto, pensé que era un desperdicio tener tantos textos de ''California'' o ''San Diego'' en los campos. Así que cambié la tabla para estar más normalizado haciendo que los campos Vecindario, Ciudad y Estado sean una clave externa para su nueva tabla (por ejemplo, búsquedas) ... y esos dos campos ahora son NULLABLE.

Entonces ... todo funciona bien. excepto cuando intento hacer algunas declaraciones Sql sobre ellos. Debido a los FK NULLABLE, es una pesadilla hacer todas estas consultas de combinación externa :(

¿Qué tal tener la tabla principal, las tablas de búsqueda secundaria (por ejemplo, Vecindarios, Ciudades y Estados) vinculadas a través de ID y luego colocar todo esto en una vista? Recuerde, NeighborhoodID y CitiyID serían NULLABLE .. ???

Solo quiero ver las opiniones de las personas sobre esto y las razones por las que hicieron sus sugerencias, por favor. Estoy realmente preocupado y confundido, pero estoy ansioso por aprender.

¡Por favor ayuda!

edición 1: necesito adherirme a una base de datos RDBMS.

edición 2: estoy pensando en ir una sola tabla (desnormalizada) con restricciones para mantener la suma de los campos UNO o Múltiples tablas con FK nulos en la tabla principal (por ejemplo, Ubicaciones (tabla principal), Barrios, Ciudades , Estados ... esquema db normalizado).

edición 3: Ciudad añadida a la muestra, segunda lista.

edición 4: pregunta de vista añadida.

¿Es esto un sistema OLTP y un sistema de informes o solo un sistema de informes? Si solo se trata de un sistema de informes, puede desnormalizar los datos en forma de almacén de datos (con dimensiones de copo de nieve o no para las jerarquías de las jurisdicciones geográficas) y encontrará que los informes son más fáciles.

Comenzaría a partir de los resultados y retrocederé, porque me parece que te están alimentando con los datos y estás tratando de llevarlos a una base de datos para respaldar los informes y el mapeo. En este caso, el esquema de la base de datos que es un sistema tradicional normalizado no es importante porque la redundancia en los datos no es algo que cause problemas de mantenimiento a los usuarios, etc.

Si esto parece apropiado, desea ver los libros de Kimball.

Como señaló @Oddthinking en un comentario, sus problemas comenzaron en:

Así que cambié la tabla para que se normalizara haciendo que los campos Vecindario, Ciudad y Estado fueran una clave externa para su nueva tabla (por ejemplo, búsquedas) ... y esos dos campos ahora son NULLABLE.

Entonces ... todo funciona bien. excepto cuando intento hacer algunas declaraciones SQL en ellos. Debido a los FK NULLABLE, es una pesadilla hacer todas estas consultas de combinación externa.

Esto me recuerda al "Doctor, doctor, duele cuando me golpeo así".

¿Por qué exactamente hizo que los campos de clave foránea fueran nulables? Antes eran obligatorios, por lo que debe mantenerlos como obligatorios, precisamente para evitar las pesadillas de las consultas de combinación externa.

Su explicación (pregunta) es algo confusa en cuanto a que enumera tres campos (Vecindario, Ciudad y Estado) y luego dice "esos dos campos ahora son nulables". ¿Cuáles son? ¿Y por qué? ¿Y qué hay en la tabla de búsqueda? ¿O hay más de una tabla de búsqueda? Puede haber un argumento para algún tipo de número de NeighbourhoodID que es una clave externa a una tabla de Vecindario, que define la ciudad y el estado, así como el nombre de Vecindario. A continuación, puede decidir que hay una lista cerrada de ciudades y las ciudades también tienen un número de identificación, y ese número también determina el estado. Probablemente estés tan bien usando un código de estado de dos letras como para crear un número de identificación de estado (probablemente 4 bytes). Sin embargo, no olvide que un criterio de verificación que asegure que el código de estado es uno de los aproximadamente 50 códigos de estado válidos es más difícil de escribir que una clave externa que hace referencia a una tabla de estados. Como ni los estados ni las ciudades cambian con mucha frecuencia, probablemente usaría la tabla de estados con una clave externa, pero la columna clave sería el código de estado.

Eso significa que puede tener una tabla de Vecindarios con columnas NeighbourhoodID, Name, CityID; una tabla de Ciudades con columnas CityID, Name, State; y una tabla de Estados con columnas Estado y Nombre. Puede agregar otras columnas como mejor le parezca. Y su tabla principal contendría una columna NeighbourhoodID que es una clave externa a la tabla Vecindarios.

Este es un problema con el que he tenido que lidiar y los sistemas RDBMS no son los mejores para almacenar datos jerárquicos. Es posible que desee ver el uso de una base de datos de objetos, ya que estos tienen que tratar con objetos anidados y están optimizados para el problema.

Si necesita usar un RDBMS, puede que tenga que seguir con un esquema desnormalizado. Sin embargo, tener tablas separadas para mantener sus ciudades, calles, etc. puede ser útil para rastrear los cambios. Si es necesario renombrar una ciudad o calle, puede actualizar el registro maestro en la tabla respectiva y programar un trabajo para actualizar una copia de texto de la cadena en su tabla ''principal''. Esto evitará tener que ejecutar actualizaciones en 10''s 100 de miles de filas durante el horario de máxima audiencia, pero aún así le permite almacenar la información más actualizada en el archivo db. Por supuesto, esto hace que la situación de duplicación de datos sea aún peor, pero es el precio a pagar por el rendimiento.

Tomando el ejemplo:

  • Calle, Ciudad, Estado
  • Estado de la Ciudad
  • Vecindario, estado
  • Estado

En primer lugar, regrese a los principios básicos, todas las anteriores son entidades geoespaciales distintas, por lo que su dirección se compone de un nombre y uno o varios especificadores geoespaciales. Esto nos dice que realmente deberíamos almacenarlos en una sola tabla. La clave aquí es pensar en los datos de manera más abstracta,

Por lo tanto, su tabla de direcciones necesita una relación 1-many con otra tabla, llamada address_entities, que es la siguiente:

  • int ID
  • nombre varchar ()
  • tipo varchar ()
  • int parentID
  • posición geográfica
  • int parentID

Esto significa que obviamente necesitará una tabla para vincular la dirección a la tabla de entidades de direcciones anterior. Ahora, cada entidad geoespacial es intrínsecamente jerárquica, y aunque hace que el SQL sea más difícil, y personalmente trato de evitar las tablas de autorreferencia, hay ocasiones en que es una buena solución y esta es una de ellas.

Los beneficios son enormes, a pesar de que hace que el código sea más difícil, vale la pena a largo plazo.

Además, incluso cuando no es un requisito inmediato, piense globalmente, no todas las direcciones en el mundo tienen una calle, o un estado, por ejemplo, en Francia una dirección válida podría ser

- la Maison des Fou - 24500 Eymet

Por lo tanto, tenlo en cuenta al diseñar esquemas.