[POSTGRESQL] Programmazione database e "best practices"

[Shadow976]

Pare che non sia l'unico folle a studiare i propri codici al pc durante un sabato pomeriggio di fine agosto...

Parlando di cose serie, avrei tre quesiti in merito al database della mia applicazione, che sto perfezionando in modo maniacale. Vi ringrazio in anticipo per l'attenzione ed il tempo che eventualmente vorrete dedicarmi. Premetto che non voglio sapere tecniche per scrivere codici, o soluzioni a problemi, ma semplicemente la migliore alternativa in assoluto, anche dal punto di vista dell'eleganza del progetto, quindi vorrei risposte solo dai programmatori di "vecchia maniera", quelli per intenderci dai quali potrei imparare a gattonare per i prossimi dieci anni (senza escludere che anche chi è alle prime armi possa insegnarmi di tutto e di più).

1) Ho sentito dire da un collega di Ingegneria che i più grossi software, o quelli che si prevede siano destinati a diventare abbastanza rilevanti per la propria attività, hanno come regola formale quella di avere nomi di tabelle e campi (per classi e variabili già lo sapevo) in inglese. Vi risulta?
2) La mia tabella è nata con una chiave primaria su ID autoincrementale. Perfezionando il mio progetto, in quella tabella ho aggiunto un campo (combinazione data + ora + persona sotto forma di stringa) che non sarà MAI duplicato. Ma così violo una delle regole sulla terza forma normale, ovvero ho una chiave di troppo che non sembra di particolare utilità! Posso eliminare l'ID autoincrementale o è buona norma lasciarlo comunque in ogni tabella anche se una chiave univoca già esiste?
3) Questa è più tosta.

Nel mio software gestione soci, il socio può essere chiamato in causa per diverse ragioni, ad esempio per operazioni_contabili o per agenda_adempimenti. Bene, per tante ragioni (conservazione dati, loro storicizzazione all'epoca dell'inserimento, conservazione relazioni se si elimina il socio e tante altre) ogni volta che il socio viene coinvolto in uno di questi eventi il mio programma "copia" tutti i suoi dati dalla tbl_soci in una apposita table_operazioni_soci o table_agenda_soci così anche se il socio della tbl_soci viene eliminato le operazioni e gli appuntamenti non saranno mai orfani. E' corretto, come sto facendo, creare una table per ogni tipo di evento nel quale il socio può essere coinvolto, o potrei creare una table_soci_secondaria per tutti i soci coinvolti in qualche evento, nella quale i dati del socio saranno copiati ogni volta che il socio farà qualcosa, ovviamente in tal caso aggiungendo un campo che indica per che tipo di evento il socio è stato copiato lì dentro (appuntamento, operazione, ecc)?

O in parole più semplici, è più importante la regola che vorrebbe una tabella per ogni "ambito funzionale" del proprio programma, o quella che non vorrebbe due tabelle con identici campi, sia nel numero che nel tipo?

Grazie tantissimo.

[MacApp]

• 1. Certo, in generale alla lunga è economincamente più conveniente;
• 2. Io uso un campo ID autoincrementale per ogni tabella che creo, quindi piazzo alla bisogna indici univoci.
  Ad esempio nel tuo caso sostituierei il campo stringa dataora+persona in due campi distinti dataora e riferimento a persona, quindi piazzerei un indice univoco composto dai due campi dataora e riferimento a persona;
• 3. Creerei una tabella di persone, una tabella di soci contenente (oltre all'omnipresente ID) un riferimento alla persona ed allo stato del socio: cosi' quando elimi il socio d fatto non lo elimi dalla tabella dei soci ma ne cambi solo lo stato; quindi una tabella storico eventi contenente riferimenti al socio e all'anagrafica eventi;

[comas17]

1) Non credo ci sia differenza "tecnica" tra l'utilizzare nomi di tabelle e/o di colonne in inglese o, per esempio, in italiano.
Di certo l'inglese è comunemente utilizzato in quanto "lingua universale"; in un grosso progetto è probabile che lavorino team di più persone, magari di nazionalità diverse, e che il lavoro di sviluppo e mentenimento duri per anni. L'utilizzo di un linguaggio "comune" è certamente un vantaggio.
Tieni presente peraltro che in molti casi il nome delle tabelle o delle colonne non è "parlante" quindi non ha necessariamente un nome o una sigla che, in una qualche lingua, significhino qualcosa (tanto per fare un esempio i nomi di alcune tabelle di SAP sono DD02T, DD02L ed i nomi di colonne sono MANDT, WERKS, MMSTD, etc)
Peraltro, visto che proprio l'inglese è anche la lingua dei vari linguaggi/dialetti SQL (select, insert, drop, index, table etc) l'uso di una lingua NON inglese eviterebbe il rischio di utilizzare parole "chiave" come nome di tabella o di colonna (a cui si ovvia facilmente usando la sintassi [parola], ma tant'è...)

2) Anche io lascerei il campo ID autoincrementante come chiave univoca; è più semplice da gestire e da utilizzare (chiavi esterne, query, etc). Introdurre un campo stringa dataora + persona mi sembra un po' forzato e rischi di non poter utilizzare questo campo per alcun altro scopo. Meglio, come suggerisce MacApp, utilizzare eventualmente due campi separati (dataora e id persona) indicizzati

