Progettazione della Primary Key di una tabella
La discussione su come progettare una Primary Key (PK) di una tabella è sempre un tema caldo tra sviluppatori e DBA, di seguito illustrerò le mie personali considerazioni fondate sulle mie personali esperienze riferite a scenari di database su SQL Server.
In primo luogo la prima domanda che ci si pone è cosa scegliere con PK tra le seguenti opzioni:
- Creare una colonna che rappresenterà un ID univoco, come ad esempio un uniqueidentifier(GUID) o un numero auto incrementante. Ovvero usare una Surrogate key.
- Utilizzare una colonna non nulla che identifica univocamente i record nella tabella. Ovvero una Natural key.
- Utilizzare una combinazione di colonne non nulle che identifica univocamente i record nella tabella. Ovvero una Compound key.
Ovviamente come spesso accade non esiste una scelta ottimale per tutte le situazioni, ma conviene optare per una delle tre opzioni in base alle entità che contiene la tabella e in base alle relazioni che ha con altre tabelle.
Considerazioni nella scelta tra Surrogate Key e Natural key
La scelta tra la Surrogate Key (SK) e Natural key (NK) deve avvenire alla luce del fatto che non conviene scegliere una NK se il valore in questa colonna pur rimanendo univoco può cambiare, in questo caso conviene optare per una SK per evitare problemi con tabelle che sono in relazione con la PK della tabella in esame.
Ovviamente per evitare inconsistenze sarà comunque consigliabile definire un indice univoco sulla colonna che rappresentava la NK e identifica univocamente i record nella tabella.
Considerazioni nella scelta tra Surrogate Key e Compound key
La scelta tra la Surrogate Key (SK) e Compound key (CK) deve avvenire alla luce del fatto che conviene scegliere una CK se le colonne candidate a formare la PK sono a loro volta in relazione con altre tabelle. Infatti in questo modo scegliendo come PK una CK si avranno i seguenti vantaggi:
- Non sarà possibile che nella tabella vengano ad essere inserite combinazioni non univoche di queste colonne. Scegliendo una SK sarebbe poi stato necessario impostare un indice univoco sulle colonne che devono identificare univocamente i record in tabella.
- Le colonne nella CK diventando la PK saranno inserite automaticamente nell’indice cluster che è più performante degli indici non cluster (su una tabella è possibile definire un solo indice cluster). Questo andrà a tutto vantaggio delle select che utilizzeranno queste colonne come filtro e delle join con le tabelle con cui tali colonne sono in relazione.
Analogamente come nel caso SK vs NK non converrà scegliere una CK se una qualunque delle colonne può variare pur rimanendo la combinazione delle colonne univoca.
Conclusioni
- Conviene scegliere una SK che non esiste una colonna immutabile che identifica univocamente i record nella tabella.
- Se si è optato per una SK, ma esiste una colonna che identifica univocamente i record nella tabella definire su tale colonna un indice univoco. Nel caso in cui tale colonna univoca viene utilizzata spesso nelle select come filtro valutare di impostare l’indice univoco come cluster e impostare la PK come non cluster.
- Se la tabella ha alcune sue colonne che sono la Foreign key (FK) di una relazione uno a molti con altre tabelle e l’insieme di tali colonne identifica univocamente i record nella tabella, tali colonne possono essere utilizzate come una CK definendo su queste la PK. Infatti in questo caso le colonne della CK sono in relazione con colonne che sono la PK di altre tabelle e se vale la considerazione che per tali PK è stata rispettata la regola di scegliere una colonna immutabile che identifica univocamente i record nella tabella, anche le colonne della CK dovrebbero essere immutabili e quindi la loro combinazione crea un identificativo immutabile che identifica univocamente i record nella tabella.
- Se invece le colonne della CK formano una identificativo che identifica univocamente i record nella tabella, ma che può non essere immutabile valutare l’adozione di una SK e definire un indice univoco sulle colonne della CK, inoltre dal momento che tali colonne verranno probabilmente usare molto spesso per seguire le join definire come clustered tale indice univoco.
- Nel dubbio conviene scegliere una SK e inserire gli indici univoci necessari per garantire la consistenza dei dati.
Per ulteriori considerazioni si vedano: