Blog 5 min read

The Heart of a PHP Blog A Beginner's Guide to the Database Schema

By Copernicus August 12, 2025

Hello everyone! My name is Copernicus. I'm just a student who loves learning how things work. I've spent a lot of my free time teaching myself different technologies like Node.js, React, Python, and of course, PHP. It's a passion of mine, and I share some of my projects over on my GitHub.

When I decided to build my own blog from scratch using PHP, I quickly realized that the most important part isn't the flashy design—it's the database. The database is the skeleton of the entire project. If you design it well, everything else becomes so much easier to build.

I wanted to write this tutorial to share my thought process for creating the database schema for my blog. I hope it can help other students and learners out there who are just getting started.

Our Database Blueprint: The posts Table

Before writing a single line of PHP, I had to plan out my posts table. This one table is the heart of the entire blog. Every article, with its title, content, and category, lives here.

Here is the exact CREATE TABLE command I used to build it. It might look a little intimidating at first, but let's break it down column by column.

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(255) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT DEFAULT NULL,
    category VARCHAR(100) NOT NULL,
    author_name VARCHAR(255) DEFAULT 'Copernicus',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Every single line in that command has a specific purpose. Let's go through the "why" for each one.

Breaking It Down: Why Each Column Matters

  • id INT AUTO_INCREMENT PRIMARY KEY This is the most important column. It gives every single post a unique number (1, 2, 3, and so on). Because it's a PRIMARY KEY, the database can use this id to find a post incredibly quickly. When my code needs to edit or delete a specific post, it always uses this id. The AUTO_INCREMENT part means the database handles creating the number for me automatically, so I never have to worry about it.

  • slug VARCHAR(255) NOT NULL UNIQUE A "slug" is the part of the URL that identifies the page. For example, in mysite.com/blog/my-first-article, the slug is my-first-article. It's a human readable and SEO friendly way to make clean URLs. I made this column NOT NULL (it must have a value) and UNIQUE (no two posts can have the same slug), because every post needs its own unique address. My create_post.php file has a function to generate this automatically from the title, which is a huge help.

  • title VARCHAR(255) NOT NULL This is straightforward it's just the title of the blog post. A post can't exist without a title, so I made it NOT NULL.

  • content TEXT NOT NULL This is where the actual article lives. I chose the TEXT data type because it can hold a lot of information, much more than a VARCHAR. All the formatted text, images, and code from my TinyMCE editor gets stored here as HTML.

  • excerpt TEXT DEFAULT NULL The excerpt is a short summary or "teaser" for the article. On my main blog page, I show the title and this excerpt to give readers a preview. I set its default value to NULL because I don't always write an excerpt. This makes it optional, which adds a nice bit of flexibility.

  • category VARCHAR(100) NOT NULL This column helps me organize my posts. In my admin form, I have a dropdown menu to choose a category like "Finance" or "Travel". Storing this information allows me to easily create pages that filter posts by a specific topic. For a small blog, this is perfect. If the site grew much larger, I might create a separate categories table for more power.

  • author_name VARCHAR(255) DEFAULT 'Copernicus' Since this is my personal blog, I just made my name the default author. But I included a field in my form to change it, just in case I ever have a guest writer in the future. It's always good to think a little bit ahead!

  • created_at and updated_at These two columns are fantastic for automation. created_at gets a timestamp the moment a post is first saved. updated_at automatically updates its timestamp every time I save a change to the post. This is great for sorting posts by date (which I do in manage_posts.php) and for showing readers when an article was last updated.

And that's it! This simple table structure is the foundation for everything. Every field in my "Create Post" form maps directly to one of these columns, and every piece of information on the final blog page is pulled from here.

Thinking carefully about your database schema at the beginning of a project will save you countless headaches later on. I hope this breakdown was helpful! Happy coding good luck you all.

you might also like...