Come implementare l’integrità referenziale nei sottotipi

Ho le seguenti tabelle in un database relazionale:

[Sensor] LocationId [PK / FK -> Location] SensorNo [PK] [AnalogSensor] LocationId [PK/FK -> Sensor] SensorNo [PK/FK -> Sensor] UpperLimit LowerLimit [SwitchSensor] LocationId [PK/FK -> Sensor] SensorNo [PK/FK -> Sensor] OnTimeLimit [Reading] LocationId [PK/FK -> Sensor] SensorNo [PK/FK -> Sensor] ReadingDtm [PK] [ReadingSwitch] LocationId [PK/FK -> Reading] SensorNo [PK/FK -> Reading] ReadingDtm [PK/FK -> Reading] Switch [ReadingValue] LocationId [PK/FK -> Reading] SensorNo [PK/FK -> Reading] ReadingDtm [PK/FK -> Reading] Value [Alert] LocationId [PK/FK -> Reading] SensorNo [PK/FK -> Reading] ReadingDtm [PK/FK -> Reading] 

Fondamentalmente, ReadingSwitch e ReadingValue sono sottotipi di Reading e SwitchSensor e AnalogSensor sono sottotipi di Sensor. Una lettura può essere un valore SwitchReading o ValueReading – non può essere entrambi e un sensore può essere un sensore analogico o uno sensore.

L’unico modo in cui mi sono imbattuto finora è qui .

Deve esserci sicuramente un modo migliore per fare questo genere di cose.

L’unico altro modo in cui riesco a pensare è di non avere sottotipi ma espandere completamente tutto:

 [SwitchSensor] LocationId [PK/FK -> Location] SensorNo [PK] [AnalogSensor] LocationId [PK/FK -> Location] SensorNo [PK] [SwitchReading] LocationId [PK/FK -> SwitchSensor] SensorNo [PK/FK -> SwitchSensor] ReadingDtm Switch [AnalogReading] LocationId [PK/FK -> AnalogSensor] SensorNo [PK/FK -> AnalogSensor] ReadingDtm Value [AnalogReadingAlert] LocationId [PK/FK -> AnalogReading] SensorNo [PK/FK -> AnalogReading] ReadingDtm [PK/FK -> AnalogReading] [SwitchReadingAlert] LocationId [PK/FK -> SwitchReading] SensorNo [PK/FK -> SwitchReading] ReadingDtm [PK/FK -> SwitchReading] 

Quale potrebbe non essere così male ma ho anche tabelle che fanno riferimento alla tabella di avviso, quindi anche loro dovrebbero essere duplicati:

 [AnalogReadingAlertAcknowledgement] ... [AnalogReadingAlertAction] ... [SwitchReadingAlartAcknowledgement] ... [SwitchReadingAlartAction] 

eccetera.

Questo problema ha senso per qualcuno ??

Niente di tutto ciò è necessario, soprattutto non raddoppiare i tavoli.

introduzione

Dal momento che lo standard per la modellazione dei database relazionali (IDEF1X) è stato utilizzato comunemente da oltre 25 anni (almeno per l’alta qualità e le prestazioni elevate del mercato), utilizzo questa terminologia. Darwen, nonostante il grande lavoro che ha fatto per progredire 1 in linea con l’ottimo lavoro svolto per sopprimere il modello di relazione, non era a conoscenza di IDEF1X fino a quando non l’ho portato alla sua attenzione nel 2009, e quindi ha una nuova terminologia 2 per il Terminologia standard che utilizziamo da decenni. Inoltre, la nuova terminologia non si occupa di tutti i casi, come fa IDEF1X. Pertanto utilizzo la terminologia standard stabilita ed evito una nuova terminologia.

  • anche il concetto di una “chiave distribuita” non riconosce le relazioni PK :: FK ordinarie sottostanti, la loro implementazione in SQL e il loro potere.

  • Il concetto Relazionale, e quindi IDEF1X, è Identificatori e Migrazione degli stessi.

  • Certo, i venditori non sono esattamente sulla palla, e hanno cose strane come “Indici parziali” ecc., Che sono completamente inutili quando le basi sono capite. Ma gli accademici famosi hanno inventato nuovi concetti incompleti quando il concetto è stato standardizzato e dato il trattamento completo 25 anni fa … che è inaspettato.

Avvertimento

IEC / ISO / ANSI SQL gestisce a malapena 5NF in modo adeguato e non supporta affatto le strutture Supertype-Subtype; non ci sono vincoli dichiarativi per questo (e dovrebbe esserci).

  • Pertanto, al fine di applicare il set completo di regole espresse nel modello di dati, sia SuperType :: Subtype e Subtype :: Supertype, dobbiamo giocare un po ‘con CHECK Constraints, etc (evito di usare Trigger per una serie di motivi) .

