月度可留存优惠券的SQL数据库架构设计咨询

Hey there! Let's work through designing a database for your corporate monthly cleaning service—we’ll make sure it covers all your key requirements: monthly billing, employee-based coupon issuance, and persistent coupon redemptions. Here’s a structured approach tailored to your scenario:

We’ll break down the system into modular tables that capture every critical part of your workflow, with clear relationships between them.

companies (Corporate Client Table) #

Stores basic, static information about your business clients:

• company_id (INT, PRIMARY KEY): Unique identifier for each enterprise (e.g., 1 for XYZ Company)
• company_name (VARCHAR(255)): Official name of the client
• contact_email (VARCHAR(255)): Primary point of contact for billing/updates
• created_at (DATETIME): Date the client started your service
• updated_at (DATETIME): Timestamp for when client details were last modified

monthly_employee_counts (Monthly Staff Tracking Table) #

Since employee numbers can change month-to-month, we need to track counts per client per service month (this drives coupon issuance):

• employee_count_id (INT, PRIMARY KEY): Unique record ID
• company_id (INT, FOREIGN KEY REFERENCES companies(company_id)): Links to the client enterprise
• report_month (DATE): Month of the count (e.g., 2018-04-01—we can ignore the day for grouping/queries)
• employee_number (INT): Number of employees the client reported for the month
• verified_at (DATETIME): Timestamp when you confirmed the employee count with the client
• created_at (DATETIME): When the count record was added

cleaning_service_invoices (Monthly Billing Invoices) #

Captures each month’s cleaning service charge, including coupon deductions:

• invoice_id (INT, PRIMARY KEY): Unique invoice number
• company_id (INT, FOREIGN KEY REFERENCES companies(company_id)): Links to the client
• service_month (DATE): Month the cleaning service was provided
• base_amount (DECIMAL(10,2)): Original, pre-deduction service cost (e.g., $1000 for XYZ’s April 2018 bill)
• coupon_deduction (DECIMAL(10,2)): Total amount deducted via coupons for this invoice
• final_amount (DECIMAL(10,2)): Amount the client actually pays (base_amount - coupon_deduction)
• payment_status (ENUM('unpaid', 'paid', 'overdue')): Current payment state of the invoice
• created_at (DATETIME): When the invoice was generated
• paid_at (DATETIME): Timestamp when the client completed payment

coupons (Issued Coupons Table) #

Tracks every individual $5 coupon issued, including its status and usage:

• coupon_id (INT, PRIMARY KEY): Unique coupon identifier
• company_id (INT, FOREIGN KEY REFERENCES companies(company_id)): The client that owns this coupon
• issuance_invoice_id (INT, FOREIGN KEY REFERENCES cleaning_service_invoices(invoice_id)): Links to the monthly invoice that triggered this coupon’s issuance
• issuance_month (DATE): Month the coupon was issued (matches the linked invoice’s service_month)
• face_value (DECIMAL(10,2)): Fixed at $5.00 (field retained for flexibility if you change denominations later)
• status (ENUM('active', 'used', 'expired')): Current state of the coupon (active = unused and valid)
• used_in_invoice_id (INT, FOREIGN KEY REFERENCES cleaning_service_invoices(invoice_id)): Links to the invoice where this coupon was redeemed (populated only when used)
• used_at (DATETIME): Timestamp when the coupon was applied to a bill
• created_at (DATETIME): When the coupon was generated

coupon_redemption_details (Optional Redemption Audit Table) #

For full transparency, add this table to track which coupons were used on which invoices (useful for reconciliation):

• redemption_id (INT, PRIMARY KEY): Unique audit record ID
• invoice_id (INT, FOREIGN KEY REFERENCES cleaning_service_invoices(invoice_id)): Links to the invoice being paid
• coupon_id (INT, FOREIGN KEY REFERENCES coupons(coupon_id)): Links to the coupon used
• deduction_amount (DECIMAL(10,2)): Amount deducted by this coupon (always $5.00 here)
• created_at (DATETIME): When the redemption was recorded

Let’s map your sample scenario to the tables to see how data flows:

1. Generate April 2018 Invoice: Create a record in cleaning_service_invoices with base_amount=1000.00, service_month=2018-04-01, and payment_status='unpaid'.
2. Record Employee Count: Add a monthly_employee_counts entry for XYZ with employee_number=5 and report_month=2018-04-01.
3. Issue Coupons: Generate 5 records in coupons, all linked to XYZ’s company_id, the April invoice, with status='active' and face_value=5.00.
4. Redeem Coupons Later: If XYZ uses 3 of these coupons for their May 2018 bill:
   • Update the May invoice’s coupon_deduction=15.00 and final_amount=985.00.
   • Mark 3 coupons as status='used', set their used_in_invoice_id to the May invoice, and populate used_at.
   • (Optional) Add 3 entries to coupon_redemption_details linking the May invoice to each used coupon.

• Data Consistency: Use database transactions when issuing coupons to ensure you never issue more/less than the employee count (e.g., if the count is 5, exactly 5 coupons are created in one atomic step).
• Coupon Expiry: If you later add an expiry rule (e.g., coupons expire after 12 months), add an expiry_date field to coupons and build a background job to mark expired coupons as status='expired'.
• Query Efficiency: To quickly check a client’s available coupons, use this query:

  SELECT COUNT(*) FROM coupons WHERE company_id = ? AND status = 'active';
  

• Employee Count Verification: Use the verified_at field in monthly_employee_counts to only issue coupons after confirming the count with the client, avoiding disputes.

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