Database Design: Let AI Agents Design Table Relationships, Indexes, Constraints and ORM According to Specifications

# SKILL.md for Database Design (Included in awesome-cursor-skills) ## Overview This is a database modeling skill collected in awesome-cursor-skills, which covers entity identification, table relationships, constraints, indexes, and Prisma/Drizzle configuration. ## Six-step Workflow Verified against the official original text, the workflow is as follows: 1. **Entity Identification**: Extract core business entities from requirements 2. **Define Entity Attributes**: Clarify each entity's fields and data types 3. **Establish Table Relationships**: Map one-to-one, one-to-many, and many-to-many associations between entities 4. **Set Up Constraints**: Add NOT NULL, unique, primary key, foreign key and other constraints to ensure data integrity 5. **Design Indexes**: Create appropriate indexes to optimize query performance 6. **Generate ORM Configuration**: Write standardized Prisma or Drizzle schema files based on the designed database structure ## PostgreSQL Example & Installation Method ### Example Code ```sql -- Sample PostgreSQL table creation script CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE posts ( id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, content TEXT, author_id INTEGER NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE ); -- Sample index CREATE INDEX idx_posts_author_id ON posts(author_id); ``` ### Prisma Configuration Example ```prisma generator client { provider = "prisma-client-js" } datasource db { provider = "postgresql" url = env("DATABASE_URL") } model User { id Int @id @default(autoincrement()) username String @unique @db.VarChar(50) email String @unique @db.VarChar(255) createdAt DateTime @default(now()) @db.Timestamptz() posts Post[] } model Post { id Int @id @default(autoincrement()) title String @db.VarChar(255) content String? authorId Int author User @relation(fields: [authorId], references: [id], onDelete: Cascade) createdAt DateTime @default(now()) @db.Timestamptz() } ``` ### Drizzle Configuration Example ```typescript import { pgTable, serial, varchar, text, integer, timestamp, unique } from 'drizzle-orm/pg-core'; export const users = pgTable('users', { id: serial('id').primaryKey(), username: varchar('username', { length: 50 }).notNull(), email: varchar('email', { length: 255 }).notNull(), createdAt: timestamp('created_at').defaultNow().notNull() }, (table) => { return { usernameUnique: unique().on(table.username), emailUnique: unique().on(table.email), } }); export const posts = pgTable('posts', { id: serial('id').primaryKey(), title: varchar('title', { length: 255 }).notNull(), content: text('content'), authorId: integer('author_id').notNull(), createdAt: timestamp('created_at').defaultNow().notNull() }, (table) => { return { authorRef: foreignKey({ columns: [table.authorId], foreignColumns: [users.id] }).onDelete('cascade'), authorIndex: index('idx_posts_author_id').on(table.authorId) } }); ``` ## Supplementary Role of AI Programming This skill makes up for the shortcomings of AI programming in schema design: 1. Provides standardized, step-bysted operating specifications to avoid AI from generating incomplete or non-compliant database schemas 2. Combines business requirements with technical implementation, helping AI transform abstract business logic into a practical, production-ready database structure 3. Standardizes the connection between logical design and physical implementation, ensuring the consistency of schema design from demand analysis to ORM code generation ## Usage Restrictions 1. **PostgreSQL Dialect Limitation**: The current skill takes PostgreSQL as the only demonstration database, and may need to be adjusted when adapting to MySQL, SQL Server and other relational databases 2. **ORM Scope Limitation**: Only covers Prisma and Drizzle two mainstream Node.js ORM frameworks, and is not applicable to ORM tools in other programming languages or non-mainstream Node.js ORMs 3. **Basic Database Design Only**: Focuses on conventional relational database modeling, and does not cover special scenarios such as distributed databases, columnar storage databases, and non-relational databases 4. Requires manual verification: AI generated content still needs developers to check for business matching, performance bottlenecks and compliance issues in actual production environments