One current owner
Recognize when a simple direct relationship is better than a bridge.
Concept
relationship-cardinality
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
A small CRM exports exactly one current primary owner per account. The business only needs current pipeline by owner and does not analyze historical ownership or split credit.
Bridge tables should solve real many-to-many grain. Adding one for a stable one-owner relationship makes simple reporting harder.
- Pick the simplest model that supports one current owner per account.
- Avoid bridge complexity when there is no split, history, or relationship attribute.
- The answer chooses a direct owner foreign key or relationship.
- The explanation states why a bridge would be unnecessary for current-only N:1 ownership.
- Ask whether more than one owner can be true for the same account at the same time.
- If the relationship has no attributes and no history, direct modeling can be the cleaner answer.
- Adding a bridge just because two business nouns are related.
- Inventing history requirements the prompt does not ask for.
Kimball direct relationship for current ownership
A direct owner key is sufficient when one account has exactly one current owner and the business does not need ownership history. Plain Kimball fact-and-dimension modeling.
Optimizes for
- • Simple current-state reporting
- • Lower query complexity
- • Clear N:1 account-owner relationship
Trade-offs
- • Cannot answer historical ownership questions later
- • Must be revisited if split credit or co-ownership appears
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.