15

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
ProgrammerGirl
  • 3,157
  • 7
  • 45
  • 82
  • Looks like Homework Tell what have you tried and implemented so far? – djadmin Sep 10 '12 at 15:57
  • 4
    @djadmin: Look at my history on Stack Overflow. I'm certainly no student. I haven't tried implementing anything yet because this is for a production site. I don't do "trial and error" on live sites, I first figure out the best way to implement something, and then I implement that. That's what I'm here trying to figure out. – ProgrammerGirl Sep 10 '12 at 16:41
  • Look at how any extant blogging engine does it. There are plenty of examples to look at. – ceejayoz Feb 02 '14 at 19:04

1 Answers1

34

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

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Carlos Grappa
  • 2,351
  • 15
  • 18
  • Interesting, thanks. Can you please explain why you suggested creating a separate "PostDetails" table rather than combining its columns with the "Posts" table? – ProgrammerGirl Sep 10 '12 at 16:42
  • 2
    Two reasons: The first is decoupling data to make a master/detail relationship (since this way you don't need to bring your maybe very long post when you just want the titles), and the second is the fact that you may end up going beyond the field capacity (maybe not an issue with a text field, but if you go with varchar, which i'd do, means you have a fixed maximun lenght per row). All in all it's just a matter of preference i like to separate information as much as possible – Carlos Grappa Sep 10 '12 at 21:09
  • Could you explain the usage of the Sequence (int) column you have used in the PostDetails table in your answer? What is the Sequence column used for? – bobble14988 Sep 18 '12 at 22:23
  • Suppose your database design the has PostText field as varchar with its max value (eg: 4000). If you want a post longer than 4000 characters, you need 2 entries in the table. Take a 6500 char post, the first 4000 go in the first record, with sequence number 1, and the last 2500 records go in the second field with sequence number 2, so when you query the table you order by Sequence ascending and you get all the rows that compose a post, and you don't have to rely on a text field, that at least in mssql is going away in the future. Also text field are not stored with the rest of the table data – Carlos Grappa Sep 19 '12 at 13:25
  • 11
    Shouldn't the Comments table have an `int FK` to the Posts table? Otherwise how do you reference which Comments are for which Posts? – rybo111 Aug 04 '13 at 17:02
  • Isn't It Good idea to add `longtext` to `PostDetails > PostText`? – Eugine Joseph Sep 21 '16 at 09:08
  • @rybo111 A post has multiple comments, so you create a join table – Muizz Mahdy Jan 29 '19 at 16:36