Design a relational database schema for [describe your application — e.g., a project management tool, an e-commerce platform, a social media app]. Include all tables with columns, data types, primary keys, foreign keys, and indexes. Define the relationships (one-to-one, one-to-many, many-to-many with junction tables). Add appropriate constraints (NOT NULL, UNIQUE, CHECK, DEFAULT values). Include created_at and updated_at timestamps. Write the SQL CREATE TABLE statements and explain your normalization decisions.
#29Database
Complex Query Builder
Write an optimized SQL query that [describe what you need — e.g., "finds the top 10 customers by total order value in the last 90 days, including their most recent order date and number of orders, but excluding cancelled orders"]. Use proper JOINs, aggregations, subqueries or CTEs as needed. Explain the query execution plan, identify potential performance bottlenecks, and suggest indexes that would improve performance. Provide both the MySQL/PostgreSQL version and explain any syntax differences.
#30Database
Database Migration System
Create a database migration system for a Node.js application. Implement a migration runner that: executes migrations in order (using timestamp-prefixed filenames), tracks which migrations have been applied in a migrations table, supports both up (apply) and down (rollback) operations, runs migrations within transactions for safety, and provides CLI commands for "migrate up", "migrate down", "migrate status", and "create new migration". Include example migrations for creating a users table and adding an index.
#31Database
Query Performance Optimizer
My database query is slow (taking over 2 seconds). Here is the query: [paste query] and the table schema: [paste schema]. Help me optimize it. Walk through: how to read the EXPLAIN/EXPLAIN ANALYZE output, identify whether the issue is a full table scan, missing index, inefficient JOIN, or N+1 query pattern. Suggest specific indexes to create, query rewrites (CTEs, subquery-to-JOIN conversion, pagination with keyset instead of OFFSET), and database-level settings to tune. Show before and after with expected performance improvement.
#32Database
Data Seeding Script
Create a database seeding script that populates a development database with realistic test data. Generate: 50 users with varied names, emails, and registration dates across the last 6 months; 200 posts with realistic titles and lorem ipsum content distributed across users (some users have many posts, some have few — following a power law); 500 comments distributed across posts with threaded replies; and relevant tags and categories. Use a seeding library or faker for realistic data. Include a reset command that clears and re-seeds. Make the seed deterministic (same seed produces same data) for reproducible testing.