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
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?