I'm building application for employee official travel with lump-sum payment based on the employee group or echelon they have and I need a little help how to store the data correctly, as the problem im facing is an employee will always have a group (based on their year of service or education) but not necessarily have echelon. Because employee that can have echelon assigned with minimum requirement group rank they have.
Minimum example is:
Employee that has group IIIa can have the lowest echelon Va assigned to them.
For reference,
Employee Group: (higher to lowest)
| Group | Suffix(lowest-highest) |
|---|---|
| IV | a/b/c/d |
| III | a/b/c/d |
| II | a/b/c/d |
| I | a/b/c/d |
Employee Echelons: (highest to lowest)
| Echelon | Suffix(highest-lowest) |
|---|---|
| I | a/b |
| II | a/b |
| III | a/b |
| IV | a/b |
| V | a |
Example of echelon employee:
Jane Doe, Group IIIa, Echelon Va
Example of non-echelon employee:
Jane Doe, Group IIb
The Problem:
As you can see above there's echelon and non-echelon employees, i need to make the lump-sum payment for travel based on the employee group and employee group with echelon. Because business rule is, employee with no echelon have different lump-sum payment cost with employee have echelon. I have confusion how to reference both attributes to lump-sum payment table that store the travel cost.
For Example:
Employee With Group III, Echelon Va
Lump-sum payment for travel to foobar country: $200
Employee With Group III, Non-echelon
Lump-sum payment for travel to foobar country: $150
The Approach:
I made junction table for both entity, as the rule employee with Group IIIa(min) - IIIb(max) can be assigned to Echelon Va and Group IIIb(min) - IIIc(max) can be assigned to echelon IVa just for the example. but with this design if the employee have Group II and below that cannot be assigned to any echelon the group_echelon.echelon FK will be NULL is this even valid ?, because i need to still referencing the group_echelon.id for the lump-sum payment even then employee doesn't have echelon and or i should i created Non-Echelon value into echelon table? or can give me some enlightment to proceed correctly.
The last is to get the lump-sum payment based on either they have group or group and echelon, should i take the group_echelon.id to referencing the lump-sum payment for each group/echelon?
Thankyou.
