Menu
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.
295
sql server - Best Database for Blog Application (Relational - SQL vs NoSQL) - Stack Overflow. Stack Overflow for Teams – Start collaborating and sharing organizational knowledge.
To show the schema, we can use the DESC command. This gives the description about the table structure.
The first command you will need to use is the SELECT FROM MySQL statement that has the following syntax: SELECT * FROM table_name; This is a basic MySQL query which will tell the script to select all the records from the table_name table.
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
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
