[MySql]aiuto per progettazione tabelle

[RaouL_BennetH]

Ciao a tutti

Da questo vecchio 3d: http://www.hwupgrade.it/forum/showthread.php?t=1641224

e' emerso che il database era stato progettato male, e, di conseguenza, stavo cercando tramite vari accrocchi di giungere comunque ad un risultato decente.

Ogni tentativo è stato vano, ed ora ho quindi la possibilità di poter riprogettare le tabelle del database.

In merito a questo, cercherò di spiegare bene il problema:

Questo programma dovrebbe gestire le ore lavorate da dei dipendenti durante un mese.

Si parte dalla lavorazione del singolo dipendente per ciascun cantiere di lavoro dove opera.

La tabella della lavorazione singola, non l'ho modificata perchè ritengo sia ancora valida, ovvero:

id_job (unsigned int)
id_mese (unsigned int, 2)
id_cantiere (unsigned int, 8)
id_dipendente (unsigned int, 5)
giorno (unsigned int, 2)
assenze (varchar, 4)
parametroBase (double)
oreEffettive (double)
differenzaParametro (double)

Ora, devo progettare la tabella che mi dovrà contenere tutto il lavorato del mese di tutti i dipendenti, e, per forza di cose, devo ottenerla in questa forma:

id_Dipendente | Cognome | Nome | giorno1 | giorno2 | etc.. giorni del mese
------------------------------------------------------------------------
12      Benneth    RaouL            5           5
12      Benneth    RaouL            4           6
12      Benneth    RaouL           -1           1

avendo quindi in sostanza, tre righe per ogni dipendente dove:

nella prima riga, in corrispondenza del giorno, c'è il parametro di base
nella seconda riga, in corrispondenza del giorno, c'è la somma delle ore effettivamente lavorate durante il mese
nella terza riga, la differenza tra il parametro base e la somma delle ore effettivamente lavorate.

Per molti di voi magari è banale, ma premettendo che io non ho ne basi di dati acquisite, non sono universitario, ma solo un appassionato, mi dareste una mano per la progettazione di questa tabella?

Grazie

RaouL.

[cionci]

Raul secondo me al risultato ci arrivavi anche con l'altro formato, solo che ti devi preoccupare un po' di più della visualizzazione dei dati, come ti avevo già spiegato.

Continuo a non capire perché tu insista ad avere un risultato finale della query fatto in quel modo, se è per semplificare la visualizzazione dei dati allora il problema non è nella tabella, sei te che devi lavorare di più alla visualizzazione.

Il formato naturale per ottenere quella tabella dal database è:

id_Dipendente Nome Cognome Giorno Ore
22 xxxxx yyyy 1 10
22 xxxxx yyyy 2 7

etc etc

Quindi la forma normale delle tabelle è:

id_Dipendente Nome Cognome
id_Dipendente Mese Giorno ore

Con la parte in grassetto chiave primaria.

[RaouL_BennetH]

Ciao cionci

Il fatto è che sono riuscito a far abolire quella tabella(che anche tu avevi visto con orrore ) che aveva come nomi delle colonne: ven1, sab2, dom3 etc... perchè veniva generata a run-time, quindi ne volevo progettare una nuova.

