We available for hire! — Need help with a web or mobile project?
From yensdesign we develop technology projects based on web apps, mobile apps, consulting and databases. We are young and hard workers, passionate about innovation and focused in new technologies.If you want to ask for a budget, we hare available for hire!.
How to make a brilliant mysql forum database from scratch
October 3rd, 2008
Hi folks! In this tutorial We will learn how to create a database for a complete and professional forums system. I want to present you an awasome utility for designing and creating databases: MySQL Workbench.
MySQL Workbench is a visual database design tool that is developed by MySQL. It is Free, Open Source under GPL and easy to use for design and make professional databases. You can download MySQL Workbench in the official downloads section.
Note:You don’t need to register on the official website, just click “Pick a Mirror” and then one more click at “No thanks, just take me to the downloads!” to avoid registration process.
So now, We know what We want: a brilliant database for our forums and what We will use: MySQL Workbench. It’s time to dive into the code guys but first of all take a look at the final result:
Isn’t easy? Yeah It is easy, so Lets Go! If you are so impatient, you can download the sources here.
Step 1: Thinking about forums flow
As always, We must do a mind-map of our problem, in this case It is a brilliant database for our forums, so let’s think in this schematic list:
- Users: We need users in our forums, They will register and post in them
- Messages: Users posts messages, right?
- Polls: But They posts polls too with lots of options…
- Categories: All these messages and polls are organized in categories… We don’t want a chaotic forum, we want a brilliant forum, remember?
- Subcategories: Because our forums will be brilliants… but big too, We want to organize it a bit more in subcategories
We suppose that in our forums, users will register to create posts, maybe They want to attach a poll to It and select a category for the post. Other users will response to our post. So in a first approuch, We need to save all this data in tables: Users, Posts, Posts Polls, Categories, Subcategories.
Step 2: Explaining a little bit the schema
It’s impossible to explain detail by detail the schema of our database, but I will comment some interesting points in this one:
The table forumPosts is used to save all posts in all topics, including the main post in a topic (the first one). By using the same table and the column parentPost We can determine if an entry of the table is the first post of a topic: when parentPost is null or It is a reply of the main post: parentPost is NOT NULL (it is the id of the main post).
By the way the boolean column called isPoll of forumPosts table determines if our topic has or not a poll attached. Isn’t easy? We have in one table the information about:
- The first post of a topic
- So, we can identify the topic by this first one
- Replies of a topic
- If the topic have or not a poll
In the other hand, forumPollOptions is linked with the forumPosts table to allow us to create and know what poll is the parent of the options, and It is linked with the votes of the users forumPollsVotes too to count the votes in our polls and to identify the users that have voted.
And finally, as We said, we want categories and subcategories for our forums so they are called: forumCategories and forumSubcategories. They just are related to allow us to build the index of our forums with their categories on our future webpage. And remember that forumCategories is linked with forumPosts too to know what is the main category of a topic.
Step 3: Starting with MySQL Workbench
I suppose you have installed MySQL Workbench. If don’t, You can download it here but if You prefer to use another Software to follow this tutorial and design the database It’s ok, You only need to understand the schema of the datebase, not repeat mechanic method without understand the explanation.
Using MySQL Workbench is so simple and intuitive, let’s create our first table example based on the schema that I showed you at the begining of this tutorial: Users table.
Users table:
- id [ int ] PRIMARY KEY, autoincrement
- nick [ varchar(16) ]
- password [ varchar(128) ]
- email [ varchar(128) ]
- date [ timestamp ] default: CURRENT_TIMESTAMP
- karma [ float(9,2) ]
- ip [ varchar(20) ]
It’s important to have a previous mind-map and the complete schema of our database before using MySQL Workbench. You only need to think about what do you need in your system, draw the schema/concept on a paper, normalize it and then start coding / using MySQL Workbench.
Note: If yo need more info about collations or engine databases you can check it here and here.
First of all rename the database. Then to create the first table click on “Add Table”, select a name, a collation (in our case utf8 – utf8_general_ci), the engine InnoDB and type a comment for the table if you want it. Here you have a screen if you are lost:
The next tab: Columns will be for setting up the columns of our table, just click on it and start to fill all columns that we listed before:
As you can see, MySQL Workbench does It easy, We only neet to know what schema we need and then click by click We will finish our database to export to a .sql file.
Step 4: Building the rest of the database
I can explain one by one all tables of the database, but as you saw It’s so easy, the problem is build the initial schema of the database… and It only can be done with practise, more practise and a lot of reading about Entity Relationship Model and Database Normalization.
Note: In the rest of the tables you will see some data in the Forein Keys tab.
Step 5: Trying and downloading source files
As always I let you download the sources of the tutorial. Here you can download the source files here that gots the entire project that we create on this tutorial. Sorry for my english one more time, I hope you can understand my explanation and see you soon in the next tutorial guys.
Thank you!
Become IT expert with XK0-002 online course. We offer expert 310-065 resources including 646-205 live demo to help you learn useful web application even if you have no IT background.
Database, forum, mysql, Tutorials
Coding, Database, Tutorials, Web Design
Enjoy this post?
Your vote will help us to grow this website and write more entries like this one :)
We available for hire! — Need help with a web or mobile project?
From yensdesign we develop technology projects based on web apps, mobile apps, consulting and databases. We are young and hard workers, passionate about innovation and focused in new technologies. We try to help you, sharing our knowledge acquired on worked projects, while helping the community and showing our capabilities.If you want to ask for a budget, we hare available for hire! Don't doubt in get in touch with us!.
“stunning and smoth…”, now “brilliant…”. Men you got no grandma.
coño que no tienes abuela? si es bueno o malo lo diran los demas.
sorry, esos “foros”, bueno la db, tienen de brillante lo mismo que el arroz, el nombre.
Me parece un buen tutorial como punto de partida a la hora de programarte unos foros propios. Entiendo que no puedas poner absolutamente todo, pero estando los ficheros de ejemplo para descargar creo que es más que suficiente.
Buen curro.
[...] How to make a brilliant mysql forum database from scratch [...]
Thanks for this. Came across it on Planet MySQL and as a new user to Workbench I have been searching for tutorials. Great stuff.
@Landon & @Javier Thanks for all! I am happy to see that somebody can use the tutorial to learn about mysql and Workbench
Sorry, I disagree with the design of the tables. For one, we don’t expect thousands of categories or subcategories – even hundreds are stretching the likely reality of maybe 5 or 15 main categories or forums and perhaps a few subcategories. All categories can be contained in one table with the inclusion of one field named similarly to parentCategoryID.
However, we do want to prepare for thousands of topics and potentially hundreds of thousands of posts. A table should be provided for recording individual topics, their titles and other information, keeping the sifting of ‘first’ posts, post counts, and titles away from sifting through a table of a ton of records.
You are right Ben!
I wrote this tut thinking about a big content management system with more than forums. I adapt it for our forums and to teach people about normalization, Entity relationship and others (foreign keys, etc).
Thanks you for your comment mate!
“Sorry, I disagree with the design of the tables. For one, we don’t expect thousands of categories or subcategories [...]”
It doesn’t matter what you expect, you either have them or you don’t. A properly designed table doesn’t care ‘how many’ you have of anything. Either the relationship works or it doesn’t, no inbetweens.
Good point Benjie
Thanks for whipping up this tut. I am just starting to get handle on mysql dbs. I have implemented a couple for business use and they are ok, but there are many roads to Rome, it is just that some are better than others.
)
Ey David nice to see you here! I am glad to see that this tut is useful for you.
Next Monday there will be a new exciting tutorial about jQuery
Really fantastic yaar. You have done a great job!!!
Benjie, I don’t understand how your point contrasts what I’m saying. I’m not saying that, when creating a forum, you should only allow for a certain number of categories, but that you should optimize your SQL for the most expected usage.
contact database…
Interestingly, this was on CNN last week….
it’s really good.i wish to post it compex journal.
Why all popular net based forums like vBulletin, phpBB, myBB, fluxBB, Vanilla etc use a seprate table to store the threads and a common table to store all posts? Is there any advantage of doing this?
(I think, it would be very hard to maintain two table synchronised)
[quote =Ben Says: October 8th, 2008 at 5:54 am ]However, we do want to prepare for thousands of topics and potentially hundreds of thousands of posts. A table should be provided for recording individual topics, their titles and other information, keeping the sifting of ‘first’ posts, post counts, and titles away from sifting through a table of a ton of records.[/quote] I think this comment has some indirect relation with my comment above.
What if we need to store the title for all the posts (including replies)?. Also why do we need to save first_post, post_count etc in a separate table, as mySQL now it’s easy to find all such information by some sub-queries (having some group by clauses)?
I do agree with Ben an that Categories should be one table, wouldn’t be wasting space that way. There should be a table for Topics which should have a “first post” and “total posts” columns, and one for the posts.
I am here at a forum newcomer. Until I read and deal with the forum.
Let’s learn!
really helpful for those who would not know anything about databases.
Thanks guys
Mental Floss prevents Moral Decay.
Sorry if an old one – but very very funny.
I am considering making my own forum, due to the ones existing that are “free” online that are basically pre-made don’t offer the controls I would like on my team forum. Such as making my own backgrounds, colors, pictures and .gifs.
I am also looking for the potential of having a variety of admission controls such as basic user, registered user, team member access, managment links, and finally, founder only links.
I would like to be able to say who can or can not make links, who can or can not read (or even see links), who can delete or edit links, etc.
The problem is that our team is voluntary and a non profit team, we never charge for our services, so our websites are donated (servers) by me the founder. All of our equipment is donated by team members, and it is not like we are made of money, so we need to find a way to make and edit this forum ourselves. I am not a genious by any means so I am looking for a free generator to make a forum from scratch, control it, back it up to protect any chance of viruses or deletion from a disgruntled team member(which sadley happens). And it needs to be something that even someone with limited computer programing can learn and handle own my or with tutorials that I can learn from.
This site I came across while surfing the internet for this purpose. I am hoping your site may lead to the answers I need, our your members may lead me to the answers I need.
I know that I may be asking a lot, especially when its a “free” or “cheap” program I am looking for. But I hope someone will see that is it is not “for-profit”, it is to make a research team have a tool that may help generate research in our field (Paranormal Research), that will one day prove to be Beneficial to the educated communities or public communities.
So in brief, does your forum program offer what I need?
@Paulybadboy thanks for posting! This post is only a tutorial and an example of a database forum, is not a program by it self… But you can try Phpbb2 or Simple Machine Forums and edit their style to adapt to your purposes.
Also you can add more features to them by installing plugins.
Good luck!
Hi people…
Hello World
nice job!
Yeah badiya hai per ise aap pura forum database nahi keh sakte…per starters ke liye theek hai…
Dhanyawad…
How to solve the search problem? I mean will the search with ‘like’ operator not take a lot of time? What are the better ways of searching forums?
Just take a look at this http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
Say saying hi to you guys!
I was searching the web for relevant blogs and came across your blog. Happy to read interesting information on your blog. Thanks.
[...] View Tutorial No Comment var addthis_pub=”izwan00″; BOOKMARK This entry was posted on Saturday, June 6th, 2009 at 6:09 am and is filed under Php Tutorials. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. [...]
Thanks for information about Database Designing.If you want to know somthing more about database designing Visit:–http://www.sagarinfotech.com
Thanks for information about Database Designing.If you want to know somthing more about database designing Visit:–http://www.sagarinfotech.com
Thanks for the great information mate! keep it up.
nice share buddy..
Very good! Stuff like this always interests me. And now, off to create a demo forum.
Awesome, will try your db very soon for experience and setting up a starters forum !!!
[...] http://yensdesign.com/2008/10/making-mysql-forum-database-from-scratch/ [...]
But what if u want to make sub-topic’s from a sub-topic?
Make one Topic Table and add a Parent Column. Then you can create a n-subtopic Forum.
Hey guys,
Very well presented article, good job author.
If there is anyone looking to develop a forum system in ASP.NET 3.5 VB then please visit our contextual easy to understand tutorial from start to finish. Link Below
http://www.digitdotnet.co.uk/Developers.aspx
Regards
DigitDotNet
I recommend using DbSchema as a database management tool.