Database Design Guide 2026

-- One-to-Many (User has many Posts)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  title VARCHAR(200),
  content TEXT
);

-- Many-to-Many (Students and Courses)
CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);

-- One-to-One (User and Profile)
CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER UNIQUE REFERENCES users(id),
  bio TEXT,
  avatar_url VARCHAR(255)
);

-- Create Index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Composite Index
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

-- Unique Index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- When to Index:
✅ Foreign keys
✅ Columns in WHERE clauses
✅ Columns in ORDER BY
✅ Columns in JOIN conditions

❌ Small tables
❌ Columns with low cardinality
❌ Frequently updated columns

-- ❌ Bad: SELECT *
SELECT * FROM users;

-- ✅ Good: Select only needed columns
SELECT id, name, email FROM users;

-- ❌ Bad: N+1 Query Problem
for (const user of users) {
  const posts = await db.query('SELECT * FROM posts WHERE user_id = ?', [user.id])
}

-- ✅ Good: JOIN or IN clause
SELECT users.*, posts.* 
FROM users 
LEFT JOIN posts ON users.id = posts.user_id;

-- Use EXPLAIN to analyze queries
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Pagination
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

-- Better: Cursor-based pagination
SELECT * FROM posts 
WHERE id < last_seen_id 
ORDER BY id DESC 
LIMIT 20;

// Schema Design
const userSchema = new Schema({
  name: String,
  email: { type: String, unique: true, index: true },
  posts: [{
    title: String,
    content: String,
    createdAt: { type: Date, default: Date.now }
  }]
})

// Embedded vs Referenced
// Embedded (1-to-few, data accessed together)
{
  _id: ObjectId("..."),
  name: "John",
  address: {
    street: "123 Main St",
    city: "NYC"
  }
}

// Referenced (1-to-many, independent access)
// User
{ _id: ObjectId("user1"), name: "John" }

// Posts
{ _id: ObjectId("post1"), userId: ObjectId("user1"), title: "..." }
{ _id: ObjectId("post2"), userId: ObjectId("user1"), title: "..." }

// Indexes
db.users.createIndex({ email: 1 })
db.posts.createIndex({ userId: 1, createdAt: -1 })