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

 

 

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.

 

 

 

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.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• Very Simple CMS In PHP MYSQL – Code Boxx