MySQL8 CRM Schema Design Expert — MySQL8 CRM 模式 De签名 Expert

MySQL 8 CRM Database De签名 技能

A comprehensive 图形界面de for de签名ing production-质量 MySQL 8 database 模式s for CRM (Customer Relationship Management) 系统s. This 技能 covers everything from core entity de签名 to advanced patterns like EAV custom fields, polymorphic activities, 审计 trAIls, and multi-tenant architectures.

How to Use This 技能

This 技能 is organized into a mAIn 图形界面de (this file) and detAIled reference documents. Read the relevant reference file before generating any SQL or making de签名 decisions.

Reference Files

Read these from references/ as needed:

File When to Read core-entities.md De签名ing the foundational CRM tables (accounts, contacts, leads, opportunities, etc.) relationships-and-normalization.md Establishing foreign keys, junction tables, and achieving proper normal forms 索引ing-and-performance.md Creating 索引es, 查询 optimization, partitioning, and performance tuning custom-fields-and-flexibility.md Implementing EAV patterns, JSON columns, or hybrid 应用roaches for user-defined fields 审计-and-soft-删除s.md Change 追踪ing, 审计 trAIls, soft 删除 patterns, and 合规 记录ging activities-and-timeline.md Polymorphic activity feeds, notes, tasks, emAIls, calls, and event 追踪ing security-and-multitenancy.md Row-level security, 角色-based 访问, tenant isolation, and data 隐私 迁移s-and-种子ing.md 模式 versioning, 迁移 scripts, and rea列出ic test data generation reference-模式s.md Complete example 模式s you can use as 启动ing points Core De签名 Principles

When de签名ing a CRM database on MySQL 8, always follow these principles:

Relational integrity first. Define FOREIGN KEY constrAInts at the database level. Never rely on 应用 code alone to mAIntAIn referential integrity.

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

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 创建d_at, 更新d_at, 删除d_at.

Every table 获取s an 审计 baseline. At minimum: id (BIGINT UN签名ED AUTO_INCREMENT), 创建d_at, 更新d_at. Most CRM tables also need 创建d_by and 更新d_by.

Soft 删除s over hard 删除s. CRM data has legal, 合规, and historical 报告ing value. Use 删除d_at (TIMESTAMP NULL) rather than 删除 状态ments.

Use BIGINT UN签名ED for primary keys. INT 运行s out at ~2.1 billion. CRM tables like activities and 审计 记录s grow fast. BIGINT UN签名ED gives you headroom through 18.4 quintillion.

UTF8MB4 everywhere. Always CHARACTER 设置 utf8mb4 COLLATE utf8mb4_unicode_ci. Customer names, notes, and communications contAIn international characters and emoji.

InnoDB only. All tables use InnoDB for transaction support, row-level locking, foreign key enforcement, and crash 恢复y.

Timestamps use DATETIME(3) or TIMESTAMP. For CRM, prefer DATETIME(3) for event times (timezone-independent, millisecond precision). Use TIMESTAMP for 创建d_at/更新d_at with DEFAULT CURRENT_TIMESTAMP and ON 更新 CURRENT_TIMESTAMP.

De签名 for integration. CRM 系统s connect to emAIl, marketing, billing, and support 工具s. Include external_id or external_source columns on entities that 同步 with third-party 系统s.

Standard Table Template

Every CRM table should follow this baseline structure:

创建 TABLE table_name ( id BIGINT UN签名ED NOT NULL AUTO_INCREMENT,

-- entity-specific columns here --

创建d_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 更新d_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON 更新 CURRENT_TIMESTAMP, 删除d_at TIMESTAMP NULL DEFAULT NULL, 创建d_by BIGINT UN签名ED NULL DEFAULT NULL, 更新d_by BIGINT UN签名ED NULL DEFAULT NULL,

PRIMARY KEY (id), 索引 idx_table_name_删除d_at (删除d_at) ) ENGINE=InnoDB DEFAULT CHAR设置=utf8mb4 COLLATE=utf8mb4_unicode_ci;

工作流 for De签名ing a CRM 模式

Follow this sequence when the user asks you to de签名 a CRM database:

Clarify scope. Determine which CRM 模块s are needed: contacts/accounts, sales 流水线, marketing/campAIgns, support/tickets, or all of the above.

Read the relevant reference files. Always 启动 with core-entities.md. 添加 others based on the 模块s identified.

De签名 entities first, relationships second. 列出 the tables and their columns, then define the foreign keys and junction tables.

应用ly 索引ing strategy. Read 索引ing-and-performance.md and 添加 索引es for every foreign key, every column used in WHERE/JOIN/ORDER BY, and composite 索引es for common 查询 patterns.

添加 flexibility layer. If the user needs custom fields, read custom-fields-and-flexibility.md and choose between EAV, JSON columns, or a hybrid 应用roach.

添加 审计 and 合规. Read 审计-and-soft-删除s.md and implement the 应用ro