mysql8-crm-schema-expert

by encryptshawn

Install this Skill

curl -fsSL -o mysql8-crm-schema-expert.zip https://skill.seembox.com/api/v1/skills/mysql8-crm-schema-expert/download && unzip mysql8-crm-schema-expert.zip

Download mysql8-crm-schema-expert.zip

SKILL.md About

---
name: mysql8-design-crm
version: "1.0.0"
description: >
  MySQL 8 database schema design for CRM systems. Use this skill whenever the user needs to design,
  review, optimize, or generate database schemas for Customer Relationship Management systems.
  Triggers on: CRM database design, CRM schema, customer database, contact management database,
  sales pipeline database, lead tracking schema, opportunity management tables, CRM entity relationships,
  CRM data model, accounts/contacts/deals schema, activity tracking database, CRM audit trail,
  CRM custom fields, EAV pattern for CRM, CRM soft deletes, polymorphic relationships in CRM,
  sales funnel database, customer lifecycle database, CRM normalization, CRM indexing strategy,
  MySQL CRM tables, or any database design work involving customer relationship management concepts.
  Also trigger when the user mentions designing tables for contacts, accounts, opportunities, leads,
  deals, pipelines, activities, notes, tasks, campaigns, or any combination of these CRM entities.
---

# 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:

| File | When to Read |
|------|-------------|
| `core-entities.md` | Designing the foundational CRM tables (accounts, contacts, leads, opportunities, etc.) |
| `relationships-and-normalization.md` | Establishing foreign keys, junction tables, and achieving proper normal forms |
| `indexing-and-performance.md` | Creating indexes, query optimization, partitioning, and performance tuning |
| `custom-fields-and-flexibility.md` | Implementing EAV patterns, JSON columns, or hybrid approaches for user-defined fields |
| `audit-and-soft-deletes.md` | Change tracking, audit trails, soft delete patterns, and compliance logging |
| `activities-and-timeline.md` | Polymorphic activity feeds, notes, tasks, emails, calls, and event tracking |
| `security-and-multitenancy.md` | Row-level security, role-based access, tenant isolation, and data privacy |
| `migrations-and-seeding.md` | Schema versioning, migration scripts, and realistic test data generation |
| `reference-schemas.md` | Complete example schemas you can use as starting points |

## 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.

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

3. **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`.

4. **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`.

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

6. **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.

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

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

9. **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`.

10. **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:

```sql
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.

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

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

4. **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.

5. **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.

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

7. **Generate migration scripts.** Read `migrations-and-seeding.md` and output versioned, idempotent migration SQL.

8. **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

| Situation | Action |
|-----------|--------|
| User needs a full CRM from scratch | Read `core-entities.md` + `reference-schemas.md`, design all modules |
| User needs just contacts + accounts | Read `core-entities.md`, design the contact/account module only |
| User asks about custom fields | Read `custom-fields-and-flexibility.md` |
| User has performance concerns | Read `indexing-and-performance.md` |
| User needs GDPR/compliance support | Read `audit-and-soft-deletes.md` + `security-and-multitenancy.md` |
| User is building multi-tenant SaaS CRM | Read `security-and-multitenancy.md` |
| User wants to track all user activity | Read `activities-and-timeline.md` |
| User needs migration scripts | Read `migrations-and-seeding.md` |
| User wants a ready-to-use schema | Read `reference-schemas.md` |

_meta.json Metadata

{
  "ownerId": "kn77nfg6wv2expv6qs7k17dfqs83zp59",
  "publishedAt": 1775519803644,
  "slug": "mysql8-crm-schema-expert",
  "version": "1.0.0"
}