skills.rest
onzhang
onzhangCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

database-design-guide

Design MySQL schemas with standardized naming, typing, indexing, and versioned migrations.

2|Updated May 1, 2026
Search Tags:#indexing#database#migrations#schema#orm#mysql
One-click install
npx skills add https://github.com/onzhang/BookNexus --skill database-design-guide
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-design-guide
Source: https://github.com/onzhang/BookNexus/tree/main/skills/database-design-guide
Command: npx skills add https://github.com/onzhang/BookNexus --skill database-design-guide

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This guide provides a comprehensive framework for designing robust, scalable MySQL schemas, ensuring consistency across projects and reducing data integrity risks.

Core Features & Use Cases

  • Standardized naming conventions (snake_case) for databases, tables, and columns, with clear, descriptive names.
  • Thoughtful data typing guidance, including primary key strategy, audit fields, and proper use of datetime, decimal, and JSON types.
  • Indexing and normalization guidance to optimize query performance while balancing denormalization when appropriate for typical read patterns.
  • Versioned database migrations and environment-specific naming to support Flyway or Liquibase workflows.
  • Real-world templates and patterns tailored for Spring Boot + MyBatis stack, such as user, role, and borrow records tables.

Quick Start

Create a base schema for a BookNexus-like project using snake_case naming, BIGINT unsigned primary keys, DATETIME(3) audit fields, utf8mb4 charset, and Flyway-based migrations.

Frequently Asked Questions about database-design-guide

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a scalable MySQL schema for a Spring Boot project?▼

Design scalable MySQL schemas by standardizing snake_case naming, BIGINT unsigned primary keys, DATETIME(3) audit fields, and utf8mb4 charset to prevent data inconsistencies across Spring Boot MyBatis projects.

What is the best primary key and audit field strategy for MySQL database migrations?▼

The best MySQL strategy uses BIGINT unsigned primary keys paired with DATETIME(3) audit fields to ensure scalable records and precise timestamp tracking during Flyway or Liquibase versioned migrations.

How do I manage database migrations with Flyway or Liquibase in a MyBatis stack?▼

Manage database migrations with Flyway or Liquibase by applying versioned scripts and environment-specific naming to enforce schema consistency across Spring Boot MyBatis application environments.

Does this MySQL schema design guidance work for book management systems?▼

Yes, this MySQL schema design guidance works for book management systems by providing real-world templates for user, role, and borrow records tables while standardizing indexing for typical read patterns.

When should I use denormalization and JSON data types in MySQL?▼

Use denormalization and JSON data types in MySQL when balancing query performance against typical read patterns, applying thoughtful typing and normalization adjustments to optimize indexing for specific application read requirements.

Related Skills

Explore More Software Engineering Skills→
Mark393295827Mark393295827
1

jpa-patterns

Apply JPA/Hibernate best practices for entity design and query optimization in Spring Boot.

Community
Advanced
inbharataiinbharatai
22

migration-generator

Generate up/down database migration scripts for Alembic, Flyway, and Liquibase.

Community
Intermediate
chrysaliscatchrysaliscat
1

Database Expert

Optimize MySQL queries, MyBatis XML mappings, and schema design.

Community
Intermediate
zebbernzebbern
5

database-patterns

Design database schemas and migrations with naming, typing, and indexing conventions.

Community
Advanced