Le tabelle di auto-referenziazione sono una buona idea per i dati geografici?

Ci sono almeno due aspetti del design del database che la tua domanda tocca.

Il primo è il problema del design classe / sottoclasse. Qual è il miglior design per classi e sottoclassi durante la progettazione di un database relazionale? (Questo problema è anche noto come tipi e sottotipi o generalizzazione e specializzazione).

Il secondo è il problema di rappresentare una gerarchia nei dati relazionali. Qual è il miglior design per rendere gli inserimenti facili e veloci? Qual è il miglior design per rendere le query facili e veloci, specialmente per cercare la sottostruttura.

Questi due sembrano lo stesso problema, ma non lo sono. Esistono situazioni in cui derivano sottoclassi, ma non esiste una gerarchia (eccetto per la gerarchia dell'ereditarietà). Ci sono altre situazioni che sorgono dove c'è una gerarchia, ma tutti i partecipanti sono di una classe, per quanto riguarda il progettista.

Per quanto riguarda le classi e sottoclassi, ti consiglio di esaminare il trattamento di Martin Fowler per "l'ereditarietà delle tabelle", in particolare "ereditarietà delle tabelle singole" e "ereditarietà delle tabelle delle classi". Ci sono vantaggi e svantaggi per ogni scelta.

Per quanto riguarda la rappresentazione di una gerarchia, ti consiglio di esaminare "modello lista di adiacenza" e "modello di serie annidato". La lista di adiacenza è così semplice che la maggior parte dei neofiti apprende la tecnica senza mai imparare il suo nome. Gli insiemi nidificati sono meno noti e sono utili solo in alcune circostanze speciali. Permette, tuttavia, di attraversare una sottostruttura senza ricorrere alla ricorsione.

Non ho intenzione di entrare più nel dettaglio qui, perché non è chiaro se sei interessato a entrambi questi problemi o solo a uno di essi.

Sì può essere, ma fai attenzione a quanto segue:

• Query ricorsive (ad esempio, in Sql Server, espressione di tabella comune (CTE)
• Ricorsione nel codice (oggetti autoreferenziali)

Entrambi possono essere difficili da capire, implementare e supportare.

Per le tue query, non sarà un semplice

Select * from Table

tipo di query. Inoltre, molto probabilmente il modello di oggetto nel codice avrà oggetti autoreferenziali che richiederanno una struttura radice e albero / nodo da supportare.

Tuttavia, posso dire che questo modello è appropriato. In un progetto passato abbiamo implementato un modello geografico autoreferenziale di buon successo. Per precauzione, quando abbiamo intervistato i candidati, avevamo una domanda sul codice di ricorsione per assicurarci che potessero gestire il codice e il modello poiché non tutti hanno familiarità con questo tipo di struttura dei dati.

Una volta ho lavorato su un sistema che modellava dati simili. Non geografico, ma gerarchico, in cui la gerarchia non era necessariamente ben definita e le entità potevano "saltare" i livelli: A - > B - > C era altrettanto valido di A - > C.

L'uso di tabelle separate è ottimo quando puoi garantire che i dati corrispondano alla struttura, e infatti deve corrispondere alla struttura. Tuttavia, sembra che non sia il caso qui.

Cose da tenere a mente:

1. Interrogare un'intera gerarchia da foglia a radice non può essere fatto facilmente. SQL Server ha CTE e Oracle ha query gerarchiche, ma il i dati non sono in uno stato coerente nel modo in cui un livello ORM si aspetterebbe: hai arbitrariamente molti record di tipi arbitrari non definiti dalla tabella stessa ma da uno dei suoi campi. In altre parole, il tipo di un record è definito dal record stesso, non dalla tabella in cui risiede.
2. Altre aree di un'applicazione richiedono un'attenzione particolare. Non è semplice come afferrare il campo "stato" o "codice postale" e collegarlo a un elemento dell'interfaccia utente, devi guardare una coppia codice / valore per determinare che cosa rappresenta il dato. È necessaria una logica aggiuntiva per inserire i dati dal database nella posizione appropriata per il modello e la vista.
3. I join diventano quasi impossibili da eseguire correttamente in tutti i casi che non sarebbe vero per la soluzione tabella separata strongmente tipizzata.

Viste le informazioni nella tua domanda, lo farei in questo modo, ma tieni presente che introduce complessità che devono essere considerate e gestite.