Per la visualizzazione, è per me un problema insormontabile al momento perchè con il linguaggio che sto utilizzando come front-end per i dati (C#), anche con l'oggetto datagridview non c'è modo di ottenere delle sotto righe. Forse per questo vorrei ottenere tutto dal db, in modo da utilizzare la griglia di C# solo per la visualizzazione e forse, sempre per questo, sto cercando di mettere tutto in un'unica tabella

[cionci]

Io non ci credo che non ci sia modo di scrivere ciò che vuoi all'interno del datagrid...c'è di sicuro

Una modifica alle tabelle:

id_Dipendente Nome Cognome
id_Dipendente Data ore

[cionci]

Sicuramente puoi fare il binding del datagrid ad un vettore...basta che tu generi quel vettore tramite codice ed è già tutto pronto...

[cionci]

Anzi, si può fare anche il binding di classi:

http://www.c-sharpcorner.com/UploadF...idBinding.aspx
http://support.microsoft.com/kb/316303
http://www.akadia.com/services/dotne..._datagrid.html

[RaouL_BennetH]

Ok, ci provo

Nel frattempo grazie infinite.

RaouL.

[RaouL_BennetH]

chiedo perdono, ma evidentemente la mia ottusità raggiunge livelli che non sospettavo

Non ci riesco per niente a tirar fuori la visualizzazione di questi dati come mi è stato richiesto

[gugoXX]

non mi e' chiara una cosa.
Vorresti una QUERY che ritornasse un numero variabile di colonne?

La rappresentazione che stai cercando si chiama
PIVOTING
Ma non si ottiene direttamente cosi' da una query.
Si ottiene da una query normalizzata, che viene usata per calcolare automanticamente la PIVOT.
Una cosa che excel per esempio sa fare benissimo in maniera nativa.

O forse ho capito male il problema?

[RaouL_BennetH]

Originariamente inviato da gugoXX

non mi e' chiara una cosa.
Vorresti una QUERY che ritornasse un numero variabile di colonne?

L'unica cosa che può variare è il numero di giorni in base al mese scelto, e questo riesco a farlo anche collegando i dati, ma solo per una riga

La rappresentazione che stai cercando si chiama
PIVOTING
Ma non si ottiene direttamente cosi' da una query.
Si ottiene da una query normalizzata, che viene usata per calcolare automanticamente la PIVOT.
Una cosa che excel per esempio sa fare benissimo in maniera nativa.

O forse ho capito male il problema?

Excel purtroppo non lo posso utilizzare

[RaouL_BennetH]

@ gugoXX :

ho trovato l'esempio perfetto che spiega meglio ciò che vorrei fare:

http://bytes.com/forum/thread654054.html

Sto cercando in lungo e in largo ma in MySql non trovo nulla di simile

[gugoXX]

Eh lo so.
Devi farlo con SQL standard, ma ti avviso che sara' lungo, brutto e assolutamente non professionale.
Ma alla fine, a cosa ti serve questa rappresentazione?
Mi spiego, se sara' una parte messa su un sito web, allora ci sono componenti grafiche che fanno il PIVOTING.
Se fosse un foglio excel, allora l'ho gia' detto.
Se fosse una Winform in C# (ma immagino anche in JAVA) ci sono componenti per fare il PIVOTING pure li'
ma se proprio devi farlo in SQL, anche se lo sconsiglio fortemente:

1) Normalizzazione del dato. Ottenere 3 righe per ciascun utente, con una sola colonna di dato e 1 colonna che dica il tipo di dato trattato:

SELECT id_job,id_mese,id_dipendente,giorno,parametroBase as value,'BASE' as tipo FROM tabella
UNION ALL
SELECT id_job,id_mese,id_dipendente,giorno,oreEffettive ,'EFFETTIVE' FROM tabella
UNION ALL
SELECT id_job,id_mese,id_dipendente,giorno,differenzaParametro ,'DIFF'  FROM tabella

Per evitare "problemi" di aggiornamenti, ti consiglierei addirittura di eliminare la colonna "differenzaParametro" sulla tua tabella originale, e di calcolare tale differenza al volo, qui, nella precedente query

2) DECODING dei dati trattati su tutti i valori interessati

SELECT id_job,id_dipendente, tipo,
           CASE WHEN giorno=1 THEN value ELSE 0 end as value01,
           CASE WHEN giorno=2 THEN value ELSE 0 end as value02,
           CASE WHEN giorno=3 THEN value ELSE 0 end as value03,
           ...
           CASE WHEN giorno=30 THEN value ELSE 0 end as value30,
           CASE WHEN giorno=31 THEN value ELSE 0 end as value31
FROM (la select di prima)
WHERE mese = @parametro del mese che ci serve

3) Raggruppamento dei dati (ci sono i cantieri di mezzo immagino, e magari anche altro, che rendono la subquery fin qui ottenuta non ancora secondo specifiche)

SELECT id_dipendente,tipo,
           SUM(value01) as value01,
           SUM(value02) as value02,
           SUM(value03) as value03,
            ...
           SUM(value30) as value30,
           SUM(value31) as value31,
FROM (la subquery di prima)
GROUP BY id_dipendente,tipo

Per costruzione, leggendo l'ultima GROUP BY, avrai 3 righe per ciascun dipendente, che e' quanto ti aspetti
Se ti serve "stampare" anche qualcosa relativo ai dipendenti (nome, cognome, etc), a questo punto potrai mettere in JOIN questa ultima query con l'anagrafica dei dipendenti.

[RaouL_BennetH]

Originariamente inviato da gugoXX

..cut..

In attesa di leggermi con calma tutto quanto da te postato, che quindi corrisponde a ciò che mi aveva già suggerito anche cionci, tu hai accennato a dei componenti winforms usando C# che possono fare questo..

Me li potresti indicare?

[cionci]

Ripeto, la strada più semplice è non farlo fare a MySQL.
Basta fare il binding di un vettore di classi sul datagrid. Cosa non ti è chiaro dai link che ti avevo messo sopra ?

[gugoXX]

E certo che e' analogo a quanto detto da Cionci...

