Comment mieux traiter les emplacements dans toute la Base de données SQL?

Aperçu

Je travaille sur certains Services d'Urgence, les rapports et l'application de cartographie pour la Californie (sorte de bizarre, en considérant les feux de là, maintenant...). Nous avons besoin de la carte démographiques et des données d'urgence interne d'un service gouvernemental.

Ce que nous avons sont tous les rues, les villes et les quartiers en Californie. Chaque quartier a aussi pertinentes du shapefile (lat long qui définit ses limites). Cela nous a été donnée par le Recensement AMÉRICAIN de conseil (tous du domaine public genre de trucs) site web.

Problème

Je ne suis pas sûr de la façon de concevoir au mieux les tables DB. Nous n'avons pas dit de quel type de DB, nous devons utiliser .. donc nous sommes ouverts aux suggestions si cela aide. Nous avons de l'expérience avec MS SQL 2005 et 2008 (et spatiale des trucs en '08).

On peut avoir de la suite de la pièce de théâtre de données.

  • Rue, Ville, État
  • La Ville, L'État
  • Quartier, État
  • État

La raison pour laquelle l'État est une pièce de théâtre emplacement est parce que nous nous sommes dit que cela pourrait être vendu à d'autres états, de sorte que nous avons besoin de planifier pour que maintenant.

Donc, à l'origine, j'ai pensé à cela...

  • LocationId ENTIER PK Identité
  • De rue de type NVARCHAR(100)
  • De voisinage de type NVARCHAR(100)
  • Ville de type NVARCHAR(100)
  • État de type NVARCHAR(100)
  • Latitude de type VARCHAR(15)
  • Longitude de type VARCHAR(15)
  • Shapefile

Aucune de ces sont les valeurs null, btw. Mais après un court moment, j'ai pensé que c'était un gaspillage d'avoir autant de "la Californie", de texte ou de "San Diego" de texte dans les champs. J'ai donc modifié la table pour être plus normalisée en faisant le Quartier, de la Ville et de l'État des champs de clé étrangère à leur propre table (eg. les recherches) .. et ces deux champs sont maintenant NULLABLE.

Donc .. que tout fonctionne bien. sauf que quand j'ai essayer et faire un peu de Sql sur eux. En raison de la NULLABLE FK, c'est un cauchemar pour rendre toutes ces requêtes de jointure externe 🙁

Ce sujet d'avoir la table principale, les sous-tables de recherche (par exemple. De quartiers, de Villes et d'États) liés par l'intermédiaire de l'ID, puis placez le tout dans un point de vue? Rappelez-vous, NeighborhoodID et CitiyID serait NULLABLE.. ???

Je veux juste voir la tête des gens sur ce et de la raisons ils ont fait part de leurs suggestions, s'il vous plaît. Je suis vraiment inquiet et confus, mais sont désireux d'apprendre.

S'il vous plaît aider!

edit 1: j'ai besoin de s'en tenir à une Base de données SGBDR.

edit 2: je suis en train de penser à aller une seule table (de-normalisée) avec des contraintes de garder la somme des champs uniques OU multi-tables avec nullable FK est sur la table principale (par exemple. Les emplacements (tableau principal), les Quartiers, les Villes, les États ... normalisé db schéma).

edit 3: Ajout d'une Ville à l'échantillon, la seconde liste.

edit 4: Ajout d'une vue sur la question.

La version normalisée semble le plus correct pour moi. Ce qui vous préoccupe concernant les requêtes? Ne pouvez-vous pas juste créer une vue qui ne les jointures pour vous une fois et ensuite requête?
Correct. j'ai un certain nombre de jointures externes lorsque les données sont normalisées. Auparavant dans d'autres projets de très de très similaire à cela, il a été une douleur dans le dos afin d'obtenir performant requêtes .. surtout quand je veux "bulle" le heirachy.
Enfin, j'ai appris à éviter les points de vue parce que j'ai été dit, ils peuvent être VRAIMENT onu-performante, spécialement sur multi-tables qui vont jusqu'à des millions et des millions de lignes....
Pourquoi avez-vous changé de Ville et de l'État à accepter les valeurs null quand ils sont devenus les clés étrangères?
Si vous utilisez MS SQL, vous avez la possibilité de vues indexées qui peut aider: microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

