Surrogate key in database design

L’usage de terme "surrogate" en informatique est parfois controversé.

Lorsque l’on conçoit une base de donnée, une des premières règles (imposées par les formes normales de conception) est de définir une clé primaire. Il s’agit d’un identifiant unique représentant un enregistrement donné et assurant qu’il ne peut être confondu avec une autre.
Les problèmes surviennent lors du choix de cette clé. Si je crée une table qui stocke les employés d’une entreprise, prendre le nom (ou le nom et le prénom) des personnes ne m’assurera pas forcémment l’unicité. Deux employés peuvent avoir les même noms et prénoms. D’où problème pour les reconnaître.
D’une manière général on introduit une colonne supplémentaire qui prend des valeurs entières uniques et qui identifie de manière distincte chaque enregistrement
ID Nom Prénom Age
1 Picard Jacques 30
2 Picard Jacques 24

En général les informaticiens appellent cette clé la clé primaire qui est par définition unique pour chaque enregistrement. (En pratique ça peut vite devenir différent). D'autres plus puristes diront qu'il s'agit d'une surrogate primary key (mais c'est plus rare).
En effet, une manière idiote de générer cette clé est qu’a chaque fois que j’enregistre un nouvel employé, je prends le maximum de la colonne ID et j’ajoute 1. “INSERT INTO tblEmployee (ID, Nom, PRENOM) VALUES (SELECT MAX(ID)+1, $Nom, $Prenom)”

Cela marchera bien si je suis l’unique utilisateur de la table employé, le problème est que le serveur de base de donnée sert plusieurs connections (utilisateurs connectés) en même temps. Que ce passe-t-il si deux personnes veulent faire une insertion dans la table employé au même moment.
U1 : veut insérer Alain Térieur qui a 36 ans. U1 Récupère le MAX(ID)+1=3 (ok)
U2 : veut insérer Jacques Cepte qui 28 ans. U2 Récupère le MAX(ID)+1 avant que U1 n’est eu le temps de faire l’insert=> Il obtient 3 aussi
U1 : fait son update et tout se passe bien
ID Nom Prénom Age
2 Picard Jacques 24
3 Térieur Alain 36
U2 : fait son update et il tente de mettre un second 3 dans le champ ID et là deux cas de figure, soit on a imposé une contrainte d’unicité sur le champ ID et heureusement la transaction explose, soit on ne l’a pas fait et on se retrouve avec clé primaire qui n’est plus unique (même si elle est censée l’être).
ID Nom Prénom Age
2 Picard Jacques 24
3 Térieur Alain 36
3 Cepte Jacques 28

Pour pallier à ce problème le vendeurs de bases de données ont crée des champs de type SERIAL ou AUTOINCREMENT, ce sont en général des entiers 32 bits. C'est-à-dire que ce n’est plus au programme de donner le numéro d’ID mais c’est le serveur de base de donnée qui s’en occupe. Ce type de clé primaire basée sur un tel champ est en général appelé par les informaticiens une surrogate key.
Avec une telle clé on se fiche de l’ID et pour insérer un utilisateur on indiquera simplement : INSERT INTO tblEmployee (Nom, Prenom) VALUES (‘Térieur’, ‘Alain’)
L’ID sera automatiquement incrémenté par la base de donnée dans ce cas de figure ma clé primaire sera toujours unique d’où le qualificatif de surrogate. Ceci est encore malheureusement de la théorie.
En pratique certains systèmes peuvent se connecter à une base de donnée y récupérer des données et puis s’en déconnecter. (Exemple : des moniteurs qui vont visiter un site clinique où il n’ont pas un accès directe à la base de donnée de la CRO, ils récupèrent les données sur leur PC, ajoute des nouvelles données lors de la visite, puis ils vont resyncrhoniser à leur retour avec la base de donnée d’origine). Même avec des clés primaires de type Autoincrement, l’utilisateur A aura de bonne chance d’avoir les mêmes valeurs de clé dans la base de donnée locale de son système que l’utilisateur B qui visitait le même site que lui au même moment.
Le système embarquer devra soit faire la réconciliation lui-même (ce qui est en pratique une très mauvaise idée)
Soit le système de base de donnée est sufisamment malin que pour réconcilier (ce n’est pas toujours gagné)
Soit on utilise un autre type de champ pour la clé primaire, pas mal de serveur de base de données aujourd’hui proposent des types GUID (Global Unique Identifier). Sa taille est de 16 octets (nettement plus lourd qu’un type integer) mais chaque valeurs générées est vraiment unique =>P(générer une valeur valeur existe)=0. Certains appelleront ce type de clé des surrogate keys alors qu'ils appelleront les autres type de champ clé des simple primary keys... Le principe consiste à définir clairement les conventions au sein d'une équipe.