How to store and retrieve processed salary information

Discussion in 'ASP General' started by saurabh, Oct 24, 2005.

  1. saurabh

    saurabh Guest

    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
     
    saurabh, Oct 24, 2005
    #1
    1. Advertising

  2. 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.
     
    Bob Barrows [MVP], Oct 24, 2005
    #2
    1. Advertising

  3. saurabh

    Bullschmidt Guest

    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 ***
     
    Bullschmidt, Oct 25, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. The job lady
    Replies:
    0
    Views:
    561
    The job lady
    Jan 13, 2006
  2. angus
    Replies:
    2
    Views:
    475
    Elliot M. Rodriguez, MCSD
    May 20, 2004
  3. Gert
    Replies:
    0
    Views:
    965
  4. Marc
    Replies:
    1
    Views:
    504
    Johannes Koch
    Aug 19, 2004
  5. Angila
    Replies:
    0
    Views:
    338
    Angila
    May 12, 2011
Loading...

Share This Page