AGENT SKILL

data-modeling

Schema design, entity relationships, normalization, and database patterns. Use when designing database schemas, modeling domain entities, deciding between normalized and denormalized structures, choosing between relational and NoSQL approaches, or planning schema migrations. Covers ER modeling, normal forms, and data evolution strategies.

适用职业数据分析师
License 许可
MIT
最近更新
2026-11-01
Stars
★ 3
Forks
1

01 前言 Front Matter

name
data-modeling
description
Schema design, entity relationships, normalization, and database patterns. Use when designing database schemas, modeling domain entities, deciding between normalized and denormalized structures, choosing between relational and NoSQL approaches, or planning schema migrations. Covers ER modeling, normal forms, and data evolution strategies.

02 资源文件 1

03 SKILL.md 正文

Data Modeling

When to Use

  • Designing new database schemas from domain requirements
  • Analyzing existing schemas for optimization opportunities
  • Deciding between normalized and denormalized structures
  • Choosing appropriate data stores (relational vs NoSQL)
  • Planning schema evolution and migration strategies
  • Modeling complex entity relationships

Philosophy

Data models outlive applications. A well-designed schema encodes business rules, enforces integrity, and enables performance optimization. The goal is to create models that are correct first, then optimize for access patterns while maintaining data integrity.

Entity-Relationship Modeling

Identifying Entities

Entities represent distinct business concepts that have identity and lifecycle.

Entity Identification Checklist:

  • Has unique identity across the system
  • Has attributes that describe it
  • Participates in relationships with other entities
  • Has a meaningful lifecycle (created, modified, archived)
  • Would be stored and retrieved independently

Common Entity Patterns:

  • Core domain objects (User, Product, Order)
  • Reference/lookup data (Country, Status, Category)
  • Transactional records (Payment, LogEntry, Event)
  • Associative entities (OrderItem, Enrollment, Permission)

Relationship Types

Type Notation Example Implementation
One-to-One 1:1 User - Profile FK with unique constraint or same table
One-to-Many 1:N Customer - Orders FK on the "many" side
Many-to-Many M:N Students - Courses Junction/bridge table

Relationship Considerations:

  • Cardinality: minimum and maximum on each side
  • Optionality: required vs optional participation
  • Direction: unidirectional vs bidirectional navigation
  • Cascade behavior: what happens on delete/update

Attribute Analysis

Attribute Types:

  • Simple: single atomic value (name, price)
  • Composite: structured value (address = street + city + postal)
  • Derived: calculated from other attributes (age from birthdate)
  • Multi-valued: repeating values (phone numbers, tags)

Key Types:

  • Natural key: business-meaningful identifier (SSN, ISBN)
  • Surrogate key: system-generated identifier (UUID, auto-increment)
  • Composite key: multiple columns forming identity
  • Candidate key: any attribute(s) that could serve as primary key

Best Practice: Prefer surrogate keys for primary keys; use natural keys as unique constraints.

Normalization

Normal Forms Progression

Each normal form builds on the previous. Normalize until requirements dictate otherwise.

First Normal Form (1NF)

Rule: Eliminate repeating groups; each cell contains atomic values.

Violation Example:

Order(id, customer, items: "widget,gadget,thing")

Resolution:

Order(id, customer)
OrderItem(order_id, item_name)

Second Normal Form (2NF)

Rule: Remove partial dependencies on composite keys.

Violation Example:

OrderItem(order_id, product_id, product_name, quantity)
                                 ^-- depends only on product_id

Resolution:

OrderItem(order_id, product_id, quantity)
Product(product_id, product_name)

Third Normal Form (3NF)

Rule: Remove transitive dependencies; non-key columns depend only on the key.

Violation Example:

Employee(id, department_id, department_name)
                            ^-- depends on department_id, not employee id

Resolution:

Employee(id, department_id)
Department(id, name)

Boyce-Codd Normal Form (BCNF)

Rule: Every determinant is a candidate key.

Violation Example:

CourseOffering(student, course, instructor)
-- Constraint: each instructor teaches only one course
-- instructor -> course (but instructor is not a candidate key)

Resolution:

InstructorCourse(instructor, course) -- instructor is key
Enrollment(student, instructor) -- references instructor

When to Stop Normalizing

Stop at 3NF for most OLTP systems. Consider BCNF when:

  • Update anomalies cause data corruption
  • Data integrity is paramount
  • Write frequency is high

Denormalization Strategies

Denormalize intentionally for read performance, not out of laziness.

Calculated Columns

Store derived values to avoid repeated computation.

Order
  - subtotal (calculated once on item changes)
  - tax_amount (calculated once)
  - total (calculated once)

Trade-off: Faster reads, more complex writes, potential consistency issues.

Materialized Relationships

Embed frequently-accessed related data.

Post
  - author_id
  - author_name (copied from User.name)
  - author_avatar_url (copied from User.avatar_url)

Trade-off: Eliminates joins, requires synchronization on source changes.

Aggregation Tables

Pre-compute summaries for reporting.

DailySales
  - date
  - product_id
  - units_sold (sum)
  - revenue (sum)

Trade-off: Fast analytics, storage overhead, stale until refreshed.

Denormalization Decision Matrix

Factor Normalize Denormalize
Write frequency High Low
Read frequency Low High
Data consistency Critical Eventual OK
Query complexity Simple Complex joins
Data size Small Large

NoSQL Data Modeling Patterns

Document Stores (MongoDB, DynamoDB)

Embedding Pattern: Embed related data that is read together and has 1:few relationship.

{
  "order_id": "123",
  "customer": {
    "id": "456",
    "name": "Jane Doe",
    "email": "jane@example.com"
  },
  "items": [
    {"product_id": "A1", "name": "Widget", "quantity": 2}
  ]
}

