I'm fresh out of designing a set of tables, in which I came up with an architecture that I was very pleased with! I've never seen it anywhere else before, so I'd love to know if I've just reinvented the wheel (most probable), or if this is a genuine innovation.
Here's the problem statement: I have Employees who can each sign a different contract with the company. Each employee can perform different Activities, and each activity may have a different pay rate, sometimes a fixed amount for completing one activity, sometimes an hourly rate, and sometimes at a tiered rate. There may also be a specific customer who likes the employee particularly, so when he works with that specific customer, he gets a higher rate. And if no rate is defined, he gets the company default rate.
Don't fuss about the details: the main point is that there are a lot of pay rates that can be defined, each in a fairly complicated way. And the pay rates all have the following in common:
- Service Type
- Pay Scale Type (Enum: Fixed Amount/Hourly Rate/Tiered Rate)
- Fixed Amount (if PayScaleType = FA)
- Hourly Rate (if PayScaleType = HR) - yes, could be merged into one field, but for reasons I won't go into here, I've kept them separate
- Tiers (1->n relationship, with all the tiers and the amount to pay once you have gone over the tier threshold)
These pay rates apply to:
- Default company rate
- Employee rate
- Employee override rate (defined per customer)
If I had to follow the simple brute force approach, I would have to create a PayRate and PayRateTier clone table for each of the 3 above tables, plus their corresponding Linq classes, plus logic to calculate the rates in 3 separate places, somehow refactoring to reuse the calculation logic. Ugh. That's like using copy and paste, just on the database.
So instead, what did I do? I created a intermediary table, which I called PayRatePackage, consisting only of an ID field. I have only one PayRate table with a mandatory FK to PayRatePackage, and a PayRateTier table with a mandatory FK to PayRate. Then, DefaultCompanyPayRate has a mandatory FK to PayRatePackage, as do EmployeeRate and EmployeeOverrideRate.
So simple - and it works!
(Pardon me for not attaching diagrams; that would be a lot of effort to go to for a SO question where I've already solved the main problem. If a lot of people want to see a diagram, please say so in the comments, and I'll throw something together.)
Now, I'm pretty sure that something this simple and effective must be in a formal design pattern somewhere, and I'd love to know what it is. Or did I just invent something new? :)