3) Eviterei proprio l'introduzione e l'utilizzo di "doppioni" (tabelle identiche con dati replicati); non solo aumenti inutilmente le dimensioni del database ma rischi di ritrovarti con maggiori problemi nella gestione successiva di query o reportistica (se cerco informazioni storiche relative ad un socio, a seconda del fatto che sia ancora presente o che sia stato "cancellato", dovrei andare a fare ricerche in una tabella oppure in un'altra...)
Fai attenzione che la "cancellazione" di qualcosa (ad esempio di un socio, ma più in generale di qualunque cosa) non necessariamente prevede l'eliminazione dei record e delle loro informazioni (con la relativa necessità di gestire copie, archiviazioni, record orfani) ma molto più opportunamente, come suggerisce MacApp, puoi gestire il tutto con un campo aggiuntivo "stato" che valorizzerai in maniera adeguata (es. 0 = nuovo socio non abilitato, 1 = socio presente, 2 = socio sospeso, 3 = socio cancellato, etc...ho messo descrizioni a caso... solo come esempio...)
L'ID autoincrementante assegnato ad ogni socio al momento del suo inserimento resterà comunque SEMPRE presente nel database (anche dopo l'eventuale "cancellazione" del socio) e ti servirà come chiave per collegare tutte le informazioni a lui relative

[Shadow976]

Non so veramente come ringraziarvi, parlo a tutti e due, siete stati gentilissimi.

1. RISOLTO: Prevedo che le dimensioni del progetto possano aumentare considerevolmente quindi userò l'inglese.

2. RISOLTO: nel modo che segue, in modo da conciliare le vostre risposte.

2a Quel nuovo campo chiave avrebbe legato soci ed eventi. Ma negli eventi quel campo è una concatenazione di altri campi; viola comunque le regole sulla normalizzazione e quindi lo eliminerò, ed al bisogno eseguirò una query che "coinvolga" quei campi singolarmente (...WHERE data = ? AND time = ? AND socio = ? eccetera). In tal modo posso lasciare l'id, perché quei campi, presi singolarmente, non costituiscono una chiave in quanto il codice fiscale, da solo, può ripetersi in più righe, così come la data, anch'essa può ripetersi.
NON HO AGGIUNTO ALCUN CAMPO OLTRE A QUELLI STRETTAMENTE INDISPENSABILI PER LE INFORMAZIONI NECESSARIE; HO MANTENUTO L'ID UNIVOCO AUTOINCREMENTALE; HO RISPETTATO LE REGOLE SULLA TERZA FORMA NORMALE IN QUANTO NESSUNO DEI RESTANTI CAMPI È TALE DA ESSERE IMPOSSIBILITATO A RIPETERSI.

2b Anche nella tabella soci_eventi (ovvero non la tabella principale persone, ma quella nella quale sono accodate le persone che sono coinvolte in operazioni o eventi, nel punto 3c spiegherò perché è necessaria) toglierò quel nuovo campo con la chiave lunghissima creata ad hoc, aggiungendone altri due, data ed ora. Riguardo il primo, un campo mi sarebbe stato comunque INDISPENSABILE ed INEVITABILE per legare la persona all'evento. Riguardo il secondo campo ora, meglio aggiungere un campo semplice in più e lasciare che i legami concatenino più campi tramite una bella query che impostare un campo in meno ma con una chiave complicatissima e magari frutto della concatenazione tra altri campi.
È IMPOSSIBILE CHE UNO STESSO SOCIO SVOLGA DUE OPERAZIONI DIVERSE NELLO STESSO GIORNO ED ALLA STESSA ORA PRECISA, QUINDI I CAMPI CODICE FISCALE, DATA ED ORA SONO PIÙ CHE SUFFICIENTI A CREARE I LEGAMI. HO MANTENUTO LA CHIAVE SULL'ID AUTOINCREMENTALE, HO AGGIUNTO IN PIÙ IL SOLO CAMPO ORA MA CON QUEL CAMPO IN PIÙ HO GUADAGNATO UNA EFFICIENZA BEN PIÙ ELEVATA DELLA BASE DI DATI E SOPRATTUTTO RISPETTO LA TERZA FORMA NORMALE, NON ESSENSO NESSUNO DI QUEI CAMPI ESCLUSO L'ID, SE PRESI DA SOLI, IRRIPETIBILI. LASCERÒ L'INCOMBENZA DELLA CREAZIONE DELLE RELAZIONI AD UNA QUERY BEN COSTRUITA.

