What really goes into making a database? Should you design it in a certain way? Are there rules of what you should or should not do?

Look at a forum like Neoseeker, for example. Almost everything you see is the result of interaction with a database: forums, threads, posts, PMs, breadcrumb trails, preferences, and so on.

My goal for this thread is to discover a process of designing a database with all of you. To do this I've been considering designing a forum system DB because it seems to be the bond that ties all of us together. We all have some familiarity with what features Neoseeker offers, so it gives us a good base line from which to start.

The focus should be on what you want to learn, so before we start, I'd like to know: is this a useful exercise? What do you want to learn about database design?

[size=1][color=#666666]This message was edited by Artificer on Aug 23 2008.

I think this is great. We don't really have any database specific threads around here like this.

I think that we should start by showing the different types of databases out there, like MySQL, MsSQL, etc. Also some tools that you could use to administrate the databases in manually input info.

Then some site specific tables, such as members, forums, threads, posts, online_members, preferences, permissions.

Afterwards some smaller things, like favorite_forums.

Yeah, an introduction to the different Databases like MySQL, MySQL Lite, Oracle, and so on, sounds good.

Then we could continue with the datatypes, like the most common ones (int, varchar, and so on), to the "special" ones (bool, time, date, timedate, and so on).

Then the different tools to manage databases, like PhpMyAdmin or SQLLite Manager.

Then the specific tables and how they could work and interact with the pages.

What about before all of the stuff you (lord monkey and Vermillion) have mentioned? I know that the issues you've brought up are important, but...

...You have to start somewhere, and in most controlled scenarios it will not begin with making tables in MySQL, for example. First you have to know what data you're even interested in, right? Otherwise, how do you know you are designing your database appropriately?

From there, there are a few approaches you can take to get to your DB tables, but I can talk about those along the way. Mostly those extra steps involve organizing that data into a format that can be translated into an actual database implementation, and that's the point where you can start worrying about questions like "Which DBMS (Database Management System) should I choose?" and "What data types should I use?"

The point is that there's a lot out there in terms of DB design that tends to get overlooked, sometimes to a fault.

You mean like how to organize my tables on a database for my forum? Or like "What Data do I need to create a moderator"?

If by "organize my tables" you mean deciding on which columns go where, then it's a bit of both. On the one hand, you have to know what you want. (In some cases, you're not the person who decides what is wanted, so this is really important.) Once you know what you want, you have to figure out how to structure it into a database (if you even decide to use a database).

So let's say before we think about MySQL, before we even think about databases or tables, our first step is to gather the data we want to store. In this case we are the ones who define what we need, so it makes this step a little easier.

What is the bare minimum we would need to make a forum?

I can name some of the objects in our business domain* right off the bat: forums and threads. What data about forums and threads would we need? Would we need any other objects in our domain? (Remember, we're thinking of the bare minimum, and think of the forums in terms of objects, not tables.)

*By business domain I mean the part of the universe that we are concerned with. In this case, it's forums, and the objects surrounding forums. It would most likely not involve objects like cars, famous buildings, or house furniture.

So for bare minimum for a forum, you would need a unique id for each one, a title, maybe a description, and permissions, but thats going over board.

As for threads, they would get more complex. Again, a unique id, and a title, but also a bool value for if the thread is locked and/or sticky.

Furthermore, posts. A unique id, the actual message, the member posting the message, the IP address of the client posting the message for security issues, and the date of the message being posted.

Let's say that we don't care about description, permissions, locking threads, stickying threads, or IP address of the poster for this example. Those are good ideas though, so keep them in mind for later.

That leaves us with the following:

Forums which have IDs and titles.
Threads which have IDs and titles.
Posts with IDs, messages, member posting the message, and the date.

Ah, but what is the member? Another object? What do we care about with regard to members?

Also, why would you need an ID for each of those objects? Or would you need an ID at all?

btw, if any of you are going to a post secondary institute where you'll be going over DB design, they'll highly favor normalization. My classes all loved going to 3rd normal at most. Normalization == crap in a lot of real world practices.

Anyways, I think you guys are being too specific to start out with. Be more generalized and then work from there. Good DB design comes down to planning, incorporated into planning is thinking into the future, which, means taking scalability into a vast consideration of your design.

• Individual forums
• Threads
• Users

Forums
-------------
forum_id
parent_id
forum_name
date_created
total_threads *

Threads
-------------
thread_id
forum_id
title
created_by
created_on
last_reply_by *
last_reply_on *
total_posts *

Users
-------------
user_id
username
password
registered_on

Tekmosis, how comes you choose to include the stared fields at all? Obviously all of which can be generated by SQL statements anyway (count, most recent date etc.), do you find it is more server friendly? I guess it does make queries more simple yet also means more queries when adding/removing threads.

I do try to incorporate normalisation in to my databases, but usually keeping it simple is best. As long as you are aware of primary and foreign keys and plan ahead you should be fine!

quote tekmosis

btw, if any of you are going to a post secondary institute where you'll be going over DB design, they'll highly favor normalization. My classes all loved going to 3rd normal at most. Normalization == crap in a lot of real world practices.

The forums were coded back in 1990-esq(?) so using the forums as an example doesn't really hold water to the topic.

Actually, the columns I starred demonstrate denomalization very well. I believe it was Rasmus Lerdorf (creator of PHP) mentioned during a PHP seminar that the target number of queries you want to run on a page is 6.

It doesn't make sense to do two queries when you can do everything in a single query and without having to do multiple joins. The worst thing you can do is n+ queries.

Say you're listing all the forums and there's 30 total. Why would you want to run 31 queries versus 1? (one query to grab all the forums and then another query per forum to get the total number of threads). The query itself may be fast but still, why would you want n+ over a single query? :S

This message was edited by tekmosis on Aug 26 2008.

quote tekmosis

The forums were coded back in 1990-esq(?) so using the forums as an example doesn't really hold water to the topic.

quote tekmosis

Actually, the columns I starred demonstrate denomalization very well. I believe it was Rasmus Lerdorf (creator of PHP) mentioned during a PHP seminar that the target number of queries you want to run on a page is 6.

quote tekmosis

It doesn't make sense to do two queries when you can do everything in a single query and without having to do multiple joins. The worst thing you can do is n+ queries.

Say you're listing all the forums and there's 30 total. Why would you want to run 31 queries versus 1? (one query to grab all the forums and then another query per forum to get the total number of threads). The query itself may be fast but still, why would you want n+ over a single query? :S

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html#06
No, my tables are not 3rd normal.

Also, reading that page on 3NF you'll see this statement:

quote

A complete normalization of tables is desirable, but you may find that in practice that full normalization can introduce complexity to your design and application. More tables often means more JOIN operations, and in most database management systems (DBMSs) such JOIN operations can be costly, leading to decreased performance. The key lies in finding a balance where the first three normal forms are generally met without creating an exceedingly complicated schema.