need some advice on my database design

Question

Hi all,

I am using cakePHP 1.26.
I am going to build a very simple forum on localhost.
Here is the conceptual design for the Database:

Tables:
User {user_id, name, date}
Topic {post_id, title, content, date}
Reply {post_id, content, date}
Quote {quote_post_id, post_type, post_id}

A User may have many Topic
A User may have many Reply
A Topic may have many Quote
A Reply may have many Quote
(Both Topic and Reply may be quoted once or many times)

Please help and advise.

Answer 1

User {id, name, logname, password}

Topic {id, title, content, date, user_id}

Reply {id, content, date, topic_id, user_id}

I'm not sure about Quote... I do not know why that does not fit under Reply.

Answer 2

Hello kwokwai.

You definitly want to introduce a field named id, as it is somewhat redundant to name a field user_id when it is the id field in table users. In addition to that cakephp treats some fields as automagic. Automagic means that cakephp can identify foreign-keys without you - the developer- having to specify them explicitly. But in order to have cakephp help you with automagic you have to stick to a few conventions. One of these conventions, which once learned are quite handy, is that foreign keys are automagically detected, once you name them YOUR_MODEL_NAME_IN_SINGULAR_HERE_id; e.g.: An author hasMany posts, a post belongsTo an author (*). In this case you can store the foreign key as autor_id in the table posts. Later when you use the cake bake script or scaffolding feature, you will come to understand the power of convention!

Another thing I noticed in your post is, that it could be you are going to miss that in cakephp relationships are established bi-directionally. This means you don't only work with a single hasMany definition, but at the same time in the other direction with a belongsTo definition.

Therefore I suggest you rephrase your statements in order to implement later cooler.

Kind regards, Benjamin.

Answer 3

I'm going to try taking thelost's answer in a different direction:

User {id, name, logname, password}

Post {id, title, content, date, parent_post_id, user_id}

Quote {post_id, quoted_post_id}

In this model, a post is a topic if parent_post_id is NULL. Replies will have parent_post_id set to the id of their topic. (Or you could allow a tree structure of replies.)

Warning: My experience is in normal SQL. I don't know how things work in cakephp, so someone else will have to help you with that.