Referencing Pattern: Reference related data when it changes independently or is shared.

{
  "order_id": "123",
  "customer_id": "456",
  "item_ids": ["A1", "B2"]
}

Hybrid Pattern: Embed summary data, reference for full details.

{
  "order_id": "123",
  "customer_summary": {
    "id": "456",
    "name": "Jane Doe"
  },
  "items": [
    {"product_id": "A1", "name": "Widget", "quantity": 2}
  ]
}

Key-Value Stores

Access Pattern Design: Design keys around query patterns.

USER:{user_id} -> user data
USER:{user_id}:ORDERS -> list of order ids
ORDER:{order_id} -> order data

Composite Keys: Combine entity type with identifiers for namespacing.

Wide-Column Stores (Cassandra, HBase)

Partition Key Design: Choose partition keys for even distribution and access locality.

Primary Key: (user_id, order_date)
             ^-- partition key (distribution)
                       ^-- clustering column (ordering)

Avoid:

  • High-cardinality partition keys causing hot spots
  • Large partitions exceeding recommended sizes
  • Scatter-gather queries across partitions

Graph Databases

Node and Relationship Design:

  • Nodes: entities with properties
  • Relationships: named, directed, with properties
  • Labels: categorize nodes for efficient traversal
(User)-[:PURCHASED {date, amount}]->(Product)
(User)-[:FOLLOWS]->(User)
(Product)-[:BELONGS_TO]->(Category)

Schema Evolution Strategies

Additive Changes (Safe)

  • Add new nullable columns
  • Add new tables
  • Add new indexes
  • Add new optional fields (NoSQL)

Breaking Changes (Require Migration)

  • Remove columns/tables
  • Rename columns/tables
  • Change data types
  • Add non-nullable columns without defaults

Migration Patterns

Expand-Contract Pattern:

  1. Add new column alongside old
  2. Backfill new column from old
  3. Update application to use new column
  4. Remove old column

Blue-Green Schema:

  1. Create new version of schema
  2. Dual-write to both versions
  3. Migrate reads to new version
  4. Drop old version

Versioned Documents (NoSQL):

{
  "_schema_version": 2,
  "name": "Jane",
  "email": "jane@example.com"
}

Handle multiple versions in application code during transition.

Best Practices

  • Model the domain first, then optimize for access patterns
  • Use surrogate keys for primary keys; natural keys as unique constraints
  • Normalize to 3NF for OLTP; denormalize deliberately for read-heavy loads
  • Document all foreign key relationships and cascade behaviors
  • Version control all schema changes as migration scripts
  • Test migrations on production-like data volumes
  • Consider query patterns when designing NoSQL schemas
  • Plan for schema evolution from day one

Anti-Patterns

  • Designing schemas around UI forms instead of domain concepts
  • Using generic columns (field1, field2, field3)
  • Entity-Attribute-Value (EAV) for structured data
  • Storing comma-separated values in single columns
  • Circular foreign key dependencies
  • Missing indexes on foreign key columns
  • Hard-deleting data without soft-delete consideration
  • Ignoring temporal aspects (effective dates, audit trails)

References

04 同仓库更多 Skills

shengdabai
shengdabai/Tony-Claude-Code-Skills

seedance-prompt-en

Write effective prompts for Jimeng Seedance 2.0 multimodal AI video generation. Use when users want to create video prompts using text, images, videos, and audio inputs with the @ reference system. Covers camera movements, effects replication, video extension, editing, music beat-matching, e-commerce ads, short dramas, and educational content.

★ 3
shengdabai
shengdabai/Tony-Claude-Code-Skills

onepage-pdf

Convert an HTML page into a single continuous-page PDF (one tall page, no pagination breaks), preserving desktop layout, backgrounds and selectable text. Use when the user asks to turn an HTML file/report/proposal into a "single-page PDF", "long PDF", "不分页 PDF", "单页 PDF", "长图式 PDF", or complains that an HTML-to-PDF export breaks into pages or cuts content. Supports optional string redaction with leak verification.

★ 3
shengdabai
shengdabai/Tony-Claude-Code-Skills

native-feel-cross-platform-desktop

Use when the user is designing, prototyping, or rewriting a desktop app that must run on multiple OSes (macOS + Windows, optionally Linux) AND feel indistinguishable from a native app to its users — fast launch, native windowing, native input handling, native materials. Trigger words include "cross-platform desktop", "Electron alternative", "Tauri vs native", "WebView wrapper", "near-native performance", "Raycast architecture", "WebKit/WebView2 quirks", "WKWebView", "system tray app", "global hotkey app", "launcher app". Do NOT trigger this skill for pure web apps, pure mobile apps, or for greenfield projects that have no native-feel requirement.

★ 3
shengdabai
shengdabai/Tony-Claude-Code-Skills

gstack

Router for the gstack skill suite. (gstack)

★ 3
shengdabai
shengdabai/Tony-Claude-Code-Skills

fireworks-tech-graph

Use when the user wants to create any technical diagram - architecture, data flow, flowchart, sequence, agent/memory, or concept map - and export as SVG+PNG. Trigger on: "画图" "帮我画" "生成图" "做个图" "架构图" "流程图" "可视化一下" "出图" "generate diagram" "draw diagram" "visualize" or any system/flow description the user wants illustrated.

★ 3
shengdabai
shengdabai/Tony-Claude-Code-Skills

dbs-video-extract

接收抖音、小红书、微信视频号等短视频链接或分享文案,查询作品/账号数据并提取语音文字稿,按作者和标题归档为 Markdown。用户希望完整解析短视频、取得数据或文字稿时使用;运行前检查 TikHub 与轻抖使用凭证,缺少时用新手能理解的方式引导购买和安全配置。

★ 3