mysql8-crm-schema-expert（mysql8 CRM schema expert 开发）

MySQL 8 CRM Database Design Skill

A comprehensive guide for designing production-quality MySQL 8 database schemas for CRM (Customer Relationship Management) systems. This skill covers everything from core entity design to advanced patterns like EAV custom fields, polymorphic activities, audit trails, and multi-tenant architectures.

How to Use This Skill

This skill is organized into a main guide (this file) and detailed reference documents. Read the relevant reference file before generating any SQL or making design decisions.

Reference Files

Read these from references/ as needed:

Core Design Principles

When designing a CRM database on MySQL 8, always follow these principles:

1. Relational integrity first. Define FOREIGN KEY constraints at the database level. Never rely on application code alone to maintain referential integrity.

1. Normalize to 3NF, then denormalize deliberately. Start at Third Normal Form. Only denormalize when you have measured performance evidence, and document the reason.

1. Consistent naming conventions. Use snake_case for all identifiers. Table names are plural (contacts, accounts). Foreign keys follow the pattern {singular_referenced_table}_id (e.g., account_id). Timestamps are created_at, updated_at, deleted_at.

1. Every table gets an audit baseline. At minimum: id (BIGINT UNSIGNED AUTO_INCREMENT), created_at, updated_at. Most CRM tables also need created_by and updated_by.

1. Soft deletes over hard deletes. CRM data has legal, compliance, and historical reporting value. Use deleted_at (TIMESTAMP NULL) rather than DELETE statements.

1. Use BIGINT UNSIGNED for primary keys. INT runs out at ~2.1 billion. CRM tables like activities and audit logs grow fast. BIGINT UNSIGNED gives you headroom through 18.4 quintillion.

1. UTF8MB4 everywhere. Always CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci. Customer names, notes, and communications contain international characters and emoji.

1. InnoDB only. All tables use InnoDB for transaction support, row-level locking, foreign key enforcement, and crash recovery.

1. Timestamps use DATETIME(3) or TIMESTAMP. For CRM, prefer DATETIME(3) for event times (timezone-independent, millisecond precision). Use TIMESTAMP for created_at/updated_at with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

1. Design for integration. CRM systems connect to email, marketing, billing, and support tools. Include external_id or external_source columns on entities that sync with third-party systems.

Standard Table Template

Every CRM table should follow this baseline structure:

CREATE TABLE `table_name` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- entity-specific columns here --

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
    `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL,

    PRIMARY KEY (`id`),
    INDEX `idx_table_name_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Workflow for Designing a CRM Schema

Follow this sequence when the user asks you to design a CRM database:

1. Clarify scope. Determine which CRM modules are needed: contacts/accounts, sales pipeline, marketing/campaigns, support/tickets, or all of the above.

1. Read the relevant reference files. Always start with core-entities.md. Add others based on the modules identified.

1. Design entities first, relationships second. List the tables and their columns, then define the foreign keys and junction tables.

1. Apply indexing strategy. Read indexing-and-performance.md and add indexes for every foreign key, every column used in WHERE/JOIN/ORDER BY, and composite indexes for common query patterns.

1. Add flexibility layer. If the user needs custom fields, read custom-fields-and-flexibility.md and choose between EAV, JSON columns, or a hybrid approach.

1. Add audit and compliance. Read audit-and-soft-deletes.md and implement the appropriate level of change tracking.

1. Generate migration scripts. Read migrations-and-seeding.md and output versioned, idempotent migration SQL.

1. Review and validate. Walk through the schema checking for: missing indexes on FKs, missing NOT NULL constraints, missing default values, orphan risk, and query patterns that would cause full table scans.

MySQL 8 Features to Leverage

These MySQL 8 specific features are particularly valuable for CRM schemas:

• JSON columns for semi-structured data (custom fields, metadata, integration payloads). See custom-fields-and-flexibility.md.
• Generated columns (VIRTUAL or STORED) to extract and index JSON values.
• Functional indexes (8.0.13+) to index expressions without explicit generated columns.
• Multi-valued indexes (8.0.17+) to index JSON arrays efficiently.
• Common Table Expressions (CTEs) for recursive queries on hierarchical data (org charts, account hierarchies, nested categories).
• Window functions for pipeline analytics (running totals, rank, lead/lag).
• CHECK constraints for data validation at the database level.
• DEFAULT expressions for computed defaults.
• Invisible indexes for safe testing of index removal.
• Descending indexes for optimizing ORDER BY ... DESC queries.

Quick Decision Guide