AI Rules for SQL vs NoSQL Databases

The Database Paradigm Determines Every Data Rule

SQL databases (Postgres, MySQL, SQLite): normalized data in tables with rows and columns, relationships via foreign keys, JOINs to combine related data, ACID transactions for consistency, and schemas enforced by the database. The AI must generate: normalized table designs, JOIN queries for related data, foreign key constraints, and migration files for schema changes. NoSQL databases (Firestore, DynamoDB, MongoDB): denormalized documents in collections, embedded data for related objects, multiple queries for cross-document relationships, eventual consistency (by default), and flexible schemas.

Without database paradigm rules: the AI generates SQL patterns for NoSQL (JOIN queries that Firestore cannot execute), NoSQL patterns for SQL (embedding user data inside order rows instead of a foreign key reference), or hybrid patterns that work in neither (denormalized tables with no foreign keys in Postgres — technically valid but loses relational benefits). The database paradigm is: the most fundamental technical choice that shapes every data interaction in the project.

This article provides: the specific AI rules needed for SQL databases, the specific rules for NoSQL, the key differences the AI must know, and templates for each. The database rule should be: in the first 50 words of every CLAUDE.md. Every data query, every model definition, and every migration the AI generates: depends on knowing which paradigm is in use.

Data Modeling: Normalized Tables vs Denormalized Documents

SQL data modeling rule: "Normalize to 3NF. One table per entity. Foreign keys for relationships. users table: id, name, email. orders table: id, user_id REFERENCES users(id), total, status. order_items table: id, order_id REFERENCES orders(id), product_id, quantity, price. Relationships: foreign keys with ON DELETE CASCADE or RESTRICT. Never embed related data in JSON columns when a proper relationship exists." The AI generates: separate tables for each entity, foreign key constraints, and normalized data that avoids duplication.

NoSQL data modeling rule: "Design by access pattern. Embed data that is always accessed together. orders collection: { id, user: { name, email }, items: [{ product: { name, price }, quantity }], total, status }. The user name and product names are denormalized (duplicated from source collections). Accept data duplication for read performance. Update denormalized data when the source changes (or accept staleness for read-heavy, write-rare data)." The AI generates: documents with embedded data, designed for the query pattern, not for normalization.

The modeling rule is: the most impactful database rule. SQL AI with no rule: may create a flat orders table with user_name, user_email columns (denormalized, violates 3NF). NoSQL AI with no rule: may create separate users and orders collections requiring two queries and application-level joining (relational thinking applied to a document database). The modeling rule tells the AI: which paradigm's design principles to follow. One rule shapes: every table or collection the AI creates.

• SQL: normalize to 3NF, one table per entity, foreign keys for relationships
• NoSQL: design by access pattern, embed co-accessed data, accept duplication
• SQL: never embed related data in JSON when a proper relationship exists
• NoSQL: never create separate collections for always-co-accessed data (embed instead)
• The modeling rule shapes: every table/collection the AI creates in the project

Query Patterns: JOINs vs Embedded Reads

SQL query rules: "Use JOINs for related data: SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id. Use WHERE for filtering, ORDER BY for sorting, LIMIT for pagination. Use CTEs (WITH clause) for complex multi-step queries. Use indexes: create indexes on columns in WHERE, JOIN, and ORDER BY clauses. ORM: Drizzle db.select().from(orders).leftJoin(users, eq(orders.userId, users.id)). Never load all records and filter in application code — always filter in the query."

NoSQL query rules: "Read the document (one query returns embedded data). const order = await db.collection('orders').doc(orderId).get(). The order document contains: user info (embedded), items (embedded), total, status. No JOIN needed. For cross-document queries: query by indexed field. await db.collection('orders').where('userId', '==', userId).orderBy('createdAt', 'desc').limit(20). Firestore requires: an index for every compound query. Create indexes in firestore.indexes.json."

The query rule prevents: the AI generating JOIN syntax for Firestore (JOINs do not exist in document databases), generating multiple sequential queries for Postgres where a JOIN would work (inefficient N+1 pattern), or filtering in application code when the database can filter (always filter at the database level for both SQL and NoSQL). The query pattern is: paradigm-determined. SQL queries look fundamentally different from NoSQL queries for the same data.

