database design: best practice for designing a forum table [closed]

Closed. This question is opinion-based. It is not currently accepting answers. 开发者_C百科

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 2 years ago.

Improve this question

I wonder if there is any practice when designing a forum. I want to design a couple of forums. yes there are two different types of forum at least. one is for a selective members and another is open for any public members.

In the forum, the member can initiate a thread, then other members can respond to it.

So I have this rough idea for the structure of this type of forums.

I'd name the forum table as 'strings' table. this is the structure I can think of,

str_id        for the auto increment id
str_tilte     for title of the thread
str_content   for content/ text of the thread
str_follow    for you to choose whether you want to follow this thread or not
str_approved  for the admin to approve or reject the thread
parent_id*1     
mem_id        member id who initiates the thread or who responds to the op
cat_id*2
str_created   
str_updated

*1 eg. if the data injection is an op thread then the parent id is itself, if the injection is a response to the op, its parent id is the str_id of the op.

*2 this column is to store the category id of the current data injection. if the injection is a selective forum then it is 1, if the injection is public forum then it is 2.

not sure if I am on the best track, but please let me know if you have any better ideas.

thanks.

edit:

thanks for some reply and suggestions that using phpbb.

just had a look and downloaded phpbb. it is 101% sure that I won't go for it. it is a whole programme like Wordpress or any other PHP frameworks (like Zend). I have an independent framework running this website, so I don't want to run another programme/ framework to mess up the site's backend structure, etc.

if I the site is only a pure forum website, then maybe I would be considering phpbb. but the forum is just a sub feature of a holistic website in my current project.

---

Don't start with a table, start with a conceptual ER Diagram of your base entities, forums, threads, replies,members, keywords, categories etc. and express the relationships first.Then get into logical design mode and define the tables needed to implement the functionality. As suggested have a peek at the way phpBB tables are organized.

I like to do my first diagram on a printing whiteboard or on paper then move to a diagramming tool for logical design.

I think there's still some room for a 'better' forum, so good luck.

---

Greetings.

As i previously recommended in comments to your question.

I strongly recommend you to set up local installations of other open source database driven forums in your machine, in order for you to have a strong idea and base of what other developers did when they develop such forum applications, not just looking at the database tables but also to the code, organization and design of the forum. I think it proof it self very useful to open your eyes to possibilities for your own forum application

A few examples of database driven forums:

• phpBB
• Simple Machines Forum
• XMB Forum

I usually say the best way to learn is trial and error but let me also point out. Even this forum application your creating, its just for small and private use for your websites.

You should consider that your forum application one day may become popular and you might desire to turn it public like these previous ones mentioned, or you just want to add some new features.

In this case i suggest you plan your Code and Database Architecture very well, planning ahead for future upgrades.

Also you should take MikeAinOz advice very seriously in his answer and start by

Conceptual ER Diagram of your base entities, forums, threads, replies,members, keywords, categories etc. and express the relationships first.Then get into logical design mode and define the tables needed to implement the functionality

Thats the best answer to this kind of question i ever seen.

Regards & Good Luck.

---

My thoughts...

1. str_follow shouldn't be in this table. It should have its own table that links members to subscribed threads.

2. I'd recommend having parent_id null if its a top level thread, it makes more sense.