ERD Diagram For Blogging Website (With Database Structure)

Welcome to a sharing of an ERD of a blogging website. So you are planning to build your own blog or CMS? Or maybe you are just curious about what goes on behind one? Well, here’s a sharing of my “simple version”, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

BLOGGING WEBSITE ERD

As in the introduction, I have kept this as simple as possible. It should be pretty self-explanatory even at the first glance.

  • Users – The administrator, editors, and readers.
  • Posts – Pages and posts.
  • Comments – Comments on each post.
  • Categories – Topic categories.

 

FOR REFERENCE ONLY

Right, I can hear the expert master keyboard warriors banging on their keyboards – “This diagram is inaccurate”, “this is stupid”, “this is too simple”. By all means, this is only for reference.

  • If you want to run a blog shop, go ahead and add “products and orders”.
  • If you want to track user activity (for security purposes), go ahead and add “history”.
  • If a post can only have one category, go ahead and make the necessary changes.

The list can go on, but the point here is – You are creating your own system, you have to define your own rules.

 

 

TABLE STRUCTURE

Now that we are done with the overview, let us get into more details on the entities or tables.

 

PART 1) USERS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary key.
user_role VARCHAR Up to you to decide – “A”dmin, “E”ditor, “C”ustomer.
user_name VARCHAR The user’s full name.
user_email VARCHAR The user’s email. Unique to prevent duplicates.
user_password VARCHAR The user’s password.

This one should be self-explanatory. Even if this is a “personal blog”, it is still good to have a users table to keep the possibility of future expansion open.

 

PART 2) POSTS

COLUMN DATA TYPE NOTES
post_id INTEGER Primary key.
user_id INTEGER The user who wrote this post. Foreign key.
post_url VARCHAR The URL of this post.
post_title VARCHAR Title of the post.
post_summary VARCHAR A short summary of the post.
post_content VARCHAR The actual content itself.
post_date DATETIME Date when the post is published
post_modified DATETIME Last modified date.

The elephant in the room, every blog has content – Posts, articles, pages, tutorials, or whatever you call them. Feel free to add more fields as required, maybe a “version” so you can fall back.

 

 

PART 3) COMMENTS

COLUMN DATA TYPE NOTES
comment_id INTEGER Primary key.
post_id INTEGER This comment is for this post.
user_name VARCHAR Name of the person who made the comment.
user_id INTEGER Foreign key.
user_email VARCHAR Email of the person who made the comment.
comment_date DATETIME Posted timestamp.
comment_text VARCHAR The comment itself.

Comments are another self-explanatory table, but just a couple of notes:

  • Take note, we are using the User ID as the foreign key here for registered users.
  • But to keep the comments section open to the public, we also keep the name and email.
  • You decide if comments are open to registered users only, to the public, or none at all (remove this table entirely).
  • To allow “reply”, one way is to add a recursive “Parent ID” key to this table. But be careful with it – The nesting can get nasty.

 

PART 4) CATEGORIES

4A) POST CATEGORIES

COLUMN DATA TYPE NOTES
category_id INTEGER Primary key.
category_url VARCHAR URL of the category. Unique.
category_name VARCHAR Name of the category.
category_summary VARCHAR A short summary of the category.

This is kind of optional, but good to have nonetheless – The topic categories. For example, Tech Tips, Reviews, etc…

 

 

4B) POSTS TO CATEGORIES

COLUMN DATA TYPE NOTES
post_id INTEGER Composite primary key, foreign key.
category_id INTEGER Composite primary key, foreign key.

Finally, this one attaches categories to posts.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEAT SHEET

eCommerce Website ERD (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *