Perché il modello relazionale per un database è importante?

L'argomento delle prestazioni è solitamente quello più intuitivo. In particolare, vuoi sottolineare come sarà difficile aggiungere buoni indici in un database normalizzato in modo errato (nota: ci sono casi limite in cui la denormalizzazione può in effetti migliorare , ma quando entrambi siete inesperti database relazionali probabilmente non vedrai facilmente questi casi).

Un altro è l'argomento della dimensione di archiviazione. Una tabella denormalizzata con molte ridondanze richiederà molto più spazio di archiviazione. Ciò vale anche per l'aspetto delle prestazioni: più dati hai, più lenti saranno le tue query.

C'è anche un argomento che è un po 'più difficile da capire, ma in realtà è più importante perché non puoi risolverlo gettando più hardware su di esso. Questo è il problema della coerenza dei dati. Un database correttamente normalizzato si prenderà cura di sé che un prodotto con un ID specifico ha sempre lo stesso nome. Ma in un database denormalizzato sono possibili tali incoerenze, quindi è necessario prestare particolare attenzione quando si tratta di evitare le incongruenze, che richiedono tempo di programmazione per andare bene e causano comunque bug che ti costeranno nella soddisfazione del cliente.

I'll be having to implement a database with my boss ...

L'uso del software dedicato alla Gestione Database dedicato potrebbe essere notevolmente più semplice (mi dispiace, non ho potuto resistere).

lngStoreID | vrStoreName | lngCompanyID | vrCompanyName | lngProductID | vrProductName

Se a questo database interessa solo "registrare" quale prodotto è stato venduto dove, quando e da chi, allora potrebbe essere in grado di estendere la definizione di "database OK" abbastanza lontano da coprirlo. Se questi dati vengono usati per qualsiasi cosa altro, allora è davvero piuttosto scarso.

Ma ...

L'applicazione / query che utilizzano questi dati rispondono male / lentamente? In caso contrario, non c'è alcun vero problema da risolvere. Certo, sembra brutto, ma se funziona allora non otterrai alcun "punto" per suggerire che "potrebbe" essere migliore.

Se riesci a trovare sintomi definiti (ad esempio problemi) che sembrano causati da una modellazione dei dati scadente, quindi prototipare una soluzione migliore. Prendi una copia di uno di questi "database", normalizza i dati e verifica se la tua soluzione funziona meglio. Se è considerevolmente migliore (e mi aspetto che le eventuali operazioni di aggiornamento su questi dati siano massivamente migliorate) allora torna al tuo capo e mostragli il miglioramento

È perfettamente possibile ricreare la sua "vista a tabella singola" dei dati con .. beh .. Views.

Why does a good relational schema improve a database?

La risposta è: non sempre migliorare un database. Dovresti essere consapevole che ciò che probabilmente ti è stato insegnato è chiamato Terzo modulo normale .

Altre forme sono valide in alcune situazioni, che è la chiave per rispondere alla tua domanda. Il tuo esempio è simile a Primo modulo normale , se ciò ti aiuta a sentirti meglio riguardo al suo stato attuale.

Le regole 3NF stabiliscono relazioni tra i dati che "migliorano" un database:

1. Impedisci ai dati non validi di entrare nel tuo sistema (se una relazione è 1-a-1 impone un errore nonostante il codice scritto su di esso). Se i tuoi dati sono coerenti nel database, è meno probabile che risultino incoerenze al di fuori del tuo database.

2. Fornisce un modo per convalidare il codice (ad esempio una relazione molti-a-uno è un segnale per limitare le proprietà / comportamenti di un oggetto). Quando si scrive codice per utilizzare il database, a volte i programmatori notano la struttura dei dati come un indicatore di come dovrebbe funzionare il loro codice. Oppure possono fornire un feedback utile se il database non corrisponde al loro codice. (Questo è più un pio desiderio, purtroppo.)

3. Fornisci regole che possano aiutarti a ridurre in modo significativo gli errori durante la creazione di un database, in modo da non costruirlo in base a requisiti arbitrari che potrebbero verificarsi in qualsiasi momento durante la vita di un database. Invece, stai valutando sistematicamente le informazioni per raggiungere obiettivi specifici.

