Geek Answers Handbook

User Group Table Schema

I am creating a system in which users can join and create groups. Within these groups, participants can create topics and respond to topics already created, so I would 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
+4
source share
3 answers

Based on your description, it seems to me that a little more might be needed. Maybe something like:

  • One group can have many users.
  • One user can belong to many groups.
  • In a group, users create topics (topic = topic, topic, category, or common area of ​​interest.)
  • Within the topic, users create new messages or reply to previous messages.

enter image description here

Note: Add a content field to Post .

+10
source

It really depends:

Case for combo table
Given the fact that only one byte takes a boolean, a combo table is a good idea.
However, note that creating an index in the is_topic field is_topic not work due to its low power . p>

MySQL (or any other database) refuses to use the index in this field, but scans the table instead.

Case for individual tables
If you often plan to use messages separately from topics, I would suggest separate tables.
Since selecting them will be faster, MySQL does not have to perform a full table scan to separate messages from topics.

+1
source

I think two tables are better ...

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

+1
source

More articles:


All Articles