Optimizing Discussion Forum Schemas: Two Design Approaches Compared

Scenario

A simple discussion‑board system needs three core entities: users, user groups, and group discussion posts.

Solution 1 – Four‑Table Design

user table: id, nick_name, password, email, status, sexuality, msn, sign, birthday, hobby, location, description

groups table: id, gmt_create, gmt_modified, name, status, description

user_group relationship table: user_id, group_id, user_type, gmt_create, gmt_modified, status

group_message table: id, gmt_create, gmt_modified, group_id, user_id, subject, content

Solution 2 – Six‑Table Design

user table: id, nick_name, password, email, status

user_profile table (one‑to‑one with user): user_id, sexuality, msn, sign, birthday, hobby, location, description

groups table: same as in Solution 1

user_group table: same as in Solution 1

group_message table: id, gmt_create, gmt_modified, group_id, user_id, subject, author

group_message_content table (one‑to‑one with group_message): group_msg_id, content

Key Differences

Solution 2 adds an author column to group_message to store the poster's nickname, and splits the original user and group_message tables into separate one‑to‑one tables for profile and content respectively.

Performance Considerations

The most frequently accessed page is the post‑list, which only needs fields from group_message (id, subject) and the user nickname. With Solution 1 the query must join the large group_message table that also contains a big content column, causing unnecessary I/O:

SELECT t.id, t.subject, u.id, u.nick_name
FROM (
  SELECT id, user_id, subject
  FROM group_message
  WHERE group_id = ?
  ORDER BY gmt_modified DESC LIMIT 20
) t, user u
WHERE t.user_id = u.id

Solution 2 simplifies the query because the author nickname is stored directly in group_message and the large content field resides in a separate table:

SELECT t.id, t.subject, t.user_id, t.author
FROM group_message
WHERE group_id = ?
ORDER BY gmt_modified DESC LIMIT 20

Additionally, separating frequently accessed user attributes into a smaller table reduces the amount of data scanned for common queries, improving overall performance despite the extra join for the one‑to‑one tables, whose impact is minimal.