PHPDeveloper

Design Pattern: Blog ERD

by Jarrod posted 5 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.

erd.png
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 http://www.msnetframework.com/#designpatterns.php

  • 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 http://www.oratc.com

  • 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! www.lucidchart.com/pages/er-diagram-tool

  • Donald Duck
    Rammehar 2014-12-09 18:56:50

    Hi I am Rammehar Sharma, From India I am a beginner and work with php mysql. Blog Database design looking very good. But due to I say I am a beginner So I want to know about some fields like what is the difference between blog_auther and blog_user table can a blog user become an auther?

  • Donald Duck
    Rammehar 2014-12-16 22:53:53

    how many times a user comment on a spacific post.

  • Donald Duck
    solomonawulonu 2015-04-14 04:13:15

    kgsd fkslskdls

  • Donald Duck
    hello 2015-08-03 03:01:38

    test

  • Donald Duck
    none just test 2015-08-03 03:02:14

    no just test yaar

  • Donald Duck
    Will 2015-11-19 08:40:14

  • Donald Duck
    Shalin 2016-05-09 02:46:06

    You can find more entity relationship diagram templates in createlydiagram community. There are 1000s of diagram templates and examples in the community to be used freely.

  • Donald Duck
    Jamie 2016-09-07 16:56:39

    Why is the Author a non identifying relationship? Surely you want every Post to have an Author?

  • Donald Duck
    Aliyan William 2017-05-12 11:11:46

    Godaddy provides lots of working of services like they sell domains names, Hosting, SSL and so on tools provide in global. So many companies’ offers website and design services in over all nations and worldwide, so many register company in worldwide this one company i will tell you Logo Design Company in UAE. you can also take any kind of services working and website design logo design and so on. Lots of company offer this services but I will told you this company they provide reasonable price of working and also give priority own customers.

Type Your Comment