4. Strutture di database appropriate migliorano le prestazioni collegando i dati in modo da minimizzare l'archiviazione dei dati, minimizzare le chiamate di archiviazione per recuperare i dati, massimizzare le risorse in memoria e / o minimizzare l'ordinamento / manipolazione dei dati per il particolare set di dati che hai, rispetto alla query che stai eseguendo contro di essa. Ma la struttura "corretta" dipende dalla quantità di dati, natura dei dati, tipo di query, risorse di sistema, ecc. Normalizzando si possono peggiorare le prestazioni (cioè se si caricano tutti i dati come 1 tabella - l'unione può rallentare una domanda). L'elaborazione delle transazioni (OLTP) e la business intelligence (data warehouse) sono molto diverse.

In una piccola azienda con piccoli set di dati, potresti scoprire che non c'è niente di sbagliato nel modo in cui è ora. Tranne che, se cresci, sarà un problema "aggiustare" più tardi, perché man mano che la tabella diventa grande, i sistemi che la usano andranno più piano.

Normalmente vorrai enfatizzare le transazioni veloci man mano che un'azienda cresce. Tuttavia, se passi del tempo su questo progetto ora invece di altre cose che potrebbero richiedere più urgentemente l'azienda, potresti non avere mai quel problema perché la tua azienda non cresce mai realmente. Questa è la "sfida pre-ottimizzazione" - dove trascorrere il tuo tempo prezioso in questo momento.

Buona fortuna!

Ci sono molti motivi per cui l'uso di un grande "tavolo dei geni" è sbagliato. Proverò a illustrare i problemi con un database di esempio preparato. Supponiamo che tu stia cercando di modellare eventi sportivi. Diremo che vuoi modellare i giochi e le squadre che giocano in quei giochi. Un disegno con più tabelle potrebbe apparire come questo (questo è molto semplicistico di proposito quindi non farti intrappolare in luoghi dove potrebbe essere applicata più normalizzazione):

Teams
Id | Name | HomeCity

Games
Id | StartsAt | HomeTeamId | AwayTeamId | Location

e un singolo database di tabelle sarebbe simile a questo

TeamsAndGames
Id | TeamName | TeamHomeCity | GameStartsAt | GameHomeTeamId | GameAwayTeamId | Location

Per prima cosa, diamo un'occhiata agli indici su quei tavoli. Se avessi bisogno di un indice sulla città natale per una squadra, potrei aggiungerlo alla tabella Teams o alla tabella TeamsAndGames piuttosto facilmente. Ricorda che ogni volta che crei un indice, questo deve essere memorizzato su disco da qualche parte e aggiornato man mano che le righe vengono aggiunte alla tabella. Nel caso della tabella Teams questo è piuttosto semplice. Inserisco una nuova squadra, il database aggiorna l'indice. Ma per quanto riguarda TeamsAndGames ? Bene, lo stesso vale per l'esempio Teams . Aggiungo una squadra, l'indice viene aggiornato. Ma succede anche quando aggiungo un gioco! Anche se quel campo sarà nullo per un gioco, l'indice deve comunque essere aggiornato e memorizzato su disco per quel gioco comunque. Per un indice, questo non suona male. Ma quando hai bisogno di molti indici per le entità multiple stipate in questa tabella, perdi molto spazio per memorizzare gli indici e un sacco di tempo del processore aggiornandoli per cose che non applicano.

Secondo, coerenza dei dati. Nel caso dell'utilizzo di due tabelle separate, posso utilizzare chiavi esterne dalla tabella Games alla tabella Teams per definire quali squadre stanno giocando in un gioco. E supponendo di rendere le colonne HomeTeamId e AwayTeamId non annullabili, il database garantirà che ogni partita che ho inserito abbia 2 squadre e che quei team esistano nel mio database. Ma per quanto riguarda lo scenario a tavolo unico? Bene, dato che ci sono più entità in questa tabella, quelle colonne dovrebbero essere nullable (potresti renderle non annullabili e inserire i dati spazzatura lì, ma questa è solo un'idea orribile). Se queste colonne sono annullabili, il database non può più garantire che quando inserisci una partita abbia due squadre.

