呼叫中心呼叫调度主表：大历史表主键选型咨询

Great question — let's break down the tradeoffs here because your scenario (high-volume historical dispatch logs for a call center) has specific needs that make both options viable, depending on your priorities.

Option 1: Composite Primary Key (DateOfRecords + Sequence) #

This is a solid choice if your workflows revolve heavily around daily operations and historical archiving:

• Pros:
  • Natural partitioning: If you use database partitioning (e.g., by DateOfRecords), queries for a specific day's dispatch data will be lightning fast since the database only scans the relevant partition. No extra index is needed for date-based queries.
  • Business-readable: The sequence resets daily, so you can instantly tell that record 2024-05-20, 147 is the 147th dispatch of that day — useful for daily reporting or debugging.
  • Reduces index bloat: The primary key index inherently covers the date, so you don't need a separate index for DateOfRecords if most of your queries filter by date.
• Cons:
  • Concurrency risk: Generating a unique daily sequence requires atomically getting the max sequence for the day and incrementing it. Without proper locking (e.g., SELECT ... FOR UPDATE or using database-specific functions), you could get duplicate sequences during peak insert loads.
  • Larger key size: A composite key (date + int) takes more storage than a single integer, which can slow down index lookups and increase storage for foreign keys if other tables reference this dispatch table.
  • Database limitations: Some databases don't natively support resetting sequences daily, so you'd need triggers or application-level logic to handle sequence resets, adding complexity.

Option 2: Single Integer Surrogate Primary Key (e.g., DispatchID INT AUTO_INCREMENT) #

This is the industry standard for a reason — it's simple, reliable, and performant:

• Pros:
  • Maximum performance: Integer keys are small, so primary key indexes are compact and fast to scan. Inserts are atomic and don't require any extra logic to generate the key.
  • Flexible foreign keys: If other tables (like call result logs) need to reference this dispatch table, a small integer foreign key is more efficient than a composite key.
  • Less complexity: No need to handle daily sequence resets or concurrency issues with sequence generation. The database handles key creation automatically.
• Cons:
  • No business meaning: The DispatchID doesn't tell you anything about the date or sequence, so you'll need an additional index on DateOfRecords for daily reporting/queries.
  • Partitioning still works: You can still partition the table by DateOfRecords even with a surrogate key — you just add the partition key as a separate column and index it.

Option 3: Best of Both Worlds (Surrogate Key + Unique Composite Index) #

If you want the reliability of a surrogate key but still need the daily sequence for business purposes, this is the sweet spot:

• Use an auto-incrementing DispatchID as the primary key.
• Add a unique constraint on (DateOfRecords, Sequence) to enforce that each daily sequence is unique.
• Generate the daily sequence using application logic or a database trigger (e.g., on insert, set Sequence to COALESCE(MAX(Sequence) OVER (PARTITION BY DateOfRecords), 0) + 1 if your database supports window functions).

This way:

• You get the performance and simplicity of a surrogate key.
• You retain the business-readable daily sequence for reporting.
• You avoid the concurrency risks of a composite primary key (since the unique constraint will catch duplicates, and you can use atomic window functions to generate sequences safely).

Final Recommendation #

For your high-volume historical call center dispatch table:

• If daily archiving and date-based queries are your top priority, go with the composite primary key (DateOfRecords + Sequence) and implement atomic sequence generation (e.g., using database-specific functions or optimistic locking). Pair it with date partitioning to keep query speeds fast as the table grows.
• If simplicity, insert performance, and long-term maintainability are more important, use a surrogate integer primary key with a unique composite index on (DateOfRecords, Sequence). Add an index on DateOfRecords to optimize daily reporting queries.

Most teams lean toward the surrogate key approach because it avoids the edge cases of composite keys, but the composite key works great if your workflows align with daily operations.

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