• SQL: JOINs for related data, CTEs for complex queries, indexes for WHERE/JOIN/ORDER BY
• NoSQL: read one document (embedded data), query by indexed field, no JOINs
• SQL: Drizzle db.select().from().leftJoin() — relational query builder
• NoSQL: db.collection().doc().get() or .where().orderBy().limit() — document queries
• Both: never filter in application code when the database can filter

Consistency: ACID Transactions vs Eventual Consistency

SQL consistency rule: "Use transactions for multi-table operations. await db.transaction(async (tx) => { await tx.insert(orders).values(order); await tx.insert(orderItems).values(items); }). If any step fails: the entire transaction rolls back. ACID: Atomicity (all or nothing), Consistency (constraints enforced), Isolation (concurrent transactions do not interfere), Durability (committed data survives crashes). Use transactions for: order creation, payment processing, inventory updates — anything where partial completion is worse than complete failure."

NoSQL consistency rule: "Accept eventual consistency for reads (data may be slightly stale — milliseconds to seconds). Use transactions sparingly: Firestore supports transactions within a single document or across a small number of documents (not across collections efficiently). Design for eventual consistency: after writing an order, the inventory count may take a moment to update. For strong consistency on critical reads: Firestore read-after-write on the same document is consistent. Cross-document: design the data model so that consistency-critical data is in one document."

The consistency rule matters because: the AI trained on SQL may assume ACID guarantees in NoSQL (writing to two Firestore collections without considering that one write may fail while the other succeeds). The AI trained on NoSQL may not use transactions in SQL (missing the opportunity for atomic multi-table operations). The rule tells the AI: what consistency guarantees the database provides and how to use them (or work around their absence).

• SQL: ACID transactions for multi-table operations. All or nothing, always consistent
• NoSQL: eventual consistency default. Transactions limited to small scope (one document or few)
• SQL rule: 'Use transactions for orders, payments, inventory — partial completion is worse than failure'
• NoSQL rule: 'Design for eventual consistency. Critical consistency: same-document reads'
• AI error: assuming ACID in NoSQL (no multi-collection transaction) or skipping transactions in SQL

Rule Templates for Each Paradigm

SQL CLAUDE.md template: '# Database (PostgreSQL with Drizzle ORM). Normalize to 3NF. Foreign keys for all relationships. Drizzle: db.select().from().where().leftJoin(). Transactions: db.transaction() for multi-table operations. Indexes: on WHERE, JOIN, ORDER BY columns. Migrations: drizzle-kit generate. Parameterized queries only (Drizzle handles this). Never: embed related data in JSON columns, filter in application code, skip transactions for multi-table writes.'

NoSQL CLAUDE.md template: '# Database (Firestore). Design by access pattern. Embed co-accessed data in one document. Collections: orders (with embedded user + items), users (profile data), products (catalog). Queries: db.collection().where().orderBy().limit(). Indexes: firestore.indexes.json for compound queries. Transactions: Firestore.runTransaction() for same-document atomic updates only. Eventual consistency: accepted for cross-document reads. Never: design like SQL (separate collections for always-co-accessed data), use JOINs (do not exist), expect ACID across collections.'

The templates show: completely different content for the same project concern (data access). The SQL template: references normalization, foreign keys, JOINs, and ACID. The NoSQL template: references access patterns, embedding, document queries, and eventual consistency. Zero overlap in the specific rules. The paradigm determines: every rule about data. Copy the template matching your database and customize the specifics (table/collection names, ORM, query patterns).

Paradigm Rule Summary

Summary of AI rules for SQL vs NoSQL databases.

• Modeling: SQL = 3NF normalized, foreign keys. NoSQL = access-pattern-driven, embedded data
• Queries: SQL = JOINs, CTEs, complex WHERE. NoSQL = document reads, indexed field queries, no JOINs
• Relationships: SQL = foreign keys, ON DELETE CASCADE. NoSQL = embedded data, denormalized copies
• Consistency: SQL = ACID transactions. NoSQL = eventual consistency, limited transactions
• Indexes: SQL = B-tree on query columns. NoSQL = compound indexes for query patterns
• ORM: SQL = Drizzle/Prisma query builder. NoSQL = Firestore SDK / Mongoose document methods
• Templates: zero content overlap — the paradigm determines every data access rule
• Database rule = first 50 words of CLAUDE.md: every query depends on knowing the paradigm