Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
The case for surrogate keys
Message
 
 
To
14/01/1999 16:09:39
Victor Chigne
Inteliventas
Peru
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00176201
Message ID:
00176425
Views:
31
From a normalization standpoint, I think you are there. The next step is to design views that join the tables so that you can better support the UI. For example, you would likely have a view that joins employees, payroll types, and the company table.

As far as surrogate keys, I am in favor of them. I use the VFE framework - and that has a appids table that holds the next key. The user should have no control over what the PK of a table. If you do have the need to create keys that are meaningful and derrived from other fields, make those candidate 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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform