Users group table schema

I am creating a system where users can join and create groups. Within these groups, members can开发者_如何学C create topics and respond to topics already created, so I'd like to know your opinion on which of the following methods is best: Create two tables, groups_posts and group_topics:

--group_topics
id PK
group_id int FK
user_id int FK
title varchar(50)
content varchar(500)
status int

--group_posts
id PK
topic_id int FK
user_id int FK
content varchar(500)
status int

or create a unique table, group_tposts:

--group_tposts
id PK
group_id int FK
user_id int FK
is_topic boolean
title varchar(50)
content varchar(500)
status int

---

Based on your description, seems to me that a bit more may be needed. Maybe something something like:

• One group can have many users.
• One user can belong to many groups.
• Within a group users create topics (topic = subject; theme; a category or general area of interest.)
• Within a topic users create new posts or reply to previous posts.

Note: add the content field to the Post.

---

It really depends:

Case for the combined table
Given the fact that the boolean only takes one byte, the combined table is not a bad idea.
Do note however that creating an index on the field is_topic will not work because of its low cardinality.

MySQL (or any other database) will refuse to use an index on that field, but do a table scan instead.

Case for separate tables
If you plan use posts separate from topics often, I'd suggest separate tables.
Because the selects on those will be faster, MySQL does not have to do a full table scan to separate the posts from the topics.

---

I think two tables is better...

you can see how to normalize databe system on wikipedia http://en.wikipedia.org/wiki/Database_normalization