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. Advertisements

  2. What obsolete columns? I do not understand your point.
    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).
    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.
    For the reasons you mention, option 2 should not be considered.

    Bob Barrows
     
    Bob Barrows [MVP], Oct 24, 2005
    #2
    1. Advertisements

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.