Le tabelle del database sono, per loro natura, creature bidimensionali. La versione semplificata del tuo chiarimento, che è ore per nome e attività, funziona bene perché ci sono solo le dimensioni dei dati (nome e attività). L'introduzione della posizione aggiunge una terza dimensione e il fatto che tu debba iniziare a pensare a come archiviare i dati in qualcosa di diverso da un tipo nativo significa che hai superato il modello.
Fortunatamente, c'è normalizzazione , che è il processo per assicurarti di evitare la duplicazione nelle tue tabelle pensando ai tuoi dati in termini di cosa sono tutti i singoli pezzi e come si relazionano tra loro. (L'ultimo bit è il motivo per cui i database SQL sono chiamati relazionali .)
Guardando il tavolo nella tua domanda, vedo quattro cose:
- Ore lavorate
- I dipendenti
- Attività
- Locali
Una cosa che potresti notare è che hai lavorato molte ore e ognuna di queste figure può essere legata a un dipendente, un'attività e una posizione. Quei legami sono le relazioni nei tuoi dati. Ogni relazione è una dimensione e un'opportunità per dividere le cose che potrebbero essere duplicate in una tabella separata:
CREATE TABLE employees (id NUMERIC, name VARCHAR(100))
CREATE TABLE activities (id NUMERIC, label VARCHAR(100))
CREATE TABLE locations (id NUMERIC, place VARCHAR(100))
Ogni tabella viene popolata con i possibili valori per tali attributi, come questi per activities :
1 'Design'
2 'Program'
3 'Test'
3 'Troubleshoot'
La tua tabella di ore lavorate può quindi avere tutte le dimensioni di cui hai bisogno facendo riferimento agli altri utilizzando chiavi esterne :
CREATE TABLE hours_worked (
id NUMERIC,
employee NUMERIC, -- Foreign key into employees(id)
activity NUMERIC, -- Foreign key into activities(id)
location NUMERIC, -- Foreign key into locations(id)
time_spent NUMERIC
)
Dopo averlo fatto, puoi utilizzare JOIN per eseguire un'ampia gamma di query, alcuni dei quali avrebbero richiesto la scrittura del codice per interrogare e assemblare se tutto fosse stato denormalizzato:
-- Your table, which uses all three dimensions
SELECT employee.name, activity.label, locations.place, time_spent
FROM
hours_worked
JOIN employees ON hours_worked.employee = employees.id
JOIN activity ON hours_worked.activity = activities.id
JOIN location on hours_worked.location = locations.id
ORDER BY xxx -- Whatever's appropriate here
-- Number of hours worked at home, which uses only one dimension
SELECT SUM(time_spent)
FROM
hours_worked JOIN location ON hours_worked.location = location.id
WHERE
location.place = 'Home'
La normalizzazione conferisce anche una notevole flessibilità.
Se i tuoi capi decidono che vogliono anche tenere traccia delle ore per progetto, la tua relazione tridimensionale diventa quadridimensionale e se pensavi di inserire dati tridimensionali in un modello 2-D sarebbe stato peloso, 4-D è peggio. Fortunatamente, un database normalizzato richiede solo l'aggiunta di un'altra tabella ...
CREATE TABLE projects (id NUMERIC, name VARCHAR(100))
... e un'altra colonna su hours_worked:
project NUMERIC, -- Foreign key into projects(id)
POOF! Con una quantità minima di lavoro, hai appena aggiunto una dimensione aggiuntiva.
Le persone lavorano nelle caffetterie oltre all'ufficio ea casa? Inserisci una riga in locations e sei a posto.
Hai deciso che la "programmazione" avrebbe dovuto chiamarsi "sviluppo"? Cambia quella singola riga in activities e tutte le query che fai in seguito avranno l'etichetta giusta allegata.