3. COMPROMESSO TRA LE VOSTRE RISPOSTE: Posto che il sistema di eliminazione logica tramite flag è già usato in tutte le mie tabelle, una tabella aggiuntiva persone è indispensabile. Diversamente non avrei la storicizzazione del socio, ovvero non potrei conoscere i suoi dati all'epoca dello svolgimento della operazione. D'altronde però usare tante tabelle persone quanti sono i tipi di eventi (operazioni contabili, eventi organizzati ecc) è assolutamente sprecato e ridondante. Quindi creerò una sola tabella nella quale saranno "copiati" i dati di tutti i soci coinvolti in qualche evento.
QUI E' SOLO DA NOTARE CHE DOVRÒ AGGIUNGERE IN TALE TABELLA UN NUOVO CAMPO TIPO_EVENTO PER FAR "CAPIRE" ALLE QUERY IN GIOCO IN QUALE TABELLA DOVRANNO ANDARE A CERCARE L'EVENTO CHE INTERESSA LA PERSONA INDICATA.

In definitva, aggiungendo due soli campi rispetto al numero in origine previsto (ovvero ora e tipo_evento), ho eliminato quella chiave lunga e complicata creata ad hoc ed il relativo campo; eseguirò relazioni multiple tramite query e non più tramite il suddetto campo; pur mantenendo l'id rispetterò la terza forma normale in quanto nessun campo oltre all'id sarà singolarmente univoco; ridurrò il numero di tabelle allo stretto indispensabile. Che ne pensate? Siete d'accordo sulle mie conclusioni?

[comas17]

Originariamente inviato da Shadow976
Che ne pensate? Siete d'accordo sulle mie conclusioni?

Non conoscendo l'intera portata del progetto (ed il suo scopo) non è semplice fare valutazioni
Adesso mi ri-leggo con calma quello che hai scritto (voglio capire meglio questa questione della storicizzazione dei dati dei soci per vedere cosa si può fare...)

P.S. com'è andata a finire la questione del backup / ripristino del db ?

http://forum.html.it/forum/showthrea...readid=1350650

[Shadow976]

Quanta disponibilità! Sono qui che pendo dalla tua tastiera in attesa di risposte...

Con enorme tristezza (quando mi prefiggo un risultato sono capace di 3 notti ininterrotte al pc ma quando lo fallisco mi demoralizzo non poco!) devo constatare che riguardo il post da te segnalato:

1) La creazione del db fatta da software è impossibile. L'utente deve comunque prendersi la briga di creare un nuovo db dai pannelli di gestione del suo hosting, quindi al primo avvio del mio software un pò come avviene con molti cms dovrà indicare tali credenziali ed il mio software popolerà il db. Insomma, non lo creerà per davvero. Capisco che comunque quanto volevo fare era davvero impossibile, ogni motore db ha password di amministrazione diverse per installazioni diverse (per fortuna). Sono (tristemente) soddisfatto, va bene così.
2) Riguardo il backup, in Posgtre dovrei eseguire un dump ed il provider non mi dà i privilegi per farlo (Plesk, il sistema che usa il mio provider, non permette di accedere direttamente al db). Potrei imporre di installare il mio software solo su server dedicati senza plesk, perdendo una fetta enorme di clienti. Quindi lasciamo stare, consiglierò a chi gestisce la rete sulla quale sarà installato il mio programma di "schedulare" lato server dei backup automatici, ma in questo modo la cosa esula dalla mia applicazione. Sono (tristissimamente) soddisfatto così, speravo che senza scomodare quell'odiato dump si potesse creare una copia di un db Postgre solo con i comuni comandi SQL, ma forse non è così. Strano, combinando i comandi SQL, per i quali non serve alcun privilegio, si può fare di tutto. Tutto tranne una cosa così importante. Davvero strano.
3) Riguardo il ripristino, risolto! Semplicemente si prende il file sql del backup e lo si esegue.



EDIT: Preferirei prima risolvere questo post, poi magari riuppo il post sulla creazione /backup con queste mie ultime considerazioni, per ora se già potessi rispondermi su questo quesito mi faresti un regalo.

[comas17]

Punti 2 e 3, alcune riflessioni generiche...
E' vero che con comandi SQL si può fare tutto o quasi (con SQL Server anche fare il backup del database) ma non dimentichiamo l'utilizzo dei permessi e non confondiamo quelle che sono operazioni "normali" sul database (inserimenti, cancellazioni, aggiornamenti) con quelle che sono invece operazioni di "manutenzione", quali appunto il backup

