SQL - Concetti di progettazione - Relazioni - Integrità referenziale - Cascading

2

THE SCENARIO

Ho appreso concetti di base di progettazione di database come operazioni di base CRUD, integrità referenziale, relazioni, ecc., anni fa. Ho fatto confusione con i database e ho usato questa conoscenza in una capacità "non ufficiale" nel corso degli anni mentre imparavo a conoscere C # e WPF.

Ora mi trovo all'inizio del mio primo progetto di progettazione di database "ufficiale". Sto per progettare e scrivere un'applicazione WPF per la mia azienda. Questa applicazione è da sostituire e un vecchio conglomerato Foxpro 2.6 / Visual Foxpro attualmente in uso.

La nuova applicazione WPF utilizzerà il modello di progettazione MVVM e un modello di repository per l'accesso ai dati al back-end del database SQL. Convertirò sette tabelle FoxPro 2.6 in un nuovo progetto di database SQL:

Clienti : ogni cliente può disporre di più dispositivi

**Equipment** - each piece of equipment can have multiple testtypes

    **TestType1** - each test can have multiple years of test results
        testtype1 year1 test result
        testtype1 year2 test result 

    **TestType2**
        testtype2 year1 test result
        testtype2 year2 test result 

    **TestType3**
        testtype3 year1 test result
        testtype3 year2 test result
     etc...

In questo momento, la tabella clienti di Foxpro ha un campo ID cliente come identificatore e il campo viene ripetuto in TUTTE le tabelle, incluse le tabelle TestType per l'apparecchiatura, insieme a un campo numero equipnumber.

TASTI PRIMARI / TASTI ESTERI

Intendo creare chiavi primarie in tutte le mie tabelle e impostare relazioni standard uno-a-molti tra le tabelle Cliente e Equipaggiamento e tra le tabelle Apparecchiatura e Tipo test.

Questo è quello che voglio fare, vero?

Devo anche memorizzare l'ID principale del cliente nelle mie tabelle TestType?

So che renderebbe SOME le query più semplici, ma è una progettazione corretta? Ho lavorato in un paio di negozi e ho visto i loro progetti di database. Per me, non sembrava che stessero impostando relazioni e rafforzando l'integrità referenziale usando SQL. Penso che stessero facendo tutto ciò nel codice C # / VB.

Devo impostare cose come cancellazioni a cascata, aggiornamenti, ecc. e basta SQL Server gestirlo?

È un incubo di manutenzione?

CAMPO ISTRUZIONATO - Dovrei o non dovrei

Inizialmente pensavo di voler incorporare e UNDELETE feature nel nuovo programma. Ma non sono sicuro di volerlo fare. Avevo un campo IsDeleted e DateDeleted per tutti i miei record di tabella e l'app WPF aggiorna il campo IsDeleted su true e immetti una data quando viene eliminato un record. Quindi, le utilità di amministrazione hanno una vera funzione SPURGO che può eliminare per data cancellata.

Sto chiedendo dei guai qui?

In quali scenari questa è una buona idea? Una cattiva idea?

METADATA - ID negozio o dati reali?

E un'ultima domanda. Quando si tratta di metadati, (ad esempio, il nome del tipo di test, il codice del risultato, ecc., Dovrei memorizzare il valore effettivo dei metadati stesso con un record di dati o un ID di metadati e collegarlo alla tabella dei metadati? come sarebbe ingombrante interrogare e diventare un incubo per la manutenzione. Ci sono dei vantaggi nel memorizzare un ID di riferimento su una tabella di metadati?

Spero di non essere troppo generoso qui. E ogni consiglio e suggerimento è veramente apprezzato.

    
posta Slider16 05.03.2016 - 18:31
fonte

2 risposte

1

Appoggerei l'opinione di @ Claude sul non ripetere l'ID cliente in TestTable, poiché questo è denormalizzare i dati prima di aver trovato una buona ragione per farlo. Inoltre, attenersi alla regola secondo cui la chiave primaria in qualsiasi tabella è un singolo campo funziona bene (OK, potrei rilassarmi per un campo di 2: molti tabella di risoluzione)

Per quanto riguarda il campo isDeleted, ho un take diverso.

  • Non è necessario un campo flag isDeleted e un campo date dateDeleted. Vorrei andare solo con il campo dateDeleted. Una volta letti i tuoi dati in POJO, puoi facilmente aggiungere un metodo isDeleted () al POJO che controlla solo se dateDeleted non è nullo. E naturalmente le tue query SQL possono eseguire lo stesso controllo.
  • Hai bisogno di conservare una cronologia delle modifiche in questa tabella? Se è così, il campo dateDeleted è un buon modo per andare. Altrimenti, perché non eliminare fisicamente il record: la semplicità è buona:)
  • Per quanto riguarda la domanda MetaData, la risposta dipende dai requisiti aziendali. Se c'è una relazione 1: 1 con la tabella genitore, allora c'è poco da guadagnare creando una seconda tabella. Se la relazione è 1: molti hanno bisogno di una tabella separata, e se molti: molti hanno bisogno di due tabelle. Vorrei mettere in discussione il nome di questa tabella - MetaData in un database SQL ha la connotazione di descrivere le colonne del database (ad esempio NUMBER (12,0) o VARCHAR2 (166)), quindi è un buon termine da evitare.

E un paio di note sulle chiavi esterne:

  • Assicurati di creare un indice su tutti i campi chiave stranieri. Un database normalizzato è ottimo, ma la maggior parte delle tue query riguarderà i join e gli indici sono necessari per mantenere le prestazioni ragionevoli.
  • Pensa attentamente a rafforzare i vincoli di chiave esterna nel database. Questo rallenta le prestazioni, in particolare quando si eliminano i dati. Molti negozi li accendono nei database di sviluppo e li spengono per la produzione. Personalmente li lascio in produzione a meno che non ci sia un problema prestazionale comprovato.

Buona fortuna: questa è stata una buona domanda.

    
risposta data 10.03.2016 - 09:39
fonte
0

La risposta alla tua domanda potrebbe dipendere dai principi generali di progettazione nel tuo team e dalle preferenze personali.

Descrivo le mie preferenze personali:

  1. Non ripetere CustomerID in TestTable . Questa è una ottimizzazione prematura . Partecipa invece a Equipment . Naturalmente dipende dal prodotto del database e dalla dimensione dei dati. Stai utilizzando SQL Server? Gli indici e le viste indicizzate possono aumentare il rendimento.
  2. In parent-child-relations (UML: composizioni) io uso cascading cancella molto. Il codice dell'applicazione elimina solo il genitore. Ciò semplifica la vita quando il database viene refactored. Nelle relazioni opzionali (UML: associazioni) uso on delete set null .
  3. Quasi mai non uso on update cascade perché le chiavi primarie devono essere progettate per non cambiare mai.
  4. ISDELETED rende l'implementazione un incubo come uno dei commenti sottolineati. Pensa se una sofisticata gestione delle transazioni potrebbe soddisfare i tuoi requisiti. Altrimenti qualcosa simile a Il flashback Oracle potrebbe essere d'aiuto. Il rilevamento delle modifiche è un'opzione su SQL Server.
  5. Metadati: se identificate una relazione 1: N con una parte non primitiva (1), memorizzerei la (1) parte in una tabella separata. A volte la (1) parte ha solo poche costanti distinte di alcuni tipi di dati primitivi (nel codice di esempio dell'esempio?). Se non sono necessarie informazioni aggiuntive come timestamp, descrizioni, ecc. Non utilizzo una tabella di ricerca ma definisco un vincolo check come resultcode in (0, 1, 5) . Per quanto riguarda le prestazioni, vedi il punto (1) sopra.
risposta data 10.03.2016 - 08:45
fonte

Leggi altre domande sui tag