Ma cosa succederebbe se decidessi di andarci comunque? Si impostano le chiavi esterne in modo tale che quei campi rimandino a un'altra entità nella stessa tabella. Ma ora il database si limiterà a verificare che tali entità esistano nella tabella, non che siano il tipo corretto. Si potrebbe facilmente impostare GameHomeTeamId sull'ID di un altro gioco e il database non si lamenterà affatto. Se l'hai provato nello scenario a più tabelle, il database avrebbe reagito.

Potresti provare a mitigare questi problemi dicendo "beh, ci assicureremo solo di non farlo mai in codice". Se sei sicuro della tua capacità di scrivere codice senza errori la prima volta e nella tua capacità di tenere conto di ogni strana combinazione di cose che un utente potrebbe provare, vai avanti. Personalmente non sono sicuro della mia capacità di fare nessuna di queste cose, quindi lascerò che il database mi fornisca una rete di sicurezza extra.

(Questo diventa ancora peggio se il tuo progetto è uno in cui copi tutti i dati rilevanti tra le righe invece di usare chiavi esterne. Qualsiasi incongruenza / altre incoerenze dei dati sarà difficile da risolvere. Come puoi sapere se "Jon" è un errore di ortografia di "John" o se era intenzionale (perché sono due persone separate)?)

In terzo luogo, quasi tutte le colonne devono essere annullabili o devono essere riempite con dati copiati o illeggibili. Un gioco non ha bisogno di un TeamName o TeamHomeCity . Quindi, ogni gioco ha bisogno di qualche tipo di segnaposto o deve essere annullabile. E se è nullable, il database farà felicemente una partita senza TeamName . Prenderà anche una squadra senza nome, anche se la tua logica aziendale dice che non dovrebbe mai accadere.

