How to store and retrieve processed salary information

[saurabh]

Hi All,
I want to store the processed salary information in the data
base. My requirement is as follows:
1.For each location for an employee , I have to pay/deduct different
components (at least 38 )which differ from person to person.
2.Each component is mapped to component table which contains the
component Id.

I may have 2 options
Option 1: Store data in traditional RDBMS system with different rows
for different components like
Emp No, Location ID, Component Id, Amount.
44, LONDON, 1, 5000
44, LONDON 2, 6000
Drawback: will result in no of obselete, non usable columns. Will
result in multiple selects.

Option 2:Storing data in one column for each employee in a location
Emp No,Location Id,Component ID1,Amount 1,componentID2,Amount 2
44, LONDON, 1 5000 2 6000


Advantage: this SAP like table structure can store dynamic data. Will
result in 1 select per employee and location
Problem: In 2 option there is difficulty in inserting and selecting
data.
Please suggest me unique and performance based best way of inserting
and selecting data in Option 2

Thanks and Regards,
Saurabh

 

[Bob Barrows [MVP]]

Hi All,
I want to store the processed salary information in the data
base. My requirement is as follows:
1.For each location for an employee , I have to pay/deduct different
components (at least 38 )which differ from person to person.
2.Each component is mapped to component table which contains the
component Id.

I may have 2 options
Option 1: Store data in traditional RDBMS system with different rows
for different components like
Emp No, Location ID, Component Id, Amount.
44, LONDON, 1, 5000
44, LONDON 2, 6000
Drawback: will result in no of obselete, non usable columns.

What obsolete columns? I do not understand your point.

Will result in multiple selects.

Not necessarily. There are techniques to pivot your data. In some rdbms's,
such as Jet, these are built into the sql language used by the rdbms. In
others, such as SQL Server, you have to write the pivot algorithm yourself
(although I've heard rumors that this will change in SQL 2005).

Option 2:Storing data in one column for each employee in a location
Emp No,Location Id,Component ID1,Amount 1,componentID2,Amount 2
44, LONDON, 1 5000 2 6000


Advantage: this SAP like table structure can store dynamic data.

How so? Any time you add a component type, you will need to add a new column
to your table and revise all the code that accesses that table.

Will
result in 1 select per employee and location
Problem: In 2 option there is difficulty in inserting and selecting
data.
Please suggest me unique and performance based best way of inserting
and selecting data in Option 2

For the reasons you mention, option 2 should not be considered.

Bob Barrows

 

[Bullschmidt]

I THINK you mean row instead of column. And to me this Option 2 (many
field settings in an Employee table or something) seems like a natural
way to go.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

<<
Option 2:Storing data in one column for each employee in a location
Emp No,Location Id,Component ID1,Amount 1,componentID2,Amount 2
44, LONDON, 1 5000 2 6000