General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
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