Comunque una vota l'avrei fatto con uno dei tanti componenti CUBE OCX presenti sul mercato (ma ancora oggi presenti)

Oggi pero' lo farei con il componente ReportViewer integrato, che espone anche l'oggetto "Matrix", che non e' nient'altro che il Pivoting.
A questo punto ti basterebbe fermarti alla prima query (opportunamente trasformata in vista), e darla in pasto al reportviewer, per ottenere quanto richiesto (metti il giorno sulle colonne, persona e "tipo" sulle righe, in quest'ordine, e poi fa tutto lui)
Non so se il Datagrid standard espone la possibilita' di pivoting, altrimenti sarebbe ancora piu' facile li'.

[RaouL_BennetH]

Ecco, finalmente con il report viewer inizio a vedere qualcosa che somiglia al risultato desiderato ....

La vista come mi consigli di crearla considerando che la tabella di origine è questa:

id_ore
id_anno
id_mese
id_dipendente
codiceLavoro
cognome
nome
giorno
assenze
parametroBase
lavoratoEffettuato
lavoratoExtra
differenzaParametro
totaleGiornaliero
maggiorazione

ed io dovrei quindi ottenere:

id_dipendente, codiceLavoro, cognome, nome, giorno, totale ?

[RaouL_BennetH]

Come posso fare se succede questo:

(premesso che finora sono arrivato a tirare fuori questa query)

select id_dipendente, codiceLavoro, cognome, nome, giorno, parametroBase as value, 'BASE' as tipo from oregamma
union all
select id_dipendente, codiceLavoro, cognome, nome, giorno, sum(totaleGiornaliero) as value, 'EFFETTIVE' FROM oregamma
union all
select id_dipendente, codiceLavoro, cognome, nome, giorno, sum(differenzaParametro) as value, 'DIFF' FROM oregamma

Nella tabella oregamma, ho anche una colonna "assenze". vorrei fare in modo che se per esempio il totaleGiornaliero è vuoto o pari a zero, nelle 'EFFETTIVE' mi riporti il codice dell'assenza (per esempio F oppure M oppure AG).

[RaouL_BennetH]

Originariamente inviato da cionci

Ripeto, la strada più semplice è non farlo fare a MySQL.
Basta fare il binding di un vettore di classi sul datagrid. Cosa non ti è chiaro dai link che ti avevo messo sopra ?

Che io a priori non so quanti dipendenti avranno lavorato e per quanti giorni, oltre al fatto che(ma di sicuro perchè non ho capito qualcosa) riesco solo ad ottenere una visualizzazione per colonne e non per righe

E mi sa che devo abbandonare anche il reportViewer perchè non permette di generare la formattazione del report a run-time.

Con la griglia, almeno riesco ad impostare il nome delle colonne dei giorni con il nome e numero dei giorni presenti nel mese di lavorazione, riesco ad evidenziare le colonne dei sabati e delle domeniche e dei festivi con colori diversi con un semplice metodo. Leggo invece che con il report viewer non è possibile...

[gugoXX]

Puoi cercare di recapitolare?
1) l'instruzione SQL che hai scritto non e' standard, non so cosa faccia e non voglio neppure saperlo.
Non puoi scrivere una SUM con anche altre cose, senza scrivere una GROUP BY. Dovrebbe funzionare altrettanto bene anche senza le 2 SUM che hai messo.

2) La tabella di origine che hai postato ora dove e' saltata fuori? E' diversa da quella del post iniziale.

3) Vuoi una colonna che ogni tanto sia un numero e ogni tanto sia una stringa. Bisogna fare un post process finale, prima di passare il tutto al report viewer, sempre se gli piace. Il concetto e' che le tabelle PIVOT hanno all'interno sempre dei numeri, e non garantisco che con le stringhe possa funzionare.
Inoltre mi sa che e' anche logicamente scorretta. Cosa vorresti che venisse fuori se un dipendente, in un determinato giorno, e' stato assente su un cantiere per il motivo 'F', e assente su un altro cantiere per il motivo 'M'?
Tale fatto magari puo' non capitare, ma lo si sta permettendo al database.
E' un errore di Design, che ti porta ad avere il problema "logico" indecidibile (per te, figurati per il database) appena descritto.

[cionci]

Originariamente inviato da RaouL_BennetH

Che io a priori non so quanti dipendenti avranno lavorato e per quanti giorni, oltre al fatto che(ma di sicuro perchè non ho capito qualcosa) riesco solo ad ottenere una visualizzazione per colonne e non per righe

La visualizzazione per colonne la ottieni facendo il binding del vettore sul datagrid, basta esporre N proprietà con N pari ai giorni lavorativi