[SQL] toglietemi un dubbio sulle relazioni

[Gemini77]

Ciao a tutti,
premetto che la mia conoscenza di DB ed SQL è limitata a piccoli progetti, composti nello specifico da qualche centinania di record e poco meno di 50 tabelle.
Il mio dubbio è il seguente, quando e quanto conviene strutturare una relazione 1 a molti tra 1 tabella padre e N tabelle figlio? parlo non solo a livello "tecnico", ma soprattutto a livello pratico e prestazionale.

Mi spiego meglio, magari con un esempio
in genere la tabella padre è una tabella di prodotto, organizzata così

T_PADRE
id_prodotto
nome_prodotto
tipologia_id
colore_id
marca_id
forma_id

T_TIPO
tipologia_id
descr

T_COLORE
colore_id
descr

T_MARCA
marca_id
descr

T_FORMA
forma_id
descr

fino ad oggi, questo genere di struttura mi è tornata comoda per alcune ragioni, la velocità di sviluppo (inserire 1 campo relazionato nella tabella padre mi torna semplice per via di funzioni pre-costruite), la sicurezza che, i dati inseriti siano "esatti", la possibilità di utilizzare le tabelle figlio su altre tabelle padre.


ora mi chiedo, quanto è giusto far così piuttosto che inserire nella tabella padre (al posto dell'id relazionato con la tabella figlio) la descrizione stessa del campo interessato? il dubbio nasce dal fatto, che spesso viene richiesto l'inserimento del campo "altro" (relativo ai campi relazionati) il che mi obbliga a soluzioni che ritengo poco pratiche (a livello di codice e di sql).



Perdonate il mio linguaggio "poco tecnico", ma in materia ho poca esperienza


grazie

[cdimauro]

Normalizzare, come hai fatto tu, è una buona cosa, per i seguenti motivi:

1) mantiene consistente il db (visto che la scelta dei valori è limitata soltanto a quelli presente nelle tabelle "foreign"; questo è il gergo tecnico);

2) compatta i dati facendo occupare meno spazio (molto meglio inserire 100 volte l'intero 1234567890 che "Giovinco");

3) migliora le prestazioni nelle ricerche (meno spazio si traduce in PIU' record presenti in una singola pagina del database, quindi meno accessi al disco; inoltre gli indici sugli id occupano molto meno spazio, e idem per i medesimi campi indicizzati delle tabelle "foreign").

Inoltre se hai tabelle con troppi campi puoi ottimizzare la velocità di accesso alla tabella "spezzandola" in due tabelle: nella prima (quella principale) metti tutti i campi per cui per un motivo o per un altro puoi fare qualche ricerca su di essi; nella seconda metti tutti gli altri campi "accessori" che recuperi esclusivamente a fini di visualizzazione o stampa.

In questo modo tutte le query che fai sulla prima tabella verranno velocizzate perché, come spiegato nel punto 3, una pagina del db conterrà molti più record e gli accessi al disco saranno di meno.

Questo principio l'ho applicato di recente a una tabella con un paio di milioni di record, e una query è passata da 8 a 6 secondi. Tanto per fare un esempio.

[_Claudio]

Originariamente inviato da cdimauro

Normalizzare, come hai fatto tu, è una buona cosa, per i seguenti motivi:

1) mantiene consistente il db (visto che la scelta dei valori è limitata soltanto a quelli presente nelle tabelle "foreign"; questo è il gergo tecnico);

