Come costruire un Database
In DB Browser creerai prima un database (vedi materiale didattico). Nella scheda Struttura del database, seleziona “Crea tabella”. Per prima cosa, dai un nome alla tabella “Cohort”. Più in basso in Campi, clicca su “Aggiungi campo”. Seguiremo una convenzione di denominazione in cui il nome del campo conterrà il nome della tabella (tutti i campi di questa tabella inizieranno con cohort_). Lo facciamo per evitare la duplicazione dei nomi dei campi. Il primo campo sarà sempre l’identificatore unico del record. Qui lo chiamiamo cohort_id. Vedrete nella tabella di confronto qui sopra che SQLite può gestire un numero limitato di tipi di dati. In questo caso la nostra scelta è il testo, selezionandolo dal menu a tendina. Sarà una “chiave primaria” e “Not NULL”, il che significa che sarà unica e sempre presente in ogni record aggiunto. Dopo questo aggiungete i campi cohort_cross_date, cohort_parent_1 e cohort_parent_2, tutti e tre come testo. Poi, aggiungeremo un campo chiamato “cohort_location” con validazione per il testo. Questo significa che il campo accetterà un numero limitato di opzioni. Idealmente, tutti i vostri campi di testo saranno progettati in questo modo. Il modo per farlo in DB Brower è quello di programmare un controllo per vedere se i dati inseriti sono una delle opzioni che avete dato in precedenza. Scorri a destra e individua la colonna “Controllo”. Inserisci cohort_location==”tank_1″ OR cohort_location==”tank_2″. Essenzialmente stai mettendo insieme i valori consentiti per cohort_location con l’OR booleano. Nota anche che devi usare il doppio segno di uguale. La sintassi per questo è field_name ==”option” OR field_name==”some_other_option”, e puoi continuare ad aggiungere OR.
La tabella individuale è creata in modo simile, ma qui introdurremo una chiave esterna alla tabella coorte. Questo significa che la collegheremo alla tabella coorte, in modo che solo gli id delle coorti che sono state create possano essere inseriti in questa tabella. Questo mantiene i dati puliti e migliora la velocità di qualsiasi join di tabelle che si può scegliere di fare in futuro. Per prima cosa, create il campo “individual_id” come tipo di dati TEXT e come chiave primaria, Not NULL e unica. Poi, quando crei “ind_cohort_id” come tipo di dati TEXT, scorri la colonna Foreign Key. Quando clicchi sul campo appropriato, appare un menu a tendina. Nel primo menu a discesa scegliete la tabella (in questo caso, coorte) e nel successivo il campo (“cohort_id”). Nella casella di testo scrivi ON DELETE SET NULL ON UPDATE CASCADE. Puoi anche scegliere “NO ACTION” se vuoi mantenere il riferimento alla coorte nella tabella individuale anche se il record della coorte stessa viene cancellato. Guarda la finestra dello script SQL qui sotto. Dovrebbe mostrare FOREIGN KEY(`ind_cohort_id`) REFERENCES `cohort`(`cohort_id`). Se questa linea non appare, togliete e ricliccate i campi PK, U e NN. Devi anche impostare la chiave esterna quando crei la tabella. Questa è una stranezza di DB Browser (o un bug). Infine, finisci con “location” come tipo di dati TEXT.
Infine, imposta la tabella “fisica”. Noterete nuovi tipi di dati. Prima di tutto, il physical_id è un intero (INT) ed è anche auto-incrementale (AI). Questo assicura che ogni misurazione effettuata sarà trattata come unica. Ogni volta che si lavora con dati che arrivano come numeri interi, come i conteggi, assicurarsi di convalidare i dati per assicurarsi che siano interi. Il campo “physical_cohort_or_ind” accerta se l’organismo misurato viene tracciato come individuo o solo come parte della coorte (perché è troppo piccolo per essere etichettato e distinto dagli altri membri della coorte). Questo tipo di dati è TEXT, con Check (physical_cohort_or_ind==’cohort’ OR physical_cohort_or_ind==’ind’). Infine, le misure di peso e lunghezza sono di tipo NUMERIC. Si noti che l’unità di misura è codificata nel nome del campo. Si può anche avere un campo separato per le unità se si prevede di lavorare con diversi strumenti e su diverse scale. La parte importante è mantenere le unità separate dalla misura numerica, in modo che i vostri dati siano ordinati e pronti per le trasformazioni e le analisi.