Sollievo

Tuttavia, tengo conto di tutto ciò. Per poter fornire efficacemente un servizio di modellazione dei dati su StackOverflow, senza dover preordinarlo con un discorso completo, fornisco volutamente modelli che possono essere implementati da persone capaci, utilizzando SQL esistenti e vincoli esistenti, in qualsiasi misura essi richiedano. È già semplificato e contiene il livello comune di applicazione. Se c’è qualche domanda, basta chiedere e tu riceverai.

Possiamo utilizzare sia la grafica di esempio nel documento collegato sia il modello ▶ Sensore di dati del sensore pienamente conforms a IDEF1X

I lettori che non hanno familiarità con lo standard di modellazione relazionale possono trovare ▶ Notazione IDEF1 ◀ utile. I lettori che pensano che un database possa essere mappato su oggetti, classi e sottoclassi sono avvisati che leggere ulteriormente potrebbe causare lesioni. Questo è più di quanto Fowler e Ambler abbiano letto.

Implementazione dell’integrità referenziale per il sottotipo Supertype

Esistono due tipi di strutture Supertype-Sottotipo.

Sottotipo esclusivo

Esclusivo significa che può esistere una sola riga di sottotipo per ogni riga di Supertipo. Nei termini IDEF1X, ci dovrebbe essere una colonna Discriminator nel Supertipo, che identifica la riga Supertipo e la riga Sottotipo esistente per esso.

  • Per più di due sottotipi, questo è richiesto e implemento una colonna Discriminator.

  • Per due sottotipi, poiché questo è facilmente derivato da dati esistenti (ad esempio Sensor.IsSwitch è il Discriminator for Reading ), non modellizzo un’ulteriore colonna di Discriminator esplicita per Reading . Tuttavia, sei libero di seguire lo Standard alla lettera e implementare un Discriminatore.

Prenderò ogni aspetto in dettaglio.

  1. La colonna Discriminator richiede un vincolo CHECK per garantire che rientri nell’intervallo di valori, ad esempio: IN ("B", "C", "D") . IsSwitch è un BIT, che è 0 o 1, quindi è già vincolato.

  2. Poiché il PK del Supertipo definisce la sua unicità, sarà consentita una sola riga di Supertipo; non è ansible inserire una seconda riga di Supertype (e quindi nessuna seconda riga di sottotipo).

    • Pertanto è eccessivo, completamente ridondante, un ulteriore Indice non necessario, per implementare un Indice come (PK, Discriminator) nel Supertipo, come consigliato dal tuo link. L’unicità è nel PK, e quindi il PK più qualsiasi cosa sarà unica).

    • IDEF1X non richiede il Discriminator nelle tabelle Sottotipo. Nel Sottotipo, che è nuovamente vincolato dall’unicità del suo PK, come per il modello, se il Discriminator è stato implementato come una colonna in quella tabella, ogni riga in essa avrà lo stesso valore per il Discriminator (ogni Book sarà ” B “; ogni ReadingSwitch sarà un IsSwitch ). Pertanto è assurdo implementare il Discriminatore come una colonna nel Sottotipo. E ancora, completamente ridondante, un ulteriore Indice non necessario, per implementare un Indice come (PK, Discriminator) nel Sottotipo: l’unicità è nel PK, e quindi il PK più qualsiasi cosa sarà unico).

    • Il metodo identificato nel collegamento è un modo per implementare l’integrità referenziale. C’è probabilmente una buona ragione per cui l’autore non ha visto quel costrutto da nessun’altra parte. È un errore di base comprendere SQL e usarlo come è in effetti. Queste “soluzioni” sono tipiche delle persone che seguono un dogma “SQL non può fare …” e quindi sono ciechi a ciò che SQL può fare. Gli orrori derivanti dai “metodi” ciechi di Fowler e Ambler sono persino peggiori.

  3. Il sottotipo PK è anche l’FK per il supertipo, che è tutto ciò che è necessario per garantire che il sottotipo non esista senza un supertipo genitore.

    • Pertanto, per ogni PK specificato, qualunque Supertype-Sottotipo viene inserito per primo avrà successo; e dopo aver tentato, successivamente, Supertype-Subtype, fallirà. Pertanto non c’è nulla di cui preoccuparsi nella tabella Sottotipo (una seconda riga di Supertype o una seconda sottotipo di riga per lo stesso PK viene prevenuta).
      .
  4. SQL CHECK Constraint è limitato al controllo della riga inserita . Dobbiamo controllare la riga inserita contro altre righe , nella stessa tabella o in un’altra tabella. Pertanto è richiesta una funzione definita dall’utente.

    • Scrivi una semplice UDF che verificherà l’esistenza del PK e del Discriminator nel SuperType e restituirà 1 se EXISTS o 0 se NOT EXISTS. Avrai bisogno di una UDF per supertipo (non per sottotipo).

    • Nel sottotipo, implementare un vincolo CHECK che richiama l’UDF, utilizzando il PK (che è sia il supertipo che il sottotipo) e il valore del discriminatore.

    • L’ho implementato in decine di grandi database del mondo reale, su diverse piattaforms SQL. Ecco il ▶ Codice funzione definito dall’utente ◀ e il ▶ Codice DDL ◀ per gli oggetti su cui si basa.

    • Questa particolare syntax e codice sono stati testati su Sybase ASE 15.0.2 (sono molto prudenti in merito alla conformità agli standard SQL).

    • Sono consapevole del fatto che le limitazioni sulle funzioni definite dall’utente sono diverse per ogni piattaforma SQL. Tuttavia, questo è il più semplice del semplice, e AFAIK ogni piattaforma consente questo costrutto. (Non ho idea di cosa facciano i non SQL.)

    • sì, ovviamente questa tecnica ingegnosa può essere utilizzata per implementare qualsiasi regola di dati non banali che è ansible disegnare in un modello di dati. In particolare, per superare i limiti di SQL. Nota la mia caucanvas per evitare Vincoli a due vie (riferimenti circolari).

  5. Pertanto, il VERIFICARE Vincolo nel Sottotipo, assicura che il PK più il Discriminatore corretto esista in Supertipo. Ciò significa che esiste solo quel Sottotipo per il Supertipo (il PK).

    • Qualsiasi tentativo successivo di inserire un altro sottotipo (cioè interrompere la regola esclusiva) fallirà perché il discriminante PK + non esiste nel supertipo.

    • Qualsiasi tentativo successivo di inserire un’altra riga dello stesso sottotipo è impedito dall’unicità del suo vincolo PK.

  6. L’unico bit mancante (non menzionato nel collegamento) è la regola “ogni Supertipo deve avere almeno un sottotipo” non viene applicata. Questo è facilmente coperto nel codice Transazionale ( non consiglio i Vincoli che vanno in due direzioni, o Trigger); usa lo strumento giusto per il lavoro.