OriginalL'auteur Pure.Krome | 2008-11-18

  1. 4

    Prenant l'exemple:

    • Rue, Ville, État
    • La Ville, L'État
    • Quartier, État
    • État

    Tout d'abord de revenir aux principes de base, tous les ci-dessus sont distincts géospatiales entités, de sorte que votre adresse est composé d'un nom et d'un ou de plusieurs géospatiales prescripteurs. Cela nous dit que nous ne devrions vraiment être de les stocker dans une table unique. La clé ici est de penser les données de manière plus abstraite,

    De sorte que votre adresse de tableau a besoin d'un 1-nombre de relation à l'autre table, appelée address_entities qui est comme suit:

    • int ID
    • varchar() nom
    • varchar() type
    • int parentID
    • position géographique.
    • int parentID

    Cela signifie que vous aurez évidemment besoin d'un tableau pour établir le lien de l'adresse à l'adresse de l'entité tableau ci-dessus. Maintenant, chaque géospatiales entité est intrinsèquement hiérarchique, et alors qu'il fait le SQL plus difficile, et personnellement, j'essaie d'éviter l'auto font référence à des tables il y a des moments où c'est une bonne solution et c'est l'un d'entre eux.

    Les avantages sont énormes, même si elle rend le code plus difficile, il vaut la peine dans le long terme.

    Aussi, même quand ce n'est pas un besoin immédiat, penser à l'échelle mondiale, et non pas toutes les adresses dans le monde ont une rue, ou de l'état, par exemple,en france, une adresse valide pourrait être

    - la Maison des Fou
