Design Pattern: Blog ERD

by Jarrod posted 2 years ago
Design Pattern: Blog ERD

With any new project, a solid design is a good place to start. So often have I seen databases designed on-the-fly only for data to be shuffled around to get that "link" table added. Or worse yet, tacking on extra columns like the classic: "image1, image2, image3...".

With a bit of planning when designing your database, you can create a nice, normalised DB that is scalable for future growth.

Well, today I'll provide you with a database schema for your blog. The download, above, includes the SQL and mySQL Workbench file used to create this schema, in-case you want to modify it, and the graphic shown below.

Did you know, with mySQL Workbench you can create an ERD diagram then generate your database?

A bit about this design

First off, this uses the InnoDB engine - which as of mySQL 5.5 is now the default engine. Why InnoDB? referential integrity of course! What's that!? In the words of Wikipedia:

Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).

This basically means that for, for example a comment, to exist the corresponding post must exist as well.

Most of the tables/fields should be self explanatory. The main table being blog_post. Some fields of interest are title_clean. This is an indexed column and is used to store your "clean" URL. If you're creating a blog you're very likely going to want to create clean URL's for SEO purposes. You'll find the suffix "clean" in other tables for the same reason.

Tip: You can create an ERD diagram (like the one below) with mySQL Workbench, then using what they call "reverse engineer" you can convert the diagram into your mySQL database! Kills two birds with one stone - documentation and the database.

Banner image is the name of your main article image. It's stored in the database so each article can have a named image rather than just "banner.jpg". Again, this is for SEO purposes and the same name should be used for your image alt tag.

The blog_related table is used to add "related" articles to a particular article. This is much the same as the blog_tag table. I should note that the design used to store the tags - that is, you add the tag and the post id it corresponds to - is one of 3 appropriate methods. I choose this one for the fact I simply preferred it over the other two design choices. If you don't know what I'm talking about, that's ok, just ignore this.

I'm happy to answer any question. Please leave them in the comment section below.

Comments for Design Pattern: Blog ERD

  • Donald Duck
    Adnan Ahmed 2012-01-23 15:11:18

    Hey, Great work bro!

  • Donald Duck
    Bassey 2012-11-29 09:58:19

    I am looking to deelop ERD for a micro blogging/bookmarkng site with the following assumptions - A reader can view all posts of all users. - Each user has a page that lists all of the links and descriptions that they have posted. - Any user can create an account and immediately post links. - Once registered, a user can edit their own profile that contains a short biography and their contact details. - Only registered users can post a reply to a link. - Replies to links can be shown immediately below the message they are in reply to or on a separate page. - All posts are to be time stamped with their creation date and time. - Both interfaces have the same functionality and display the same content but in a manner appropriate to each device. can you be of any help.

  • Donald Duck
    swetha k 2013-09-20 10:56:39

    Excellent Post. Also visit

  • Donald Duck
    Oracle DBA Jobs 2013-12-09 05:54:46

    HI i am Qadir Shaikh and i am provide a platform for the student to do their career in oracle.for more information visit at

  • Donald Duck
    Me 2014-04-07 01:25:05

    good job

  • Donald Duck
    Serviced Apartments Resident 2014-05-07 05:11:43

    Great stuff Jarro! Well explained and easy enough to understand for us, ur...mentally challenged!

  • Donald Duck
    macy dalby 2014-06-03 19:30:34

    Thanks this info was really helpful! I used a website called Lucidchart to create my own erd diagram and it was super easy to use. If you use diagrams often you should check it out!

Type Your Comment