Sottotipo non esclusivo

Il Supertipo (genitore) può ospitare più di un Sottotipo (figlio)

  1. Non esiste un sottotipo da identificare.

    • Il discriminatore non si applica ai sottotipi non esclusivi.

    • L’esistenza di un sottotipo viene identificata eseguendo un controllo di esistenza nella tabella Sottotipo, utilizzando il Supertipo PK.

  2. Basta escludere il vincolo CHECK che richiama l’UDF sopra.

    • PRIMARY KEY, FOREIGN KEY e il consueto Range CHECK Constraints supportano adeguatamente tutti i requisiti per i sottotipi non esclusivi.

Riferimento

Per ulteriori dettagli; una panoramica schematica con dettagli; e la distinzione tra sottotipi e tabelle di colonne opzionali, si riferisce a questo documento sottotipo .

Nota

  1. Anch’io sono stato preso dai riferimenti costanti di CJ Date e Hugh Darwen per “promuovere” il modello relazionale . Sulla base di prove coerenti, dopo molti anni di interazione, ho concluso che il loro lavoro è in effetti, una svalutazione di esso. Non hanno fatto nulla per approfondire il lavoro fondamentale del dott. EF Codd e tutto per danneggiarlo e sopprimerlo.

  2. Hanno definizioni private per termini relazionali, che ovviamente ostacolano seriamente qualsiasi comunicazione. Hanno una nuova terminologia per i termini che abbiamo avuto dal 1970, per apparire che l’hanno “inventato”. Tipico di truffe e ladri.

Anche la seconda opzione è irta di problemi, ad esempio Sensori (e supponendo che SensorNo sia una chiave surrogata), poiché non si dispone di una tabella di base, il surrogato SensorNo non è univoco tra le sottoclassi, a meno che non si utilizzi un Meccanismo klugey per emettere le chiavi su tutte le tabelle della sottoclass (o usare un guid).

Questo sarebbe amplificato se tu avessi un’interfaccia utente che “combina” diversi tipi di sensori, per esempio una lista che mostra un’unione di sensori Analog e Switch.

Ti consiglierei di rimanere con il tuo primo schema, e quindi incapsulare l’inserimento e la manutenzione di queste tabelle con codice transazionale ben collaudato.

Ad esempio, creare procedure di inserimento per i vari sottotables, che inseriscono i rispettivi record di base e sottoclassi in un’unità di lavoro. Puoi andare oltre, quindi revocare i privilegi INSERT su QUALUNQUE delle tabelle, forzando così l’inserimento tramite gli SPROC.

È anche ansible eseguire un rapporto di integrità giornaliero che verifica che non vi siano state violazioni della struttura di ereditarietà.