CRM类系统多维度每日统计数据库设计及方案可行性咨询

Hey there! Let's break down your problem and your current daily snapshot approach, then talk about better alternatives that scale better and are easier to maintain for your multi-parameter filtering needs.

First: What's wrong with your current full daily snapshot plan? #

Your idea works for now with 6000 leads, but it has some hidden issues that will bite you as you scale or add more fields:

• Massive data redundancy: Copying all 6000 leads every day means you'll end up with 219k+ rows after a year, most of which are identical to previous days' entries (since only a small subset of leads change status or attributes daily). That's wasted storage and unnecessary compute overhead.
• Rigid structure: If you add new custom fields later, you'll have to modify your stats table schema and backfill all historical snapshot data—this gets messy fast.
• No guarantee of better performance: Without proper indexing on your stats table, querying it might be just as slow as querying the original leads table (if not slower, thanks to the larger dataset).

Better Approaches for Multi-Parameter Statistical Queries #

Option 1: Optimize the Original Leads Table (Best for Current Scale) #

You don't need a separate stats table at all right now—6000 rows is tiny for modern databases, and a well-designed original table with proper indexes can handle your complex queries in milliseconds.

Key Table Design Tweaks:

• Add a status_updated_at column to your leads table. This tracks when a lead switched to its current status (critical for your example query, where you need to count leads that became "sold" between Jan-Mar 2018).
• Keep custom fields directly in the table (or use JSON/JSONB fields if you expect frequent new custom fields—more on that later).

Indexing for Your Example Query:

Create a composite index that covers all your filter dimensions:

CREATE INDEX idx_leads_filter ON leads (
  created_at, 
  custom_field1, 
  custom_field2, 
  status, 
  status_updated_at
);

This lets the database quickly narrow down rows without scanning the entire table.

Example Real-Time Query:

Your exact requirement can be answered directly on the original table with this SQL:

SELECT COUNT(*) AS sold_lead_count
FROM leads
WHERE created_at BETWEEN '2017-10-01' AND '2017-10-31'
  AND custom_field1 = 22
  AND custom_field2 = 1
  AND status = 'sold'
  AND status_updated_at BETWEEN '2018-01-01' AND '2018-03-31';

Option 2: Incremental Snapshot (If You Still Want a Stats Table) #

If you prefer keeping a separate stats table for some reason, switch to incremental updates instead of full daily copies:

• Add a last_modified_at column to your leads table.
• Every day, only copy leads where last_modified_at >= CURRENT_DATE (or the start of the current day). This way, you only add rows for leads that changed in some way (status, custom fields, etc.).
• Add the same composite indexes as above to your stats table to speed up queries.

Option 3: Handle Scaling & Flexible Custom Fields #

If you expect leads to grow to hundreds of thousands or need to add custom fields frequently:

• JSON/JSONB Fields: Store custom fields in a single JSONB column (PostgreSQL) or JSON column (MySQL). You can still index individual keys in the JSON data—for example, in PostgreSQL:

  CREATE INDEX idx_leads_custom1 ON leads ((custom_fields->>'custom_field1'));
  

• Partitioned Tables: Partition the leads table by created_at or status_updated_at (e.g., monthly partitions). This makes queries over date ranges scan only the relevant partitions, drastically speeding things up for large datasets.
• Materialized Views: Use database-managed materialized views to precompute common stats. For example, a view that aggregates leads by creation month, custom field values, and status. You can refresh it daily (or hourly if needed) instead of manually copying data.

Option 4: EAV Model (For Extreme Custom Field Flexibility) #

If you have dozens of dynamic custom fields, use an Entity-Attribute-Value (EAV) model:

• Create a lead_custom_fields table with columns: lead_id, field_name, field_value.
• Index this table on (lead_id, field_name, field_value) for fast lookups.
• Your example query would use joins to filter custom fields:

  SELECT COUNT(*) AS sold_lead_count
  FROM leads l
  JOIN lead_custom_fields cf1 
    ON l.id = cf1.lead_id 
    AND cf1.field_name = 'custom_field1' 
    AND cf1.field_value = '22'
  JOIN lead_custom_fields cf2 
    ON l.id = cf2.lead_id 
    AND cf2.field_name = 'custom_field2' 
    AND cf2.field_value = '1'
  WHERE l.created_at BETWEEN '2017-10-01' AND '2017-10-31'
    AND l.status = 'sold'
    AND l.status_updated_at BETWEEN '2018-01-01' AND '2018-03-31';
  

Note: EAV can get slow for very large datasets, so use it only if you truly need extreme flexibility.

Final Recommendation #

For your current 6000-lead scale, skip the daily full snapshot entirely. Optimize your original leads table with a status_updated_at column and composite indexes, and use real-time queries. This is simpler, cheaper, and easier to maintain long-term.

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