Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > How to store and retrieve processed salary information

Reply
Thread Tools

How to store and retrieve processed salary information

 
 
saurabh
Guest
Posts: n/a
 
      10-24-2005
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

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-24-2005
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.


 
Reply With Quote
 
 
 
 
Bullschmidt
Guest
Posts: n/a
 
      10-25-2005
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 ***
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
New CCNA Salary Information and Survey CCIEAdvisor Cisco 0 08-10-2011 03:59 PM
How to store and retrieve array of bytes MYSQL Gert ASP .Net 0 09-13-2005 03:32 PM
XSLT: how to retrieve the path of the processed xml file Marc XML 1 08-19-2004 08:44 AM
how to retrieve and store an image in MS SQL and asp.net? angus ASP .Net 2 05-20-2004 02:40 PM



Advertisments