Four Hands, One Invoice
One fee, several consultants, hours as the fairness rule. Credit everyone correctly without ever billing the client twice.
Concept
many-to-many-modeling
The primary modeling idea this problem reinforces.
Requirements
3
Business needs the model must satisfy.
A consultancy bills a fixed fee per engagement, and several consultants usually staff the same engagement, logging hours as they go. The feed sends two tables: engagements with their client and fee, and timesheets saying who worked how many hours on what. Partners want two numbers they can trust: how much revenue each consultant gets credit for, sharing each fee in proportion to hours logged, and how much revenue came from each client, where every fee counts exactly once no matter how many people worked it. Design the model, load the feed, and expose both views.
Whenever two entities share each other in both directions, the join between them multiplies rows, and money that rides that join gets counted twice without a single error appearing. This is among the most common silent corruptions in real warehouses and a favorite interview probe.
- Design any model you like, load the feed into it, and answer through views. The shape underneath is your call.
- Report credited revenue per consultant, each fee shared in proportion to hours logged on that engagement.
- Report revenue per client, with every fee counted exactly once.
- Fees are fixed per engagement and never depend on hours; hours only decide how credit is shared.
- Staffing ranges from one to a handful of consultants per engagement, usually with uneven hours.
- A consultant works many engagements; an engagement is worked by many consultants.
- How much revenue is credited to each consultant, sharing each fee by hours logged?
- How much revenue came from each client, counting every fee exactly once?
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.