Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   How to store and retrieve processed salary information (http://www.velocityreviews.com/forums/t799870-how-to-store-and-retrieve-processed-salary-information.html)

saurabh 10-24-2005 11:25 AM

How to store and retrieve processed salary information
 
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] 10-24-2005 12:48 PM

Re: How to store and retrieve processed salary information
 
saurabh wrote:
> 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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



Bullschmidt 10-25-2005 09:12 PM

Re: How to store and retrieve processed salary information
 
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
>>


*** Sent via Developersdex http://www.developersdex.com ***


All times are GMT. The time now is 09:37 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.