I recently rewrote my banner management system for one of my boards. The board is fairly active (in fact we’re averaging over 100,000 page views daily now) so with multiple page views per second taking place during the busiest times of the day it would make sense to be concerned about performance. And I was. But I also had to be concerned about auditing my banner and page statistics, and ensuring that if I said a banner was going to be displayed every 10 page views that it was. So the system had to be 100% efficient and just as accurate. That presented some challenges.

More…

The IP address information for a poster is stored in the phpbb_posts table in phpBB2. In my Checkbox Challenge MOD it’s also stored during registration attempts. The IP address stored after being converted to hex and is then stored as an 8 character string. This is then decoded on the fly when requested. Sometimes you might want to decode the IP from the character string by using MySQL directly. It turns out there is a very simple formula to do that.

More…

In the first post in this series on working with recursive data I talked about several different ways to store the information in a database. Some of them were promising, but they all had complications of some kind or another. I was using the phpBB Doctor Project Manager database design as an example, but there are quite a few different scenarios where recursive data will be found. Since SQL is not a recursive language, I am trying to find the best way to model the data so that I can access it with minimal fuss.

As an example, in my project management system I need to be able to quickly and easily identify the parent task, if the task has any sub-tasks (child records), and which tasks are at the same level (siblings). I would like to be able to traverse the tree in either direction (up to the parent or down to the child) without using recursion. In order to do that, I need a model that is different from anything presented in the prior post.

More…

There are all sorts of scenarios that require recursive data. If you don’t know what “recursive” means, it’s a relationship from an entity back to the same entity. In English, it’s data that points back to itself. Some typical examples of recursive data are company org charts, inventory build instructions, or even forums for phpBB3. Yes, I’m talking about phpBB3, are you surprised? I hope not, because I’m going to reference phpBB3 only in passing. The article is actually more about storing recursive data in any form. It’s also how I store information in my phpBBDoctor Project Management system, among other things.

SQL is not a recursive language. When I write a query it’s all about relationships between rows, not about looping back through the same table. Oracle has a special construct used to traverse recursive data and it works very well, but it’s the only database that I am currently aware of to support this. Since most phpBB MOD authors will not be writing for Oracle, I will skip that concept for now.

As mentioned in the first paragraph I have recursive data in my project tracking system that I use here on the phpBB Doctor site. The design for this system is simple, but complex. The first table is the project table and it includes summary attributes for the project. These attributes include values such as when the project started, who is the project manager, a description of the project, and the status. The next table is the tasks table. A task is assigned to a project, but a task can be broken up into sub-tasks as well. There is no expected limit to the depth of the tasks. Here is a screen shot of my test project so you get an idea of what I’m talking about.

More…

A Check-Up From the Doctor

I will not install code for clients that was written by someone else without reviewing it first. There is a lot of bad code out there. There is also some very good code, don’t get me wrong about that. But many excellent MOD authors just don’t know databases as well as they would like. So the queries that they write may be functional, but not optimal. At this point in my involvement with phpBB I write most of my own MODs, so most of the time this review is for a private client who wants me to install something that I don’t already have or have no interest in writing.

At one point after I essentially rewrote a MOD for one of my clients they suggested that I offer this service via the Doctor Board. Hmm. Well. It’s an interesting idea. It wasn’t in my plans for this board when I started (and still isn’t) and I would much rather get my desired services completed and released first, but yeah, I could do that.

Probably in about 2012.

Maybe.

More…

In prior articles in this series I have talked about associative tables and primary / foreign keys. In this post I am going to talk more about keys but more specifically about table joins. If you’ve been working with databases for a while this article will probably seem fairly basic. But like many basic things, it’s important.

More…

Over the years that I have been involved with phpbb.com I have seen a number of posts - not that frequent, mind you, but more often than I would expect - asking how to “renumber my users” or something like that. It seems that folks are bothered by the fact that the first user_id is 2, or sometimes by the fact that there can be gaps in the sequence of user_id values. Why worry? Your database doesn’t.

More…

This is the first in a planned series of posts where I will share some principals of good database design. Many MOD authors are good at coding php, and some are even comfortable with sql. But a good database design can make or break your project, and it’s not a skill that is taught in programming classes. Getting the database design right the first time can certainly can make coding and performance tuning easier. This first post is going to explain the purpose of something called an Associative table relationship. We even have a couple of those in phpBB.

More…