I would like to know if this is a good database for a blog:

Database Diagram

This is a data model for a blog system that has the following characteristics:

  • Users can register
  • Users can have different roles, eg. author, administrator, commentator
  • Blog posts can be established with eg. headline, content, picture
  • Blog posts can belong to a number of categories
  • Blog posts can be marked with a number of tags
  • Blog posts can have different states, eg. drafts, published, archived, hidden

The data model (the design) has to include:

  • Entities with specification of attributes and keys
  • Relations with specification of cardinality and participation

This is for a school project, I am a beginner.


2 Answers 2


I have a few observations and suggestions:

Do not create a DATE table when all you need is an element (column) for the date of the POST. Generally speaking, a table with nothing but a meaningless ID and one other column is a good indication that you need to look hard at whether all you really need is the non-key column.

Consider CATEGORY, this also has just its ID and the category description. Is this a good candidate for an independent table? There are two things to think about:

  1. Are there likely to be other attributes that depend on the category? In other words, should category be normalized out of posts?
  2. Is it possible that categories could exist that have zero posts? In other words, is the category list something that needs to be managed by an administrator or someone other than the person who creates posts?

On another subject: Your model indicates that a POST can be written by many USERs. I'm not sure that this is really possible in your system, since you don't say anything about collaborative authorship. However, one thing that is almost certainly a problem is that each user can only ever author one post. I'm sure that's not what you meant.

To fix this you either need to move the foreign key in the relationship between USER and POST to the post side or you need to create an intersection entity if posts can have multiple authors.

Similarly... Your business rules state that posts can have multiple CATEGORY and TAG assignments, however, your model doesn't reflect this. In your model each post can have exactly one of each of these.

To fix this you need to implement a many-to-many relationship for each of tags and categories. This will result in an intersection table in each case.

Lastly: Why is some of your user information segregated into another table with a many-to-many relationship to the main user table? There may be good reasons to keep password information in a separate table from other user information, but I don't see any reason stated in your business rules. Also, it isn't likely that password will be many-to-many with your user record.

  • Category part is not clear to me.suppose Category can exists without post or not exists.Can you explain in both situation what should be the table and their relations
    – KumarHarsh
    Commented May 23, 2017 at 6:12
  • @KumarHarsh - If you have a pre-set list of categories, for example maintained by an administrator, then you must have a separate CATEGORY table. If the authors create the categories as they go and there is no control on categories then you could get away without a separate table, although you might still choose to create one anyway to have a normalized set of categories from which you could make suggestions.
    – Joel Brown
    Commented May 23, 2017 at 12:49

As a newbie, I suggest you don`t include much entity into your system.

  • Try just use the necessary entity (post、comment、category and so on) to finish the initial project.

  • Then you can take some complex relationship into consider and extend your origin system.

You can get better design while double check your design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.