DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DB Design/Layout Question

  1. #1
    Join Date
    Jun 2000
    Posts
    417
    I have a question about a database layout.

    As a setting, let's say there are 5 types of employees, and say, 25 types of bonuses. (bonus1, bonus2, etc). Some bonuses are specific to an employee type (eg, emp1 cannot ever get bonus15-25, but could have the rest). I'm wondering what the ideal structure would be

    I could have an employee table, with columns for the 25 bonuses (value is the ammount of bonuses), eg

    <font face="courier">
    Employee Type bonus1 bonus2 bonus3 ....
    ---------------------------------------------------------------
    Joe emp1 15 23
    Bob emp2 12
    </font>

    But since the bonuses are limited by types that could be a large number of unused columns, plus application logic checking for each bonus has to manually check for each bonus which is a pain, eg

    <font face="courier">
    if (rec.bonus1 is not null) then
    do something;
    end if;
    if (rec.bonus2 is not null) then
    do something;
    end if;
    ...
    </font>

    I could create another table called bonuses that would store information like this

    <font face="courier">
    Employee bonus ammount
    ------------------------------------
    Joe bonus1 15
    Joe bonus2 23
    Bob bonus3 12
    </font>

    The app would have a little easier time with this, but if this were extended to hundreds or thousands of employees, would query performance suffer from trying to find all the employee's bonuses?

    Also, each bonus would have it's own attributes, eg (percentage increase, name, description, whatever), so there would have to be a table containing the information on all the bonuses.

    If there were always a constant number of bonuses, I suppose the first method (all bonus info in emp record) would be a faster query and only damper a little on the application design, but it's very inflexible if new bonuses are required, which would be supported by the second method. However with the second method, assuming there are 1000 employees each with 10 bonuses, just to find one employees bonuses I have to dig through 10000 rows, which might be fast enough but could easily get larger.

    I suppose since the first method is so inflexible I have no choice but to throw it out (as I would like the option of adding/removing bonuses), but is there a way that might provide better performance than the second I suggested?

    Thanks

    [Edited by pwoneill on 10-12-2000 at 04:52 PM]

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You yourself well analyzed your design.

    Go for the 2nd option. (Normalize tables). Today Oracle can handle billions of rows in a table, performance will not be a problem.

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    I would suggest 3 tables:
    employees, bonuses and employee_bonuses:

    table employees:
    emplooyee_id primary key,
    name,
    etc.

    table bonuses:
    bonus_id primary key,
    amount,
    etc.

    table employee_bonuses:
    employee_id foreign key referencing employees,
    bonus_id foreign key referencing bonuses

    Performance is not really a problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width