We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this:
There are only a couple of us on the project, and our boss dropped this design on us. I'm a novice, but I was concerned about the
Slot table because it seems like we're heading toward an EAV model and I don't think our team is experienced enough to navigate the pitfalls.
Slot can be a company, location, region, route or position (based on its
SlotType). We have tables for these entities, but management wants the "flexibility" to assign slots to each other (
SlotHierarchy). I'm not going to get into the mess of effective dates that apply to every slot, slot type, slot level, slot level type, and employee's position assignment. All the effective dates are because the management wants control over every piece.
The plan is to have an
Employee get assigned to a position slot (
SlotPositionAssignment) that can then be assigned to a location, route or region slot. A position can only have one employee assigned at a given time. A position can be assigned to any number of slots, though.
So, we may have two positions (slots) of
PositionType "Salesman" and
SlotDesc of "Sales 1" and "Sales 2", each with an employee assigned to it. These two positions may be assigned to multiple route, region, location or company slots. The
SlotHierarchy helps sort this out.
I'm afraid we may shoot ourselves in the foot with this. I don't mind writing additional application logic, but this seems "too clever". Would it not make more sense to call a spade a spade and use tables that map to real-life instead of obscuring them as "slots"?
I wanted to do away with having positions as slots and use a
Position table, or perhaps use just
PositionType though we'd lose the ability to report on a specific position in the company like "Rt 1 Manager". When I brought this up, I was told that it wouldn't be "flexible" and that was the end of it.
Is there a better way to organize this, or make some compromise, or am I worried about nothing? I am the novice and don't mind being wrong, as long as I know why I'm wrong.