How to structure the tables of a very simple blog in MySQL?

I want to add a very simple blog feature on one of my existing LAMP sites. It would be tied to a user's existing profile, and they would be able to simply input a title and a body for each post in their blog, and the date would be automatically set upon submission. They would be allowed to edit and delete any blog post and title at any time. The blog would be displayed from most recent to oldest, perhaps 20 posts to a page, with proper pagination above that. Other users would be able to leave comments on each post, which the blog owner would be allowed to delete, but not pre-moderate. That's basically it. Like I said, very simple.

How should I structure the MySQL tables for this?

I'm assuming that since there will be blog posts and comments, I would need a separate table for each, is that correct? But then what columns would I need in each table, what data structures should I use, and how should I link the two tables together (e.g. any foreign keys)?

I could not find any tutorials for something like this, and what I'm looking to do is really offer my users the simplest version of a blog possible. No tags, no moderation, no images, no fancy formatting, etc. Just a simple diary-type, pure-text blog with commenting by other users.

Answers


I'd say you need the following tables

Posts
  PostID (identity)
  PostTitle (varchar)
  PostDate (datetime)
  Deleted (int)
  OwnerID (int FK to Users)

PostDetails
  PostDetailID (identity)
  PostID (FK to Posts)
  Sequence (int) -> for long posts you order by this
  PostText (text)

Comments
  CommentID (identity)
  Comment (text)
  CommenterID (int FK to Users)
  CommentDate (datetime)
  Deleted (int)

Users
  UserID (identity)
  UserNAme (varchar)
  UserEmail (varchar)
  CreatedDate (datetime)
  Active (int)

All datetime fields default to the current time, all identity fields are PK The sequence field in post details is there in case you don't use the text type and go with varchar so you can split a post over several records.

Other than this, I'd look at any open source blogging system and see what they did and subtract what I don't need.

Hope that helps


Need Your Help