Assegnando opportunamente i privilegi io posso anche impedire di eseguire operazioni banali quali aggiornamenti o cancellazioni, quindi non che sia sempre vero che si possa fare "quasi tutto senza privilegi".

Io non sarei così scontento del fatto che i miei utenti (quantomeno i normali "user") non possano fare il backup dell'applicativo. Il backup può anche contenere dati importanti (personali, sensibili ?) e se chiunque potesse farlo chi mi assicura che poi non se lo porta a casa o ne fa un uso scorretto ? Una cosa è che l'utente (le cui operazioni quando è collegato al database possono essere tracciate) vada a cancellare o modificare dei record (con i giusti permessi lo posso impedire) ed un'altra è che questo utente possa portarsi via i dati dell'azienda senza che nessuno lo sappia

EDIT: mi sono accorto solo ora del tuo edit... ok, nessun problema, vado a rileggere il post precedente, vediamo se mi viene qualche idea/interrogativo ...

[comas17]

Fammi capire bene...provo a riassumere "terra terra"

Tu hai una tabella con i dati di ogni socio; questi dati non sono "statici" ma possono variare nel corso del tempo...
Quando viene creato un nuovo evento tu vuoi registrare i dati dei soci che sono coinvolti ma essendo essi variabili non puoi semplicemente indicare l'ID del socio perchè esso ti riporterebbe solo ai valori "attuali" che potrebbero essere diversi da quelli di quando c'è stato l'evento

Ho capito bene ?

Non riesco a capire la frase in neretto "QUI E' SOLO DA NOTARE CHE DOVRÒ AGGIUNGERE IN TALE TABELLA UN NUOVO CAMPO TIPO_EVENTO PER FAR "CAPIRE" ALLE QUERY IN GIOCO IN QUALE TABELLA DOVRANNO ANDARE A CERCARE L'EVENTO CHE INTERESSA LA PERSONA INDICATA."

Hai già un'idea di quali siano le query/report/ricerche che vuoi andare a fare ? es. a quali eventi ha partecipato il socio X, oppure quali soci erano presenti all'evento Y...
Quanti diversi tipi di "eventi" ci sono ? vengono memorizzati in tabelle diverse e separate ? hai una tabella di "anagrafica" degli eventi, in cui associ ad ogni diverso tipo un identificativo univoco (forse è proprio quel campo tipo_evento di cui parli...)

Prova a dare qualche dettaglio in più su questa parte...

[Shadow976]

IMPORTANTISSIMA RIFLESSIONE

Atteso che la storicizzazione dei dati era chiesta più per necessità relative a consultazioni dell'Autorità Giudiziaria, considerato che nel mio sofwtare voglio implementare un sistema di log che su una tabella a parte tiene traccia di tutto quanto avviene scrivendo le query e la loro data e ora di esecuzione in una tabella a parte, considerato che ogni socio è gestito in modo piuttosto complesso, con cinque tabelle (soci, soci_indirizzi, soci_telefoni, soci_operazioni, soci_email) per un totale di quasi 60 campi, visto che gli eventi sono moltissimi e che quindi ogni socio sarebbe ripetuto per un sacco di volte, ma soprattutto ribadendo il sistema di log di cui sopra...

QUASI QUASI ELIMINO IL SISTEMA DI COPIA DATI SU TABELLA SEPARATA

Avrei lo stesso vantaggio (l'Autorità Giudiziaria potrebbe vedere subito eventuali modifiche ai dati da quella tabella di log) e ridurrei le dimensioni del database di moltissimo, diciamo forse un terzo o un quarto di quelle attuali, rendendolo anche enormemente più veloce!

CHE NE DICI?

EDIT: Grazie per la tua risposta, che mi ha aiutato a riflettere!

[comas17]

Originariamente inviato da Shadow976
IMPORTANTISSIMA RIFLESSIONE

QUASI QUASI ELIMINO IL SISTEMA DI COPIA DATI SU TABELLA SEPARATA

Avrei lo stesso vantaggio (l'Autorità Giudiziaria potrebbe vedere subito eventuali modifiche ai dati da quella tabella di log) e ridurrei le dimensioni del database di moltissimo, diciamo forse un terzo o un quarto di quelle attuali, rendendolo anche enormemente più veloce!

CHE NE DICI?

L'eliminazione di dati duplicati (e la conseguente riduzione di dimensioni del DB) sono certamente positive, ma ad essere sincero non ti seguo... :master:
(non mi piaceva sin dall'inizio l'idea di tenere copie su tabelle separate... )

Originariamente inviato da Shadow976
EDIT: Grazie per la tua risposta, che mi ha aiutato a riflettere!

Son contento... ma quale delle due ? La prima ?