Qual è il modo migliore per implementare Polymorphic Association in SQL Server?

Ho un sacco di casi in cui ho bisogno di implementare una sorta di associazione polimerica nel mio database. Io spreco sempre un sacco di tempo pensando a tutte le opzioni da capo. Qui ci sono i 3 a cui riesco a pensare. Spero ci sia una buona pratica per SQL Server.

Ecco l’approccio a più colonne

Approccio a colonne multiple

Ecco l’approccio senza chiave straniera

Nessun approccio a chiave estera

Ed ecco l’approccio alla tabella di base

Approccio alla tabella di base

I due approcci più comuni sono Tabella per Classe (cioè una tabella per la class base e un’altra tabella per ogni sottoclass che contiene le colonne aggiuntive necessarie per descrivere la sottoclass) e Tabella Per Gerarchia (cioè tutte le colonne in una tabella, con una o più colonne per consentire la discriminazione delle sottoclassi. Qual è l’approccio migliore dipende in realtà dai particolari della vostra applicazione e dalla strategia di accesso ai dati.

Avresti Tabella per Classe nel tuo primo esempio invertendo la direzione dell’FK e rimuovendo gli ID extra dal genitore. Gli altri due sono essenzialmente varianti di tabella per class.

Un altro nome comune per questo modello è il modello Supertype, in cui uno ha un set di base di attributi che possono essere espansi tramite l’unione con un’altra quadro. Nei libri Oracle, viene insegnato sia come modello logico che come implementazione fisica. Il modello senza relazioni consentirebbe ai dati di crescere in stati non validi e in record orfani, per convalidare fortemente le esigenze prima di selezionare quel modello. Il modello principale con la relazione memorizzata nell’object base genererebbe valori nulli e, nel caso in cui i campi si escludessero a vicenda, avresti sempre un valore nullo. Il diagramma in basso in cui viene applicata la chiave nell’object figlio eliminerebbe i valori nulli ma renderebbe anche la dipendenza una dipendenza morbida e consentirà agli orfani se la sovrapposizione non venisse applicata. Penso che valutare questi tratti ti aiuterà a selezionare il modello che si adatta meglio. Ho usato tutti e tre in passato.

Ho usato la seguente soluzione per risolvere un problema simile:

Disegno basato su Molti-Molti: anche se la relazione è 1-Molti tra un ObjectN e Something, è equivalente a una relazione Many-Many con una modifica del PK della tabella delle relazioni.

Per prima cosa creo una tabella di relazioni tra ObjectN e Something per Object e quindi utilizzo la colonna Something_ID come PK.

Questo è il DDL della relazione Something-Object1 che è lo stesso per Object2 e Object3:

CREATE TABLE Something ( ID INT PRIMARY KEY, ..... ) CREATE TABLE Object1 ( ID INT PRIMARY KEY, ..... ) CREATE TABLE Something_Object1 ( Something_ID INT PRIMARY KEY, Object1_ID INT NOT NULL, ...... FOREIGN KEY (Something_ID) REFERENCES Something(ID), FOREIGN KEY (Object1_ID) REFERENCES Object1(ID) ) 

Maggiori dettagli ed esempi di altre possibili opzioni in questo ticket più-chiavi estranee-per-stessa-regola-commerciale

Secondo me il tuo primo tipo di approccio è il modo migliore per definire i dati e le tue lezioni, ma come tutti i dati primari dovrebbero essere disponibili per il bambino.

In questo modo puoi verificare i tuoi requisiti e definire il Database.

Ho usato quello che immagino chiameresti l’approccio al tavolo base. Ad esempio, avevo tabelle per nomi, indirizzi e numeri di telefono, ognuno con un’id quadro come PK. Poi ho avuto un’entity framework tabella quadro principale (entityID) e una tabella di collegamento: attributo (entityKey, attributeType, attributeKey), in cui attributeKey poteva puntare a una qualsiasi delle prime tre tabelle, a seconda di attributeType.

Alcuni vantaggi: consente di aggiungere tanti nomi, indirizzi e numeri di telefono per quadro, aggiungere facilmente nuovi tipi di attributi, normalizzazione estrema, attributi comuni facili da individuare (ad es. Identificare persone duplicate), altri vantaggi di sicurezza specifici del business

Svantaggi: query abbastanza complesse per creare set di risultati semplici hanno reso difficile la gestione (ovvero ho avuto problemi nell’assumere persone con un buon numero di costanti T-SQL); le prestazioni sono ottimali per casi d’uso MOLTO specifici piuttosto che generali; l’ottimizzazione delle query può essere complicata

Avendo vissuto con questa struttura per diversi anni per una carriera molto più lunga, avrei esitato ad usarlo di nuovo a meno che non avessi gli stessi strani vincoli logici di business e schemi di accesso. Per l’utilizzo generale, raccomando vivamente che le tue tabelle digitate facciano riferimento direttamente alle tue entity framework. Cioè Entità (entityID), Nome (NameID, EntityID, Name), Phone (PhoneID, EntityID, Phone), Email (EmailID, EntityID, Email). Avrai alcune ripetizioni di dati e alcune colonne comuni, ma sarà molto più facile da programmare e ottimizzare.

L’approccio 1 è il migliore, ma l’associazione tra qualcosa e object1, object2, object3 dovrebbe essere uno a uno.

Voglio dire FK nella tabella figlio (object1, object2, object3) dovrebbe essere chiave univoca non nullo o chiave primaria per la tabella figlio.

object1, object2, object3 possono avere valore dell’object Polymorphic.

Non esiste una pratica migliore singola o universale per raggiungere questo objective. Tutto dipende dal tipo di accesso di cui avranno bisogno le applicazioni.

Il mio consiglio sarebbe di fare una panoramica sul tipo atteso di accesso a queste tabelle:

  1. Utilizzerai un livello OR, stored procedure o SQL dinamico?
  2. Quale numero di record ti aspetti?
  3. Qual è il livello di differenza tra le diverse sottoclassi? Quante colonne?
  4. Farai aggregazioni o altri rapporti complessi?
  5. Avrai un data warehouse per la segnalazione o no?
  6. Avrai spesso bisogno di elaborare record di sottoclassi diverse in un unico lotto? …

In base alle risposte a queste domande, potremmo elaborare una soluzione appropriata.

Un’ulteriore possibilità di memorizzare proprietà specifiche per sottoclassi consiste nell’utilizzare una tabella con coppie Nome / valore. Questo approccio può essere particolarmente utile se esiste un numero elevato di sottoclassi diverse o quando i campi specifici nelle sottoclassi vengono utilizzati di rado.

Ho usato il primo approccio. Sotto carichi estremi la tabella “Qualcosa” diventa un collo di bottiglia.

Ho preso l’approccio di avere il modello DDL per i miei oggetti diversi con le specializzazioni degli attributi che sono state aggiunte alla fine della definizione della tabella.

A livello di DB se ho veramente bisogno di rappresentare le mie diverse classi come un recordset “Something” allora ho messo una visione sopra di loro

 SELECT "Something" fields FROM object1 UNION ALL SELECT "Something" fields FROM object2 UNION ALL SELECT "Something" fields FROM object3 

La sfida sta nel modo in cui assegni una chiave primaria non-clash, dato che hai tre oggetti indipendenti. In genere, le persone utilizzano un UUID / GUID, tuttavia nel mio caso la chiave era un numero intero a 64 bit generato in un’applicazione basata su un orario e una macchina al fine di evitare interferenze.

Se si segue questo approccio, si evita il problema dell’object “Qualcosa” che causa il blocco / blocco.

Se vuoi modificare l’object “Qualcosa” allora questo può essere imbarazzante ora hai tre oggetti indipendenti, ognuno dei quali richiederà la modifica della loro struttura.

Quindi per riassumere. L’opzione 1 funzionerà bene nella maggior parte dei casi, tuttavia in presenza di un carico pesante si può osservare il blocco dei blocchi che richiede la suddivisione del disegno.

L’approccio 1 con più chiavi esterne di colonne è il migliore. Perché in questo modo è ansible avere connessioni predefinite con altre tabelle E ciò rende molto più facile agli script selezionare, inserire e aggiornare i dati.