Ci sono una manciata di altri motivi per cui vorrai tabelle separate (compreso il mantenimento della sanità mentale degli sviluppatori). Ci sono anche alcuni motivi per cui un tavolo più grande potrebbe essere migliore (la denormalizzazione a volte migliora le prestazioni). Questi scenari sono pochi e distanti tra loro (e di solito vengono gestiti meglio quando si hanno metriche sul rendimento per dimostrare che questo è davvero il problema, non un indice mancante o qualcos'altro).

Infine, sviluppa qualcosa che sarà facile da mantenere. Solo perché "funziona" non significa che sia OK. Cercare di mantenere tavoli divini (come le lezioni di dio) è un incubo. Ti stai solo preparando per il dolore più tardi.

Citazione del giorno: " Teoria e pratica dovrebbero essere uguali ... in teoria "

Tabella denormalizzata

La tua tabella hold-it-all univoca contiene dati ridondanti ha un vantaggio: rende molto semplice la codifica delle sue linee sul codice e veloce da eseguire perché non devi fare nessun join. Ma questo a un costo elevato:

• Contiene copie ridondanti di relazioni (ad esempio IngCompanyID e vrCompanyName ). L'aggiornamento dei dati master potrebbe richiedere l'aggiornamento di molte più righe rispetto a uno schema normalizzato.
• Mescola tutto. Non è possibile garantire un controllo di accesso semplice a livello di database, ad es. assicurando che l'utente A possa aggiornare solo le informazioni sull'azienda e l'utente B solo le informazioni sul prodotto.
• Non è possibile garantire regole di coerenza a livello di database (ad esempio chiave primaria per far rispettare l'esistenza di un solo nome di società per un ID azienda).
• Non è possibile trarre il massimo vantaggio dall'ottimizzatore DB che potrebbe identificare strategie di accesso ottimali per una query complessa, sfruttando la dimensione delle tabelle normalizzate e le statistiche di più indici. Ciò potrebbe rapidamente compensare il limitato vantaggio di evitare i join.

Tabella normalizzata

Gli svantaggi di cui sopra sono vantaggi per lo schema normalizzato. Naturalmente, le query potrebbero essere un po 'più complesse da scrivere.

In breve, lo schema normalizzato esprime molto meglio la struttura e le relazioni tra i tuoi dati. Sarò provocatorio e dirò che è lo stesso tipo di differenza rispetto alla disciplina richiesta per utilizzare un set di cassetti per ufficio ordinato e la facilità di utilizzo di un cestino.

Penso che ci siano almeno due parti alla tua domanda:

1. Perché non dovrebbero essere archiviate entità di tipi diversi nella stessa tabella?

Le risposte più importanti qui sono la leggibilità e la velocità del codice. Una SELECT name FROM companies WHERE id = ? è così molto più leggibile di un SELECT companyName FROM masterTable WHERE companyId = ? e si hanno meno probabilità di accidentially sciocchezze query (ad esempio SELECT companyName FROM masterTable WHERE employeeId = ? non sarebbe possibile quando le aziende ei dipendenti sono memorizzati in tabelle differenti). Per quanto riguarda la velocità, i dati di una tabella di database vengono recuperati leggendo l'intera tabella in sequenza o leggendo da un indice. Entrambi sono più veloci se la tabella / indice contiene meno dati, e questo è il caso se i dati sono memorizzati in tabelle diverse (e devi solo leggere una delle tabelle / indici).

2. Perché le entità di un singolo tipo dovrebbero essere suddivise in sub-entità che sono memorizzate in diverse tabelle?

Qui, la ragione è principalmente quella di prevenire incoerenze nei dati. Con l'approccio a una sola tabella, per un sistema di gestione degli ordini è possibile memorizzare il nome del cliente, l'indirizzo del cliente e l'ID del prodotto del cliente ordinato dal cliente come singola entità. Se un cliente ha ordinato più prodotti, nel suo database sono presenti più istanze del nome e dell'indirizzo del cliente. Nel migliore dei casi, hai appena ricevuto dati duplicati nel tuo database, il che potrebbe rallentarlo un po '. Ma un caso peggiore è che qualcuno (o qualche codice) ha commesso un errore quando i dati sono stati inseriti in modo che una società finisca con diversi indirizzi nel proprio database. Questo da solo è già abbastanza brutto. Ma se dovessi interrogare l'indirizzo di una società in base al suo nome (ad esempio SELECT companyAddress FROM orders WHERE companyName = ? LIMIT 1 ) avresti ottenuto arbitrariamente uno dei due indirizzi restituiti e non avresti nemmeno realizzato che c'era un'incongruenza. Ma ogni volta che si esegue la query, si può effettivamente ottenere un indirizzo diverso, a seconda di come la query viene risolta internamente dal DBMS. Questo probabilmente spezzerà la tua applicazione da qualche altra parte, e la causa alla radice di quella rottura sarà molto difficile da trovare.

Con l'approccio a più tavoli, ti rendi conto che esiste una dipendenza funzionale dal nome della società all'indirizzo della società (se una società può avere un solo indirizzo), devi archiviare il ( companyName, companyAddress) tupla in una tabella (ad esempio company ) e la tupla (productId, companyName) in un'altra tabella (ad esempio order ). Un vincolo UNIQUE sulla tabella company potrebbe quindi far rispettare che ogni azienda ha un solo indirizzo nel database in modo che non si verifichi alcuna incoerenza per gli indirizzi aziendali.

Nota: in pratica, per motivi di prestazioni, probabilmente generi un ID aziendale univoco per ciascuna azienda e lo utilizzerai come chiave esterna anziché utilizzare direttamente companyName. Ma l'approccio generale rimane lo stesso.

TL; DR - Stanno progettando il database in base a come sono stati insegnati loro quando erano a scuola.

Avrei potuto scrivere questa domanda 10 anni fa. Mi ci è voluto un po 'di tempo per capire perché i miei predecessori progettavano i loro database come facevano. Stai lavorando con qualcuno che:

1. Ha ottenuto la maggior parte delle competenze di progettazione del database utilizzando Excel come database o
2. Stanno usando le migliori pratiche da quando hanno finito la scuola.

Non sospetto che sia il n. 1 dal momento che in realtà hai numeri di identificazione nel tuo tavolo, quindi presumo il n. 2.

Dopo essere uscito da scuola, stavo lavorando per un negozio che utilizzava un AS / 400 (noto anche come IBM i ). Ho trovato alcune strane cose nel modo in cui hanno progettato i loro database, e ho iniziato a sostenere che facciamo delle modifiche per seguire come mi è stato insegnato come progettare i database. (ero stupido allora)

Ci è voluto un paziente programmatore più anziano per spiegarmi perché le cose erano fatte in quel modo. Non avevano modificato lo schema perché avrebbe causato la rottura di programmi più vecchi di me. Letteralmente, il codice sorgente per un programma aveva una data di creazione dell'anno prima che io nascessi. Sul sistema su cui stavamo lavorando, i loro programmi dovevano implementare tutta la logica e le operazioni che il pianificatore di query del database gestisce per te . (lo puoi vedere eseguendo EXPLAIN su una delle tue query)

Era aggiornato sulle tecniche che stavo cercando di implementare, ma mantenere il sistema in esecuzione era più importante che apportare modifiche "perché andava contro ciò che mi veniva insegnato". Ognuno di noi ha iniziato a fare il miglior uso del modello relazionale che eravamo in grado di fare. Sfortunatamente, altri programmatori / consulenti di quel periodo progettavano ancora i loro database come se stessero lavorando con i precedenti vincoli di quel sistema.

Alcuni esempi di ciò che ho incontrato non si adattavano al modello relazionale:

• Le date sono state memorizzate come numeri di giorni giuliani che hanno richiesto un join a una tabella di date per ottenere la data effettiva.
• Tabelle denormalizzate con colonne sequenziali dello stesso tipo (ad esempio code1,code2, ..., code20 )
• Lunghezza NxM colonne CHAR che rappresentano un array di N stringhe di lunghezza M.

Le ragioni che mi hanno dato per le decisioni di progettazione erano tutte basate sui vincoli del sistema quando il database è stato progettato per la prima volta.

Date - Mi è stato detto che impiegava più tempo di elaborazione per utilizzare le funzioni di data (mese o giorno o giorno della settimana) per elaborare una data piuttosto che creare una tabella di ogni possibile data con tutte quella informazione.

Colonne sequenziali dello stesso tipo : l'ambiente di programmazione in cui si trovavano consentiva a un programma di creare una variabile di matrice su una parte della riga. Ed era un modo più semplice per ridurre il numero di operazioni di lettura.

Colonne CHAR Lunghezza NxM : è stato più semplice inserire valori di configurazione in una colonna per ridurre le operazioni di lettura dei file.

Un esempio mal concepito in equivalente C per riflettere l'ambiente di programmazione che avevano:

#define COURSE_LENGTH 4
#define NUM_COURSES 4
#define PERIOD_LENGTH 2

struct mytable {
    int id;
    char periodNames[NUM_COURSES * PERIOD_LENGTH];  // NxM CHAR Column
    char course1[COURSE_LENGTH];
    char course2[COURSE_LENGTH];
    char course3[COURSE_LENGTH];
    char course4[COURSE_LENGTH];
};

...

// Example row
struct mytable row = {.id= 1, .periodNames="HRP1P2P8", .course1="MATH", .course2="ENGL", .course3 = "SCI ", .course4 = "READ"};

char *courses; // Pointer used to access the sequential columns
courses = (char *)&row.course1;


for(int i = 0; i < NUM_COURSES; i++) {

    printf("%d: %.*s -> %.*s\n",i+1, PERIOD_LENGTH, &row.periodNames[PERIOD_LENGTH * i], COURSE_LENGTH,&courses[COURSE_LENGTH*i]);
}

Outputs

1: HR -> MATH
2: P1 -> ENGL
3: P2 -> SCI
4: P8 -> READ

Secondo quanto mi è stato detto, alcune di queste erano considerate best practice all'epoca.