2) compatta i dati facendo occupare meno spazio (molto meglio inserire 100 volte l'intero 1234567890 che "Giovinco");

3) migliora le prestazioni nelle ricerche (meno spazio si traduce in PIU' record presenti in una singola pagina del database, quindi meno accessi al disco; inoltre gli indici sugli id occupano molto meno spazio, e idem per i medesimi campi indicizzati delle tabelle "foreign").

Inoltre se hai tabelle con troppi campi puoi ottimizzare la velocità di accesso alla tabella "spezzandola" in due tabelle: nella prima (quella principale) metti tutti i campi per cui per un motivo o per un altro puoi fare qualche ricerca su di essi; nella seconda metti tutti gli altri campi "accessori" che recuperi esclusivamente a fini di visualizzazione o stampa.

In questo modo tutte le query che fai sulla prima tabella verranno velocizzate perché, come spiegato nel punto 3, una pagina del db conterrà molti più record e gli accessi al disco saranno di meno.

Questo principio l'ho applicato di recente a una tabella con un paio di milioni di record, e una query è passata da 8 a 6 secondi. Tanto per fare un esempio.

La soluzione di Gemini77 è corretta anche dal punto di vista teorico e quindi di conseguenza anche dal punto di vista pratico/prestazionale.
Per quanto riguarda dati "accessori" è sempre un problema il fatto che siano poco standardizzabili (ci ho scritto una tesi su queste faccende). Solitamente si tende a tenere così nel limbo tali dati per poi standardizzarli e formalizzarli non appena possibile... tra l'altro calcoli statistici dicono che in caso di rarità di questi dati costa di più standardizzarli e/o creare una struttura software di gestione apposita che nemmeno gestirli così, grezzi...

Per quanto riguarda la tecnica proposta da cdimauro se ho ben capito si tratta di "spostare" dati tra due tabelle identiche in base alla loro località temporale. Non sono molto d'accordo, perchè è una pratica poco ortodossa... ma si sa che ogni metodo rude è buono nell'informatica.
Tuttavia piuttosto che un metodo così "faccendiero" preferirei costruire una struttura software (oggetti serializzati) che contengono i dati acceduti più frequentemente in modo che avrei il massimo delle prestazioni, non facendo riferimento ogni volta a qualcosa salvato sul disco (DataBase) ma avrei direttamente i miei dati in RAM e in caso di reboot del sistema o chiusura del software essendo l'oggetto serializzabile può essere comodamente salvato e ricaricato così com'è da disco.
(Ovviamente va usata una struttura dati apposita che richiede con ogni probabilità un linguaggio ad oggetti, non a caso io ho parlato di oggetto).

[cdimauro]

Forse non sono stato molto chiaro. La mia idea è di dividere una tabella in due tabelle legate da relazione 1:1, lasciando sulla prima i dati usati più frequentemente per le ricerche, mentre sulla seconda quelli "accessori".

L'esempio a cui mi riferivo lo riporto qui. Ecco la tabella originale:

CREATE TABLE Formats (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  ItemID INTEGER NOT NULL,
  SubProductTypeID INTEGER NOT NULL,
  Language SET('it','en','fr','de','sv','zh','cs','da','nl','fi','hu','ja','ko','no','pl','pt','ru','sk','es','tr') NOT NULL,
  StorageURI LONGBLOB,
  Digest VARCHAR(40) NOT NULL DEFAULT '',
  CONSTRAINT pk_Formats PRIMARY KEY (ID),
  CONSTRAINT fk_Formats_ItemID FOREIGN KEY (ItemID) REFERENCES Items (ID) ON DELETE CASCADE,
  CONSTRAINT fk_Formats_SubProductType FOREIGN KEY (SubProductTypeID) REFERENCES SubProductTypes (ID) ON DELETE CASCADE
) ENGINE = InnoDB;

Che è stata "spezzata" nelle seguenti due:

CREATE TABLE Formats (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  ItemID INTEGER NOT NULL,
  SubProductTypeID INTEGER NOT NULL,
  Language SET('it','en','fr','de','sv','zh','cs','da','nl','fi','hu','ja','ko','no','pl','pt','ru','sk','es','tr') NOT NULL,
  CONSTRAINT pk_Formats PRIMARY KEY (ID),
  CONSTRAINT fk_Formats_ItemID FOREIGN KEY (ItemID) REFERENCES Items (ID) ON DELETE CASCADE,
  CONSTRAINT fk_Formats_SubProductType FOREIGN KEY (SubProductTypeID) REFERENCES SubProductTypes (ID) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE FormatsExtras (
  FormatID INTEGER NOT NULL,
  StorageURI LONGBLOB,
  Digest VARCHAR(40) NOT NULL DEFAULT '',
  CONSTRAINT fk_FormatsExtras_FormatID FOREIGN KEY (FormatID) REFERENCES Formats (ID) ON DELETE CASCADE
) ENGINE = InnoDB;

Normalmente il mio server lavora sulla prima, mentre la seconda viene usata soltanto quando serve estrapolare dei dati per visualizzare / stampare delle informazioni o per fare qualche controllo occasionale (tipo il controllo della firma digitale).

Il risultato è che le query che eseguo molto spesso sono state velocizzate, al costo di un leggero aumento dei tempi di calcolo quando servono i dati presenti nella seconda tabella, ossia raramente.

[_Claudio]

Originariamente inviato da cdimauro

Forse non sono stato molto chiaro. La mia idea è di dividere una tabella in due tabelle legate da relazione 1:1, lasciando sulla prima i dati usati più frequentemente per le ricerche, mentre sulla seconda quelli "accessori".

L'esempio a cui mi riferivo lo riporto qui. Ecco la tabella originale:

CREATE TABLE Formats (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  ItemID INTEGER NOT NULL,
  SubProductTypeID INTEGER NOT NULL,
  Language SET('it','en','fr','de','sv','zh','cs','da','nl','fi','hu','ja','ko','no','pl','pt','ru','sk','es','tr') NOT NULL,
  StorageURI LONGBLOB,
  Digest VARCHAR(40) NOT NULL DEFAULT '',
  CONSTRAINT pk_Formats PRIMARY KEY (ID),
  CONSTRAINT fk_Formats_ItemID FOREIGN KEY (ItemID) REFERENCES Items (ID) ON DELETE CASCADE,
  CONSTRAINT fk_Formats_SubProductType FOREIGN KEY (SubProductTypeID) REFERENCES SubProductTypes (ID) ON DELETE CASCADE
) ENGINE = InnoDB;

Che è stata "spezzata" nelle seguenti due:

CREATE TABLE Formats (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  ItemID INTEGER NOT NULL,
  SubProductTypeID INTEGER NOT NULL,
  Language SET('it','en','fr','de','sv','zh','cs','da','nl','fi','hu','ja','ko','no','pl','pt','ru','sk','es','tr') NOT NULL,
  CONSTRAINT pk_Formats PRIMARY KEY (ID),
  CONSTRAINT fk_Formats_ItemID FOREIGN KEY (ItemID) REFERENCES Items (ID) ON DELETE CASCADE,
  CONSTRAINT fk_Formats_SubProductType FOREIGN KEY (SubProductTypeID) REFERENCES SubProductTypes (ID) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE FormatsExtras (
  FormatID INTEGER NOT NULL,
  StorageURI LONGBLOB,
  Digest VARCHAR(40) NOT NULL DEFAULT '',
  CONSTRAINT fk_FormatsExtras_FormatID FOREIGN KEY (FormatID) REFERENCES Formats (ID) ON DELETE CASCADE
) ENGINE = InnoDB;

Normalmente il mio server lavora sulla prima, mentre la seconda viene usata soltanto quando serve estrapolare dei dati per visualizzare / stampare delle informazioni o per fare qualche controllo occasionale (tipo il controllo della firma digitale).

Il risultato è che le query che eseguo molto spesso sono state velocizzate, al costo di un leggero aumento dei tempi di calcolo quando servono i dati presenti nella seconda tabella, ossia raramente.

Non capisco, tu hai la prima tabella a cui ci colleghi la seconda... ma se la prima tabella ha necessariamente meno dati (altrimenti dove sta l'ottimizzazione) rispetto la prima soluzione "canonica", le informazioni riguardo ad esempio il "Language" dei prodotti salvati solo sulla seconda tabella dove li salvi?

[cdimauro]

Io non salvo soltanto alcuni "prodotti" sulla seconda tabella: li salvo tutti.

Cioé, se ho 100 record nella prima tabella, ho sempre 100 record anche nella seconda, dove ogni record della prima è collegato a uno e un solo record nella seconda (relazione 1:1).

Complessivamente non ho meno dati (anzi, ne ho leggermente di più perché la collona ID della prima tabella è duplicata anche nella seconda), ma semplicemente ho spostato delle colonne dalla prima alla seconda tabella.

Il vantaggio è che questo spostamento mi permette di compattare molto di più i dati della prima tabella, rendendo le operazioni di ricerca molto più veloci (perché vengono lette meno pagine dal db, e quindi eseguendo meno accesso al disco).

[_Claudio]

Originariamente inviato da cdimauro

Io non salvo soltanto alcuni "prodotti" sulla seconda tabella: li salvo tutti.

Cioé, se ho 100 record nella prima tabella, ho sempre 100 record anche nella seconda, dove ogni record della prima è collegato a uno e un solo record nella seconda (relazione 1:1).

Complessivamente non ho meno dati (anzi, ne ho leggermente di più perché la collona ID della prima tabella è duplicata anche nella seconda), ma semplicemente ho spostato delle colonne dalla prima alla seconda tabella.

Il vantaggio è che questo spostamento mi permette di compattare molto di più i dati della prima tabella, rendendo le operazioni di ricerca molto più veloci (perché vengono lette meno pagine dal db, e quindi eseguendo meno accesso al disco).

Capisco, beh sì per tabelle di dimensioni enormi può funzionare ma il vantaggio è dato solo dalla lettura di meno pagine dal disco.
E la soluzione rimane pur sempre poco ortodossa e aderente agli standard.
Inoltre comporta anche una modifica alla composizione delle query facendo salire a livello software tale modifica... tanto vale implementare a livello software la soluzione che ho proposto io con relativa politica di aggiornamento e sostituzione.

[cdimauro]

Originariamente inviato da _Claudio

Capisco, beh sì per tabelle di dimensioni enormi può funzionare ma il vantaggio è dato solo dalla lettura di meno pagine dal disco.

Hai detto niente: il collo di bottiglia dei DB è rappresentato spesso dal tempo speso per operazioni di I/O.

Ti assicuro che la mia soluzione funziona bene, specialmente con tanti dati.

E la soluzione rimane pur sempre poco ortodossa e aderente agli standard.

Perché, scusa? Le relazioni 1:1 esistono da quando ci sono i db. Questa è soltanto una (banale) applicazione.

Inoltre comporta anche una modifica alla composizione delle query facendo salire a livello software tale modifica...

Basta una semplicissima JOIN da manuale, soltanto nei casi in cui servono i dati parcheggiati nella seconda tabella.

tanto vale implementare a livello software la soluzione che ho proposto io con relativa politica di aggiornamento e sostituzione.

La soluzione che proponi è decisamente più complessa della JOIN di cui sopra eseguita una tantum, più raramente.

Inoltre non sempre è fattibile / utile, perché mi costringe a introdurre in un server centrale delle API per forzare il flush o l'update degli oggetti, visto che non sono l'unico ad accedere al DB per eseguire modifiche. API che ovviamente devono usare tutte le applicazioni.

Immagino che il modello a cui ti riferisci sia quello di hibernate, ma per quel che ci faccio con le mie applicazioni è più costoso a livello implementativo e meno performante.

My 2 cents.

[_Claudio]

Originariamente inviato da cdimauro

Hai detto niente: il collo di bottiglia coi DB è rappresentato dal tempo spero per operazioni di I/O.

Ti assicuro che la mia soluzione funziona bene, specialmente con tanti dati.

Perché, scusa? Le relazioni 1:1 esistono da quando ci sono i db. Questa è soltanto una (banale) applicazione.

Basta una semplicissima JOIN da manuale, soltanto nei casi in cui servono i dati parcheggiati nella seconda tabella.

La soluzione che proponi è decisamente più complessa della JOIN di cui sopra eseguita una tantum, più raramente.

Inoltre non sempre è fattibile / utile, perché mi costringe a introdurre in un server centrale delle API per forzare il flush o l'update degli oggetti, visto che non sono l'unico ad accedere al DB per eseguire modifiche. API che ovviamente devono usare tutte le applicazioni.

Immagino che il modello a cui ti riferisci sia quello di hibernate, ma per quel che ci faccio con le mie applicazioni è più costoso a livello implementativo e meno performante.

My 2 cents.

Sicuramente le relazioni 1:1 sono ammesse... dove progettualmente richieste però, ossia in base alla composizione dei dati.
La progettazione delle basi di dati in teoria esula del tutto dal problema dell'ottimizzazione dei tempi di accesso, cosa che si da per scontato faccia bene il DB.
In ogni caso non è comunque ottimale modificare le query sql da quelle concettualmente corrette (da manuale) anche per una semplice join.
Inoltre la progettazione di una soluzione come proposta da me andrebbe anzi benissimo proprio una struttura distribuita in cui ogni server periferico aggiorna i propri dati interni (tenendoli su ram in fase di esecuzione e non chiedendoli via rete, e salvandoli su disco serializzati quando se ne ha bisogno) e tiene una copia che si presume sia il più coerente possibile... insomma tipo le cache.
In caso di modifiche queste sarebbero effettuate nella maniera canonica, anche perchè in ogni caso vanno aggiornate entrambe le tabelle.

Poi, per carità, io ti parlo delle soluzioni da manuale, poi è ovvio che nella pratica ci sono 1000 altre problematiche che rendono quelle soluzioni poco praticabili, ma l'errore sta nel fatto di assumere come ottime in assoluto soluzioni locali ad uno specifico contesto e riproporle altrove.

[cdimauro]

Originariamente inviato da _Claudio

Sicuramente le relazioni 1:1 sono ammesse... dove progettualmente richieste però, ossia in base alla composizione dei dati.
La progettazione delle basi di dati in teoria esula del tutto dal problema dell'ottimizzazione dei tempi di accesso, cosa che si da per scontato faccia bene il DB.

Non è sempre così, lo sai: non ci sarebbe da analizzare il query plan dell'ottimizzatore e forzarne eventualmente un altro.

E' vero che sulla carta i db dovrebbero memorizzare dati e basta, ma li devono rendere accessibili per quanto serve all'applicazione. Per questo scegliamo accuratamente anche dove andare a piazzare gli indici. Altrimenti lasceremmo al db l'onere e l'onore di organizzarsi i dati come meglio crede.

In ogni caso non è comunque ottimale modificare le query sql da quelle concettualmente corrette (da manuale) anche per una semplice join.

Ma infatti non le ho cambiate. Per la precisione, quelle che il server utilizza normalmente non sono cambiate di una virgola, visto che ai campi spostati non accedeva. Soltanto una query per il sistema di reportistica è stata cambiata aggiungendo la necessaria join.

Inoltre la progettazione di una soluzione come proposta da me andrebbe anzi benissimo proprio una struttura distribuita in cui ogni server periferico aggiorna i propri dati interni (tenendoli su ram in fase di esecuzione e non chiedendoli via rete, e salvandoli su disco serializzati quando se ne ha bisogno) e tiene una copia che si presume sia il più coerente possibile... insomma tipo le cache.

Sì, è chiaro, ma mantenere la coerenza non è cosa facile. Bisogna mettere in piedi un meccanismo di notify tramite il quale comunicare l'aggiornamento effettuato a tutti gli attori in gioco.

In caso di modifiche queste sarebbero effettuate nella maniera canonica, anche perchè in ogni caso vanno aggiornate entrambe le tabelle.

Le modifiche non coinvolgono sempre entrambe le tabelle. Dipende sempre dai campi a cui si accede, e quindi avvengono quasi sempre su quelli della prima tabella.

Poi, per carità, io ti parlo delle soluzioni da manuale, poi è ovvio che nella pratica ci sono 1000 altre problematiche che rendono quelle soluzioni poco praticabili, ma l'errore sta nel fatto di assumere come ottime in assoluto soluzioni locali ad uno specifico contesto e riproporle altrove.

Esattamente. Infatti quello del caching non è una buona soluzione per le applicazioni che ho progettato, perché le richieste vanno a toccare dati che nella stragrande maggioranza dei casi sono eterogenei. Paradossalmente con un sistema di caching aggressivo mi ritroverei con la memoria dei server piena di dati per lo più inutili, mentre se adottassi un sistema di rimozione dei dati più vecchi di fatto renderei quasi del tutto inutile il caching.

[Gemini77]

Grazie a tutti e due per la grande quantità di informazioni a rugardo

credo d'aver capito che, la strada percorsa è giusta (anche se ogni tanto non sopporto di dover definire relazioni di inner e left join ... ma ci si abitua molto in fretta); mi resta sempre e cmq il problema pratico dei dati non standardizzati e presenti nelle tabelle accessorie.

per dirla con un esempio pratico (che mi capita spesso)

i possibili prodotti (che per ipotesi si chiamano "prodotto_1", "prodotto_2", "prodotto_n") hanno sicuramente 2 colori (in RGB, per comodità "FFFFFF", "000000"); però può capitare che qualcuno faccia un colore ad-hoc che per quel prodotto non è disponibile in origine (diventa quindi impossibile gestire il campo con una relazione, ma devo lasciare all'utente finale la possibilità di inserire un colore "a mano") il mio problema è: se il campo colore_id nella tabella prodotti è un relazione con la tabella colore; è impossibile inserire una stringa? e trovo impossibile gestire una "seconda relazione" con una tabella non standardizzata (che poi dovrò farla compilare dall'utenza finale, il che renderebbe il dato non certificato), non saprei come capire, lato software, a quale delle due tabelle devo relazionarmi, devo necessariamente usare un nuovo campo "altro" in più per ogni campo relazionato? una nuova tabella (usando forse la soluzione di cdimauro?)

è questo il mio principale dubbio


grazie

[cdimauro]

Originariamente inviato da Gemini77

Grazie a tutti e due per la grande quantità di informazioni a rugardo

credo d'aver capito che, la strada percorsa è giusta (anche se ogni tanto non sopporto di dover definire relazioni di inner e left join ... ma ci si abitua molto in fretta); mi resta sempre e cmq il problema pratico dei dati non standardizzati e presenti nelle tabelle accessorie.

per dirla con un esempio pratico (che mi capita spesso)

i possibili prodotti (che per ipotesi si chiamano "prodotto_1", "prodotto_2", "prodotto_n") hanno sicuramente 2 colori (in RGB, per comodità "FFFFFF", "000000"); però può capitare che qualcuno faccia un colore ad-hoc che per quel prodotto non è disponibile in origine (diventa quindi impossibile gestire il campo con una relazione, ma devo lasciare all'utente finale la possibilità di inserire un colore "a mano") il mio problema è: se il campo colore_id nella tabella prodotti è un relazione con la tabella colore; è impossibile inserire una stringa?

Esatto. Puoi inserire soltanto l'id del colore.

e trovo impossibile gestire una "seconda relazione" con una tabella non standardizzata (che poi dovrò farla compilare dall'utenza finale, il che renderebbe il dato non certificato), non saprei come capire, lato software, a quale delle due tabelle devo relazionarmi, devo necessariamente usare un nuovo campo "altro" in più per ogni campo relazionato? una nuova tabella (usando forse la soluzione di cdimauro?)

è questo il mio principale dubbio

grazie

La tabella che fa riferimento a colore_id è legata soltanto a una tabella tramite foreign key, per cui non puoi referenziare valori di altre tabelle.

Per risolvere il tuo problema non dovresti usare un id per il colore, ma memorizzare direttamente la stringa che lo rappresenta.
Al più la tabella dei colori la puoi usare come look-up, per fornire all'utente un elenco da cui selezionare velocemente un colore.

[Gemini77]

Originariamente inviato da cdimauro

Per risolvere il tuo problema non dovresti usare un id per il colore, ma memorizzare direttamente la stringa che lo rappresenta.
Al più la tabella dei colori la puoi usare come look-up, per fornire all'utente un elenco da cui selezionare velocemente un colore.

era la soluzione che volevo adottare, ma non so se percorrere questa strada o quella delle refenrenziazioni + secondo capo di testo ...

se tecnicamente è valida, forse conviene usare quella delle tabelle look-up?

[cdimauro]

Per il tipo di informazione che stai trattando, ti consiglierei di usare il mio suggerimento.

[Gemini77]

Originariamente inviato da cdimauro

Per il tipo di informazione che stai trattando, ti consiglierei di usare il mio suggerimento.

e look-up sia ...

grazie a tutti per le info

[_Claudio]

Originariamente inviato da cdimauro

Non è sempre così, lo sai: non ci sarebbe da analizzare il query plan dell'ottimizzatore e forzarne eventualmente un altro.

E' vero che sulla carta i db dovrebbero memorizzare dati e basta, ma li devono rendere accessibili per quanto serve all'applicazione. Per questo scegliamo accuratamente anche dove andare a piazzare gli indici. Altrimenti lasceremmo al db l'onere e l'onore di organizzarsi i dati come meglio crede.

La scelta degli indici è una pura questione di accesso da parte della componente software, che sia un vincolo nella definizione delle tabelle di un database è dato dal fatto che definendo un indice univoco per ogni record è facilitato il salvataggio e l'interfacciamento al DB. Dal punto di vista della progettazione del motore del DB non cambia poi molto avere gli indici o meno.

Ma infatti non le ho cambiate. Per la precisione, quelle che il server utilizza normalmente non sono cambiate di una virgola, visto che ai campi spostati non accedeva. Soltanto una query per il sistema di reportistica è stata cambiata aggiungendo la necessaria join.

Cambiare UNA query o tutto quanto da un punto di vista teorico e purista è pur sempre uno sfacelo...

Sì, è chiaro, ma mantenere la coerenza non è cosa facile. Bisogna mettere in piedi un meccanismo di notify tramite il quale comunicare l'aggiornamento effettuato a tutti gli attori in gioco.

Le modifiche non coinvolgono sempre entrambe le tabelle. Dipende sempre dai campi a cui si accede, e quindi avvengono quasi sempre su quelli della prima tabella.

Esattamente. Infatti quello del caching non è una buona soluzione per le applicazioni che ho progettato, perché le richieste vanno a toccare dati che nella stragrande maggioranza dei casi sono eterogenei. Paradossalmente con un sistema di caching aggressivo mi ritroverei con la memoria dei server piena di dati per lo più inutili, mentre se adottassi un sistema di rimozione dei dati più vecchi di fatto renderei quasi del tutto inutile il caching.

Io ho progettato (e sviluppato) un sistema del genere, il server centrale con i dati è rimasto tale e quale, quelli periferici che servivano gli host invece avevano una politica attiva di mantenimento della consistenza... a fronte di un leggero incremento dell'utilizzo della rete si aveva un elevato incremento reale di prestazioni e velocità di reperimento dati.

Originariamente inviato da Gemini77

Grazie a tutti e due per la grande quantità di informazioni a rugardo

credo d'aver capito che, la strada percorsa è giusta (anche se ogni tanto non sopporto di dover definire relazioni di inner e left join ... ma ci si abitua molto in fretta); mi resta sempre e cmq il problema pratico dei dati non standardizzati e presenti nelle tabelle accessorie.

per dirla con un esempio pratico (che mi capita spesso)

i possibili prodotti (che per ipotesi si chiamano "prodotto_1", "prodotto_2", "prodotto_n") hanno sicuramente 2 colori (in RGB, per comodità "FFFFFF", "000000"); però può capitare che qualcuno faccia un colore ad-hoc che per quel prodotto non è disponibile in origine (diventa quindi impossibile gestire il campo con una relazione, ma devo lasciare all'utente finale la possibilità di inserire un colore "a mano") il mio problema è: se il campo colore_id nella tabella prodotti è un relazione con la tabella colore; è impossibile inserire una stringa? e trovo impossibile gestire una "seconda relazione" con una tabella non standardizzata (che poi dovrò farla compilare dall'utenza finale, il che renderebbe il dato non certificato), non saprei come capire, lato software, a quale delle due tabelle devo relazionarmi, devo necessariamente usare un nuovo campo "altro" in più per ogni campo relazionato? una nuova tabella (usando forse la soluzione di cdimauro?)

è questo il mio principale dubbio


grazie

È impossibile inserire una stringa se il tipo definito è diverso, io direi di inserire una colonna in più nella tabella che contiene i prodotti con altro, come hai proposto te. Una nuova tabella con relazione 1:1 con quella principale dove c'è l'id e la colonna "altro" andrebbe comunque collassata nella tabella di partenza.

Originariamente inviato da cdimauro

Esatto. Puoi inserire soltanto l'id del colore.

La tabella che fa riferimento a colore_id è legata soltanto a una tabella tramite foreign key, per cui non puoi referenziare valori di altre tabelle.

Per risolvere il tuo problema non dovresti usare un id per il colore, ma memorizzare direttamente la stringa che lo rappresenta.
Al più la tabella dei colori la puoi usare come look-up, per fornire all'utente un elenco da cui selezionare velocemente un colore.

Esatto

[cdimauro]

Originariamente inviato da _Claudio

La scelta degli indici è una pura questione di accesso da parte della componente software, che sia un vincolo nella definizione delle tabelle di un database è dato dal fatto che definendo un indice univoco per ogni record è facilitato il salvataggio e l'interfacciamento al DB. Dal punto di vista della progettazione del motore del DB non cambia poi molto avere gli indici o meno.

Non si tratta soltanto di avere indici univoci: gli indici vanno aggiunti opportunamente in base alle ricerche effettuate su certi campi. Se la numerosità dei valori di un campo è abbastanza elevata e il campo viene confrontato spesso, gli si piazza un indice sopra, anche se non è univoco.

Questa è una cosa che può decidere soltanto il progettista del db, in base al carico di lavoro che è previsto (o che si è valutato anche in un secondo momento): non può farlo il db autonomamente (tante volte m'è pure capitato di dover correggere la query plan perché l'ottimizzatore aveva cannato clamorosamente).

Cambiare UNA query o tutto quanto da un punto di vista teorico e purista è pur sempre uno sfacelo...

Ma io sono un informatico: devo progettare sistemi reali, per cui se il gioco vale la candela lo faccio.

Io ho progettato (e sviluppato) un sistema del genere, il server centrale con i dati è rimasto tale e quale, quelli periferici che servivano gli host invece avevano una politica attiva di mantenimento della consistenza... a fronte di un leggero incremento dell'utilizzo della rete si aveva un elevato incremento reale di prestazioni e velocità di reperimento dati.

Sì, questo l'avevo capito, ma dipende anche dai dati. Evidentemente tu hai spesso delle richieste agli stessi dati, per cui il tuo modello dà dei risultati decisamente migliori rispetto alla classiche query eseguite per ogni richiesta.

Io invece ricevo spesso richieste di dati diversi, per cui il caching è sostanzialmente inutile a fronte di una notevole complessità del codice.

Non v'è dubbio che in condizioni diverse il tuo approccio sarebbe preferibile.

Tra l'altro faccio già caching "massivo" per alcune tabelle che vengono usate molto spesso (e sono abbastanza piccole): le precarico e lavoro esclusivamente in memoria; quando i dati cambiano oppure a intervalli di tempo predefiniti eseguo il refresh delle cache.
Per tutto il resto, dove l'accesso ai dati è quasi causale, ho preferito adottare la politica della query per ogni richiesta, come dicevo prima.

La normalizzazione come tu l'hai posta è giusta ovviamente.

I concetti stessi di "piegare" o "rompere" le regole di normalizzazione quando necessario (tempi di sviluppo, praticità, ecc.) vanno benissimo quando fatti con coscienza.

Il discorso del campo "altro" che hai esposto è verissimo e a chiunque (pure a me) è capitato spesso di dovervi far fronte.

Purtroppo in questo caso, a mio modo di vedere, non è tanto un problema di normalizzazione, di codice ecc. quanto un problema a livello più alto ovvero di "architettura dell'informazione".

In una categorizzazione precisa ed esauriente voci come "altro, varie, miscellanea" ecc. non dovrebbero esistere in quanto sono solo un calderone di ripiego per una scarsa ottimizzazione dell'architettura informativa del proprio sistema.

Da qui quindi la derivazione di tutti i problemi di sorta durate la fase di normalizzazione di un DB e non solo.

Il mio consiglio è quindi prima di tutto, se possibile, cercare di eliminare situazioni "pericolose" come queste. Nel caso ove non fosse possibile allora credo stia di caso in caso decidere come meglio procedere.