Database Design & Management

Instructions

1. •

   Schema design

   • •Identify entities and relationships
   • •Normalize data where needed
   • •Define primary and foreign keys
2. •

   Tables creation

   • •Use clear and meaningful table names
   • •Define proper data types
   • •Add constraints (NOT NULL, UNIQUE)
3. •

   Migrations

   • •Create versioned migrations
   • •Support rollback and re-run
   • •Keep migrations small and focused
4. •

   Indexes & performance

   • •Add indexes on frequently queried columns
   • •Avoid over-indexing
   • •Optimize for read/write balance

Best Practices

• •Follow consistent naming conventions
• •Keep schema simple and scalable
• •Always use migrations (no manual DB changes)
• •Document schema changes
• •Test migrations before production

Example Structure

-- users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  password TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);