- 24500 Eymet

    Donc, gardez cela à l'esprit lors de la conception de schémas.

    Également utiliser NVARCHAR pour les noms, au moins pour les gars dans le sud de l'entre NOUS qui vivent dans les villes mexicaines noms...

    OriginalL'auteur Richard Harrison

  2. 2

    @Oddthinking a fait remarquer dans un commentaire, votre les problèmes ont commencé:

    J'ai donc modifié la table pour être plus normalisée en faisant le Quartier, de la Ville et de l'État des champs de clé étrangère à leur propre table (eg. les recherches) .. et ces deux champs sont maintenant NULLABLE.

    Donc .. que tout fonctionne bien. sauf que quand j'ai essayer et faire un peu de SQL sur eux. En raison de la NULLABLE FK, c'est un cauchemar pour rendre toutes ces requêtes de jointure externe.

    Cela me rappelle le "docteur, Docteur, ça fait mal, quand j'appuie sur moi, comme cette" plaisanterie.

    Exactement pourquoi avez-vous fait du champs de clé étrangère nullable? Ils étaient obligatoires avant, de sorte que vous devriez garder leur caractère obligatoire, précisément pour éviter les cauchemars de jointure externe requêtes.

    Votre explication (question) est un peu déroutant que vous énumérez trois champs (Quartier, Ville et État) et de dire: "ces deux champs sont maintenant nullable". Dont deux sont? Et pourquoi? Et ce qui est dans la table de recherche? Ou est-il plus d'une table de recherche? Il y a peut être un argument pour une sorte de NeighbourhoodID nombre qui est une clé étrangère d'une table de Voisinage, qui définit la Ville et de l'État ainsi que de Quartier de nom de. Vous pouvez alors décider qu'il y a une liste fermée de villes et les villes ont un numéro d'identification trop, et ce nombre détermine l'état. Vous êtes probablement aussi bien à l'aide d'une à deux lettres du code de l'état comme la création d'un (probablement 4 octets) etat numéro d'identification. Cependant, n'oubliez pas que le critère de contrôle qui garantit que le code d'état est l'un des 50 valide des codes d'état est plus difficile à écrire que d'une clé étrangère qui fait référence à un tableau de l'état. Étant donné que ni les états, ni les villes change très souvent, je serais probablement utiliser la table des états avec une clé étrangère - mais la colonne de la clé serait le code de l'état.

    Cela signifie que vous pouvez avoir une table de Quartiers avec des colonnes NeighbourhoodID, Nom, CityID; un tableau de Villes avec des colonnes CityID, le Nom, l'État; et une table d'États avec des colonnes de l'État et le Nom. Vous pouvez ajouter d'autres colonnes que vous voyez l'ajustement. Et votre table principale contient un NeighbourhoodID colonne est une clé étrangère vers les Quartiers de la table.

    sorte de ... de Quartiers, les Villes et les États ont leurs propres tables de choix => ID et la Description. Simple. Dans la table principale, il fait référence à ces tables de recherche. MAIS, certaines de ces références peut être null - eg. Quartier, État == pas de la rue de texte et pas de ville id - donc, les valeurs null.
    Je ne comprends toujours pas comment, dans la non-normalisé de la structure, les champs étaient tous de ne pas prendre la valeur null, et pourtant soudainement devenu nullable après la normalisation. Qu'avez-vous dans le pas nullable champs quand il n'y avait pas de ville?

    OriginalL'auteur Jonathan Leffler

  3. 1

    C'est un bon endroit pour commencer. Ensemble #$(#$-charge de schémas de base de données pour vérifier:

    http://www.databaseanswers.org/data_models/

    Non, rien ne m'a aidé. Aussi, je suis après quelques-unes des raisons suggéré dessins sont suggérées. pas juste des réponses. Nous avons besoin d'apprendre, ici.
    Eh, c'est une bonne liste, tout de même.

    OriginalL'auteur TAG

  4. 1

    Ce un problème que j'ai eu à traiter avec et systèmes SGBDR ne sont pas les meilleurs au stockage de données hiérarchiques. Vous voudrez peut-être regarder à l'aide d'un objet de base de données puisque celles-ci ont à traiter avec des objets imbriqués et sont optimisés pour le problème.

    Si vous avez besoin d'utiliser un SGBDR, vous pouvez avoir à coller avec de schéma normalisé. Avoir des tables distinctes pour maintenir vos villes, rues, etc peut être utile pour le suivi des modifications. Si une ville ou d'une rue doit être renommé, vous pouvez mettre à jour la fiche dans la table correspondante et planifier un travail de mise à jour d'un texte copie de la chaîne dans votre "main" de la table. Cela vous évitera d'avoir à exécuter des mises à jour sur 10 100 de milliers de lignes, en prime time, mais encore permet de stocker le plus de données dans la db. Bien sûr, cela fait de la duplication des données de la situation encore pire, mais c'est le prix à payer pour la performance.

    J'ai besoin de s'en tenir à un RDMBS. Pourquoi vous suggère de rester à une de-normailized schéma sur une seule table?
    Dans l'application, j'ai travaillé sur, nous ne descendit la fermeture éclair et le volume de données s'est un peu grand. En dé-normaliser, je veux dire un schéma avec moins de tables, peut-être avec certaines données répétées dans chaque. Cela permettra de limiter le nombre de jointures que vous avez à faire, mais laissez-vous vous en tenez à quelques db principes de conception.
    J'ai mis à jour ma réponse pour être plus précis.
    +1 je suis d'Accord de Normalisation est l'idéal, mais dans le monde réel Dénormalisée de données rend la vie plus facile à la fois. Cependant, dans le plus Pur.Krome est le cas du modèle de données semble assez simple, et les données limitées assez (je suppose que c'est tout au plus quelques millions d'entrées?) qu'il pourrait aussi bien être normalisé.
    Actuellement, nous avons 1 1/2 mill, mais cela pouvait aller jusqu'à environ 10 moulin bizarre?

    OriginalL'auteur Dana the Sane

  5. 0

    Est-ce un système OLTP et un système d'information ou seulement un système de reporting? Si c'est uniquement un système d'information, vous pouvez éliminer les données dans un entrepôt de données de la mode (avec le flocon de neige dimensions ou non de la hiérarchie similaire géographique de territoires) et vous trouverez les rapports pour être plus facile.

    Je commencerais les résultats et le travail, car il me semble que vous êtes l'obtention de la fed, les données et que vous tentez de l'amener dans une base de données à l'appui de la déclaration et de la cartographie. Dans ce cas, le schéma de base de données traditionnel normalisé système n'est pas important car la redondance dans les données n'est pas quelque chose qui va causer des problèmes de maintenance pour les utilisateurs, etc.

    Si cela semble approprié, vous voulez regarder dans la Kimball livres.

    OriginalL'auteur Cade Roux