Gestion des relations complexes de clés étrangères dans la conception de la base de données

Scénario

Cet exemple explore un défi de conception de base de données commun: créer une relation de clé étrangère où une seule table doit référencer les clés primaires à partir de plusieurs autres tables. Le scénario spécifique implique le tableau déduction qui doit être lié à la fois vers employés_ce et employés_sn tables.

le problème et sa solution

La question est de savoir si une clé étrangère dans déductions peut directement référencer les clés primaires dans les deux employés_ce et employés_sn simultanément. La réponse est: pas directement, dans une base de données relationnelle standard. Une seule clé étrangère ne peut faire référence qu'une seule clé primaire.

La solution proposée exploite un modèle d'héritage bien structuré pour résoudre ce problème.

Approche basée sur l'héritage

Les tableaux employés , employés_ce , et employés_sn représentent une hiérarchie d'héritage. employés est la table de base, avec employés_ce et employés_sn en tant que tableaux spécialisés qui héritent de celui-ci.

Structure de la base de données:

employees (id INT PRIMARY KEY, name VARCHAR(255))
employees_ce (id INT PRIMARY KEY, ce_specific_attribute VARCHAR(255), employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees(id))
employees_sn (id INT PRIMARY KEY, sn_specific_attribute VARCHAR(255), employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees(id))

Solution:

Au lieu d'un lien direct vers employés_ce et employés_sn , le tableau déduction devrait faire référence à la table des parents communs, employés :

deductions (id INT PRIMARY KEY, employee_id INT, deduction_amount DECIMAL(10,2), ..., FOREIGN KEY (employee_id) REFERENCES employees(id))

Cette approche assure l'intégrité référentielle et évite la redondance. Le employee_id dans déduction lie vers l'enregistrement des employés dans employés , que cet employé ait des informations supplémentaires dans employés_ce ou employés_sn . Cette conception gère efficacement la relation et maintient la cohérence des données.