Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
The case for surrogate keys
Message
From
14/01/1999 16:09:39
Victor Chigne
Inteliventas
Peru
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
The case for surrogate keys
Miscellaneous
Thread ID:
00176201
Message ID:
00176201
Views:
78
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
Map
View

Click here to load this message in the networking platform