database-standard
Database design standards defining primary keys, foreign keys, audit fields, soft delete, junction tables. PostgreSQL style preferred, SQL lowercase without comments.
When & Why to Use This Skill
This Claude skill automates the application of professional PostgreSQL database design standards to ensure high data integrity and architectural consistency. It enforces strict rules for primary keys, foreign key constraints, and standardized audit fields (crd, mrd, rlv), while providing a clean, lowercase SQL coding style. By implementing best practices for soft deletes, junction tables, and tree structures, it helps developers build scalable and maintainable database schemas optimized for modern backend systems.
Use Cases
- Scenario 1: Generating production-ready PostgreSQL table schemas with built-in audit trails and optimistic locking (row lock versioning).
- Scenario 2: Designing efficient junction tables for many-to-many relationships that adhere to minimal-overhead standards.
- Scenario 3: Implementing hierarchical data structures using standardized tree-table patterns for recursive data like address hierarchies or organizational charts.
- Scenario 4: Standardizing SQL code style across a development team to ensure all database scripts are consistent, lowercase, and comment-free for better readability and automation.
| name | database-standard |
|---|---|
| description | Database design standards defining primary keys, foreign keys, audit fields, soft delete, junction tables. PostgreSQL style preferred, SQL lowercase without comments. |
Database Preferences
- Forbidden: MySQL
- Recommended: PostgreSQL or other modern databases
- Required: Foreign key constraints for data integrity
SQL Code Style
- All lowercase
- SQL files have no comments
Primary Key Standard
| Field | Type | Description |
|---|---|---|
id |
bigint | Primary key, required for all regular tables |
Audit Fields
All tables except junction tables MUST include:
| Field | Full Name | Type | Default | Description |
|---|---|---|---|---|
crd |
create row datetime | timestamp | current_timestamp | Row creation time, timezone-independent |
mrd |
modify row datetime | timestamp | null | Last modification time, timezone-independent, nullable |
rlv |
row lock version | integer | 0 | Optimistic lock version |
Soft Delete Field
| Field | Full Name | Type | Default | Description |
|---|---|---|---|---|
ldf |
logic delete field | timestamp | null | Soft delete time, timezone-independent, null means active |
Junction Table Standard
Tables linking two entities:
- No primary key
- No audit fields
- Only foreign key IDs from both tables
Tree Structure
Tables with upward lookup (e.g., address) use pid for parent link:
| Field | Type | Description |
|---|---|---|
pid |
bigint | Parent primary key, nullable |
Examples
Regular Table
create table user (
id bigint primary key,
name varchar(255) not null,
email varchar(255),
ldf timestamp,
crd timestamp not null default current_timestamp,
mrd timestamp,
rlv integer not null default 0
);
Junction Table
create table user_role (
user_id bigint not null references user(id),
role_id bigint not null references role(id)
);
Tree Table
create table address (
id bigint primary key,
pid bigint references address(id),
name varchar(255) not null,
crd timestamp not null default current_timestamp,
mrd timestamp,
rlv integer not null default 0
);