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
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
|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
|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
|post_id||INTEGER||This comment is for this post.|
|user_name||VARCHAR||Name of the person who made the comment.|
|user_email||VARCHAR||Email of the person who made the comment.|
|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
|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
|post_id||INTEGER||Composite primary key, foreign key.|
|category_id||INTEGER||Composite primary key, foreign key.|
Finally, this one attaches categories to posts.
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
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
- Very Simple CMS In PHP MYSQL – Code Boxx
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!