Database Schema Design

Plan your content structure before you build. Includes ready-made schema patterns for blogs, portfolios, team directories, and product catalogs with reviews.

Good schema design makes your content easy to manage and your website easy to build. This guide covers common patterns.

Schema Basics

Tables & Fields

Think of tables as spreadsheets:

  • Table = A type of content (e.g., "Blog Posts")
  • Field = A column (e.g., "Title", "Date")
  • Entry = A row (e.g., one blog post)
Relationships

Tables can link to each other:

  • One-to-many: One author has many posts
  • Many-to-many: Posts can have multiple tags, tags can have multiple posts

Common Schemas

Blog

Tables needed:

TableFields
Poststitle, slug, content, excerpt, date, featured_image, author (relation), category (relation), published
Categoriesname, slug, description
Authorsname, bio, photo, twitter

Tell the AI:

"Create a blog schema with posts, categories, and authors. Posts belong to one category and one author."

Portfolio

Tables needed:

TableFields
Projectstitle, slug, description, client, date, images, link, featured
Skillsname, icon, category

Tell the AI:

"Create a portfolio schema with projects and skills. Projects should have multiple images and a link to the live site."

Team Directory

Tables needed:

TableFields
Membersname, role, department (relation), bio, photo, email, linkedin
Departmentsname, description

Tell the AI:

"Create a team directory with members and departments. Each member belongs to one department."

Product Catalog

Tables needed:

TableFields
Productsname, slug, description, price, images, category (relation), in_stock, featured
Categoriesname, slug, description, image
Reviewsproduct (relation), author, rating, comment, date

Tell the AI:

"Create a product catalog with products, categories, and reviews. Products can have multiple images and belong to one category."

Best Practices

Always Use Slugs

Add a slug field for URL-friendly identifiers:

  • "My First Blog Post" → my-first-blog-post
  • Used in URLs: /blog/my-first-blog-post

"Add a slug field to the posts table that auto-generates from the title"

Add Created/Updated Dates

Track when content was created and modified:

  • created_at: When the entry was created
  • updated_at: When it was last modified

Useful for sorting and auditing.

Use Booleans for Status

Instead of text statuses, use boolean fields:

  • published: Is this content live?
  • featured: Should this be highlighted?

Makes filtering simpler: "Show all where published is true"

Plan for Relationships

Think about how content connects:

  • Does content belong to categories?
  • Does content have authors?
  • Can items have multiple tags?

Set up relationships early—they're harder to add later.

Next Steps