General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only