skills.rest
guygrigsby
guygrigsbyCOMMUNITY
Explore all skills by this author →
Website𝕏@usernamevalidGitHub

oltp-schema-design

Design PostgreSQL OLTP schemas with 5NF normalization and ACID constraints.

Updated Nov 30, 2018
Search Tags:#postgresql#indexes#normalization#data-integrity#schema-design#transactional#oltp
One-click install
npx skills add https://github.com/guygrigsby/dotfiles --skill oltp-schema-design
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: oltp-schema-design
Source: https://github.com/guygrigsby/dotfiles/tree/main/opencode/skills/oltp-schema-design
Command: npx skills add https://github.com/guygrigsby/dotfiles --skill oltp-schema-design

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Design PostgreSQL OLTP schemas that ensure data integrity and high write throughput for concurrent transactional workloads.

Core Features & Use Cases

  • Normalization up to 5NF where appropriate to minimize redundancy and update anomalies.
  • Enforcement of ACID guarantees, foreign key constraints, and row-level locking for safe high-concurrency operations.
  • Practical patterns and guidance for mapping DDD aggregates and designing index strategies for OLTP workloads.

Quick Start

Design an OLTP schema for a high-concurrency orders system.

Frequently Asked Questions about oltp-schema-design

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

FAQPage Schema
How do I design a PostgreSQL schema for high-concurrency transactional workloads?▼

Design a PostgreSQL schema for high-concurrency transactional workloads by enforcing 5NF normalization, applying B-tree indexing strategies, and utilizing row-level locking to maintain ACID guarantees and maximize write throughput.

What is the best way to map DDD aggregates in an OLTP database schema?▼

Map DDD aggregates in an OLTP schema by structuring PostgreSQL tables around transactional boundaries, enforcing foreign key constraints, and using row-level locking to ensure data integrity across concurrent operations.

How does normalization up to 5NF improve write-heavy PostgreSQL applications?▼

Normalization up to 5NF improves write-heavy PostgreSQL applications by minimizing data redundancy and update anomalies, which stabilizes transactional throughput and enforces strict data integrity.

Can I use row-level locking to prevent conflicts in concurrent e-commerce inventory systems?▼

Yes, you can use row-level locking to prevent conflicts in concurrent e-commerce inventory systems by ensuring ACID guarantees during simultaneous inventory updates and order processing transactions.

What B-tree index strategy should I use for an order processing database?▼

For an order processing database, use B-tree indexes on foreign keys and high-cardinality query columns to optimize transactional throughput while maintaining ACID guarantees and data integrity.

When should I not use a strict 5NF normalization approach for my database schema?▼

You should avoid strict 5NF normalization when your system prioritizes read-heavy analytical queries over transactional throughput, as the join complexity will degrade performance compared to denormalized analytical schemas.

Related Skills

Explore More Software Engineering Skills→
shafibabarshafibabar
1

data-model-design

Design normalized OLTP schemas and dimensional OLAP marts with PostgreSQL constraints.

Community
Advanced
isiouisiou

postgresql

Design PostgreSQL schemas with constraints, indexes, partitioning, and row-level security.

Community
Advanced
lamb92009lamb92009

database-optimizer

Analyze slow queries and execution plans to optimize PostgreSQL and MySQL performance.

Community
Advanced
pohlai88pohlai88

database-schema-designer

Design SQL and NoSQL database schemas with normalization, keys, constraints, indexing, and reversible migrations.

Community
Advanced