How to design a database design for forums?

Question

I am designing a forum application in php i want to know How to design a database design for forums?

Answer 1

I'll give you a basic table outline so that you can go through the process of designing and implementing a functional forum database on your own.

Basic table outline

I am omitting several columns that you would normally find in a forum database for simplicity. This design assumes only one forum area, eg. "Lounge". To support multiple thread categories, you would have a Categories table and a ThreadCategories table to map categories to threads. But focus on getting the posting and listing of threads working first.

Users table

Stores user information and access details.

UserID       int (PK)
Username     nvarchar(256)
PasswordHash varchar(256)

Threads table

Stores threads, each belonging to the user who created it.

ThreadID     int (PK)
UserID       int, related to Users table - (user who started the thread)
Title        nvarchar(512)
Date         datetime, when a thread was created

Posts table

Stores users' posts, each belonging to a thread.

PostID       int (PK)
ThreadID     int, related to Threads table
UserID       int, related to Users table to indicate the poster
Date         datetime, when post was made
Title        nvarchar(512) - post title (optional)
Body         ntext - the actual body of a post

Selecting all Posts for a Thread

SELECT
    p.PostID, p.Title, p.Body,
    u.Username
FROM Posts p
INNER JOIN Users u
    ON u.UserID = p.UserID
WHERE p.ThreadID = @threadID
ORDER BY p.Date

---

PK = Primary Key, a unique row identifier.