Re: Designing a forum - general design logic question

[Rick James]

* Do not provide mysql logins for the users. Instead build a login/authentication mechanism using table(s) and PHP (or other front-end) code.

* Download any of several free forum software products and study the schema they use.

* You will need (at least) these tables:
-- Forum -- one row per forum (if you separate Modelling from Newbie, etc, like this forum)
-- Threads -- one row per thread
-- Messages -- one row per message
-- Users -- one row per message
Get the drift?? Think of what "entities" there are (user, message, thread, etc) and build a table around each.

* (Usually) each table has an AUTO_INCREMENT id as the PRIMARY KEY.

* Then think about "relationships". Most of what you need are one-to-many. One table would include the id into the other table(s). Use JOIN to, for example, show both the post content and username. JOINing Messages to Threads to get thread info, such as who started it.

* If you need any many-to-many relationships, that takes another table.

* Use TEXT or MEDIUMTEXT (not VARCHAR) for the 'content'.

* Use utf8 throughout, and be sure to initialize connections with "SET NAMES utf8" or equivalent.

* Use InnoDB.

* KISS (Keep It Simple). Don't build a full tree in a thread; just a simple list. Order the display by id. (More complex would be to allow branching within a thread. You may have noticed that this forum get a bit confused over such; it displays them in two different orders.)