so, i’m creating some extremely simple forums, based around punbb (obviously i have to recode pretty much the whole thing, but i’m using their ideas). it’s pretty minimal, but i had a look at a few different open source forums. phpbb3 and punbb. (some others too).

when i first learned php and mysql, i was taught never to repeat information in the database. for example, if you look up a post, then you look up the post_user_id and then from that you look up the user_id in the users table to get the username. It recently occurred to me that perhaps storing the username in the posts table would allow less queries.

is this bad practice? or should i design my tables so that less queries are used, rather than never repeating myself.

thanks for any guidance you can offer.

also, anyone have any idea how to say “last post: 15 minutes ago” like in these codeigniter forums, rather than “last post: 9:30”. i think the former is a lot easier on the brain…

You are right to be wary of de-normalising your database.

Stick with storing usernames in your user table, and storing user ID’s in your message table.

also, anyone have any idea how to say “last post: 15 minutes ago” like in these codeigniter forums, rather than “last post: 9:30”. i think the former is a lot easier on the brain…

How far have you got with this one?

A function that takes a given DATETIME stamp, and calculates the delta with current time, then gives a ‘fuzzy’ string back - it’s just a matter of calculating what different time periods should generate what response.  Possibly a large switch or a series of if/elseif’s I’d imagine.

would it be faster the other way? what’s the benefits of each? thanks.

tomdelonge - 26 June 2009 11:10 AM

would it be faster the other way?

Who knows.  Possibly.  Depends what you do.  If you want to find every message posted by a given user, then probably not as you’ll be doing a string search, which even on an indexed column is going to be slower than an indexed integer column, especially one that’s the PK (so presumably ‘sorted’ in the right order already).

What performance problems are you expecting to hit that every decent DB designer doesn’t?

what’s the benefits of each? thanks.

Database normalisation has lots of benefits for OLTP systems.  I’d suggest you start with the wikipedia article on the subject and proceed from there.

The proper way is to use a join in your query to join the post and the user tables.  The post will contain the userid key.  Your query would look something like this:

SELECT * FROM posts LEFT JOIN users ON posts.userid = users.userid WHERE postid = 1

Store the timestamp of the post in the database, then grab the current timestamp and subtract the post timestamp to get the difference in seconds.  You can then divide by 60 to get the number of minutes.

Ahh .. I thought I’d seen something about this a little while back, and found the reference in my custom helper file - I’d pilfered Johan’s code, but happily also made a note of the url.

Check out this Nicer Dates thread that does pretty much what you want.