Shared account ownership
Recognize when account ownership needs a bridge because ownership has splits and history.
Concept
many-to-many-modeling
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
Revenue operations needs ARR by account owner. One account can have several owners at the same time, each with a credit split and effective ownership dates.
A single owner foreign key hides co-ownership and makes ARR double counting likely when credit is split across owners.
- Pick the structure that stores one row per account, owner, and effective ownership period.
- Keep the allocation weight on the relationship row.
- The answer chooses an account-owner bridge.
- The explanation names account plus owner plus effective period as the bridge grain.
- Ask whether the relationship has attributes of its own.
- Credit split and effective dates belong to ownership, not only to account or owner.
- Putting only current_owner_key on the account and losing co-ownership.
- Duplicating account rows for every owner and inflating account counts.
Kimball bridge for account ownership
Model account ownership as a bridge with account_key, owner_key, effective dates, and allocation weight. A Kimball multivalued-dimension bridge.
Optimizes for
- • Shared credit reporting
- • Historical ownership analysis
- • Avoiding ARR double counting
Trade-offs
- • Queries need date-window filters against ownership periods
- • Allocation rules must be maintained as ownership changes
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.