5

We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this:

enter image description here

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.

A 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.

My concern:
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.

marc_s
  • 8,613
  • 6
  • 43
  • 51
Zairja
  • 215
  • 1
  • 2
  • 6
  • 2
    I definitely share your concern .... this already smells bad now.... wait until you have millions of rows ..... tell your super-clever boss to read [this exposé on how EAV can go horribly wrong and sink companies](https://www.simple-talk.com/content/article.aspx?article=292) - hopefully we'll come around and change his mind! – marc_s Feb 26 '14 at 10:00

1 Answers1

2

I'd suggest you have a think about this system's use cases. Work out in your own mind how the queries will look for the given design and for your alternative. Include scenarios where people move between slots. What do updates look like? Can business constraints be enforced in DRI? Can the desired values still be found? Having actual examples to talk through will highlight your concerns and allow your boss to explain the model more fully.

Writing horror queries against a ghastly schema is only one part of the problem. Someone has to fix them, at 3am, with no documentation, when they break. More intelligible queries at this stage will give you a better system, sooner.

If it calms your nerves any, I've had success with EAV systems. That said, we were a team of very experienced DBAs, it really was a blue-sky, "what if" project and, no, we didn't bet the company on it. Any dev project that will be heading to production we discourage most strongly from using an EAV approach.

If you go the 'flexible' route do not try to write generic queries just because you have a generic schema. Each query should only deal with one of Company, Location, Region or whatever your slot types are.

My two cents' worth.

Michael Green
  • 22,481
  • 12
  • 46
  • 87