买家与司机交互动作记录表的技术解析与问询

Alright, let’s unpack this action table thoroughly—going over field definitions, data rules, and the business logic behind each interaction scenario as requested.

Field Definitions #

Each column serves a specific purpose in tracking cross-user interactions:

• id: Unique primary key for every action record (example values like 100, 101 are auto-incrementing identifiers)
• job_id: Links actions to a specific order/task—all interactions related to the same job share this ID (e.g., records 100-102 belong to job 10, while 103-104 belong to job 17)
• sender_buyer: ID of the buyer when they’re the sender; set to 0 if the sender isn’t a buyer
• rcpt_driver: ID of the driver when they’re the recipient; set to 0 if the recipient isn’t a driver
• sender_driver: ID of the driver when they’re the sender; set to 0 if the sender isn’t a driver
• rcpt_buyer: ID of the buyer when they’re the recipient; set to 0 if the recipient isn’t a buyer
• action: Core identifier for the type of interaction (e.g., message, post_job)
• text: Text content tied to the action—used for messages, left empty for actions that don’t require text

Core Data Rules #

Based on the sample data and business context, these rules govern the table’s integrity:

• Interactions only happen between buyers and drivers—no same-type user interactions (buyer→buyer or driver→driver is impossible)
• Sender fields are mutually exclusive: Either sender_buyer is non-zero (buyer sends) or sender_driver is non-zero (driver sends); the other sender field must be 0
• Recipient fields are mutually exclusive: Either rcpt_driver is non-zero (sent to driver) or rcpt_buyer is non-zero (sent to buyer); the other recipient field must be 0
• All actions with the same job_id are part of the same order/task’s workflow
• text is required only for message actions; other action types (like post_job) can leave this field empty

Action Type-Specific Rules & Business Scenarios #

Let’s break down the two action types from the sample data:

message (Direct Messaging) #

• Value Rules:
  • Driver → Buyer: sender_driver = driver’s ID, rcpt_buyer = buyer’s ID; sender_buyer = 0, rcpt_driver = 0 (examples: records 100, 101, 103)
  • Buyer → Driver: sender_buyer = buyer’s ID, rcpt_driver = driver’s ID; sender_driver = 0, rcpt_buyer = 0 (example: record 104)
  • text must contain the actual message content (never empty for this action type)
• Business Context: This is for real-time communication between buyers and drivers during a job—like confirming pickup locations, delivery times, or special instructions.

post_job (Job Assignment) #

• Value Rules:
  • Only buyers can post jobs, so sender_buyer = buyer’s ID, rcpt_driver = target driver’s ID; sender_driver = 0, rcpt_buyer = 0 (example: record 102)
  • text is intentionally left empty (no text content needed for this action)
• Business Context: This action represents a buyer sending a job request directly to a specific driver. The driver can then respond with messages or accept/reject the job (though accept/reject actions aren’t shown in the sample).

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