ERD中sales_rep_id归属问询：应在orders表还是accounts表？

Great question—let’s break this down into two core parts to unpack the design tradeoffs and behavioral implications you’re curious about.

Should sales_rep_id live in the orders table instead of accounts? #

This depends entirely on your business logic, but let’s align with common patterns:

• If a customer is permanently assigned to one sales rep (no changes, or all past/future orders should tie to their current rep), placing sales_rep_id in accounts makes sense—it avoids redundant data and keeps the customer-rep relationship centralized.
• If orders can be tied to different sales reps (e.g., a customer switches reps mid-relationship, or specific orders are handled by a different rep), putting sales_rep_id in orders is the standard approach you mentioned. This lets you track order-specific rep assignments, which is critical for accurate sales attribution, commission calculations, and historical reporting.

Most businesses prefer the second approach because it preserves granular, historical context—something that’s often lost when the rep link is only at the customer level.

What happens if you change the sales rep in the current ERD (with sales_rep_id in accounts)? #

If sales_rep_id lives in accounts, switching a customer’s rep will retroactively reassign all their past orders to the new rep when you query. Here’s why:
When you join orders to accounts to pull rep data, you’re pulling the current sales_rep_id from the accounts table—not the one that was active when the order was placed. This means old orders will no longer be associated with the rep who originally handled them, which can skew historical metrics like rep performance over time.

If you move sales_rep_id to orders, each order retains the rep ID that was active at the time of creation. Changing a customer’s rep later only affects new orders, leaving historical order-rep associations intact.

内容的提问来源于stack exchange，提问作者Osama Negm