Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
The case for surrogate keys
I feel that my table design is going worng. Could you please lend me a hand?
The scenario
A Payroll System, that holds concepts per company and payroll type :
Employees
Emp ID ( PK)
Payroll type (PK-FK)
Company ID (PK-FK)
Emp Name
Table payroll types
payroll type (PK)
Name
Table Companys
Company ID (PK)
Company name
Table Payroll concepts
Concept ID (PK)
Company ID (PK-FK)
payroll type (PK-FK)
Concept Description
Table Concept values
Emp ID (PK)
Concept ID (PK-FK)
Payroll type (PK-FK)
Company ID (PK-FK)
Concept Value
Questions :
Are the tables well normalized (specially table employees an table concept
values)?
Should I use a surrogate PK for table concept values? When I set an
identfiying relation between Concept values and employees, my tool insist in
translating all the PK's from Employees to Concept values.
A fellow DBA tells me that, if I set a surrogate PK for concept values, I'll
have to write a lot of triggers to ensure that the concept values are the
correct ones for payroll type and company. If I set the tables as above, the
referential integrity will be managed automatically by the RDBMS. But i feel
that there is a lot of duplicity in the desigN. Is there a better way to
model the data? The DBA says that the above is OK.
Thanks in advance
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement