Help with logic setting up my database tables

Im working on my first frameworked website (using codeigniter) and having trouble coming up with the best way to set up my database tables. This website is essentially function like a stripped down reddit and hackernews. Users will register, create a thread that has 2 possible cateogories and has nested comments. So far Ive come up with this setup:

Users

id name password creationdate shortbio

Posts

id title content category creationdate

comments

id parent id content creationdate

Now Im not sure how to go from here. Would there be a better way to set this up? Im having the most trouble visualizing 开发者_运维问答how the usernames link up to the posts they make and where the comments come into play. Any help or direction is appreciated!!

---

The first step you need to take is to figure out the relationships. An ERD (Entity Relationship Diagram) helps greatly in this. You can also use a tool like MySQL Workbench to draw how the tables relate to eachother. It's very simple, it just shows you the relationships between your entities. I'll list out the most obvious ones here so you know how to start

1. Comments belong to Post : This means that the comments table needs a post_id column to store the ID of the post that is the OWNER of this comment.

2. Posts belong to User : Just like earlier, a user_id column in needed in the posts table to designate ownership.

3. Post belongs to Category : You've correctly added a category column to designate ownership.

Once you have the relationships mapped out, you can use codeigniter to write up your models corresponding to these primary entities.

---

It'll be useful to add:

• status fields (tinyint) to tables, to be able to disable/enable post, users, comments.
• emails for users
• shortbio is notsogood idea, better to store bio pieces separately (date of birth, last name, etc), so it'll be more flexible
• for comments add post_id, user_id fields - id of parent post
• extract cats in separate table and link'em by cat_id (may be you'll end with more than 2 cats)

Users

id name status email password creationdate dob

Posts

id title status cat_id content category creationdate

comments

id post_id user_id parent_id status content creationdate

Categories

cat_id title status

More options to add and improve will become obvious during further development)