Topic: Username and Userid entered with posts?

I've noticed that both the username and userid is entered with posts. What happens if an admin edits a username? Does Punbb update the textual username of all the posts?

Would it be better if an 'inner join' was used to extract the username from the 'users' table instead?

I don't HAVE a signature, ok?

Re: Username and Userid entered with posts?

Yes, if an admin changes a username, all posts by that user will use the new name.

Re: Username and Userid entered with posts?

Thanks so much for answering. smile

I don't HAVE a signature, ok?

4 (edited by lj 2005-11-13 22:01)

Re: Username and Userid entered with posts?

I started playing with PunBB today, thought I'd have a look at the database... one of the first things I noticed was that the posts table has a poster varchar and a poster_id integer... only the latter one seems to be needed if you ask me.

As my forum (currently a phpBB) has over 500,000 posts, that's a lot of repetition! wink

I couldn't find any topics debating the issue except this one... so what's the reason for doing it like this? Perhaps if there's a problem query that required breaking "normal form" (whatever that is!) then I'd be interested to take a look...

Re: Username and Userid entered with posts?

The poster column is needed for guest posts.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Username and Userid entered with posts?

lj wrote:

I started playing with PunBB today, thought I'd have a look at the database... one of the first things I noticed was that the posts table has a poster varchar and a poster_id integer... only the latter one seems to be needed if you ask me.

As my forum (currently a phpBB) has over 500,000 posts, that's a lot of repetition! wink

I couldn't find any topics debating the issue except this one... so what's the reason for doing it like this? Perhaps if there's a problem query that required breaking "normal form" (whatever that is!) then I'd be interested to take a look...

In a quick glance, here was one query I found (from post.php):

$result = $db->query('SELECT poster, message FROM '.$db->prefix.'posts WHERE id='.$qid) or error('Unable to fetch quote info', __FILE__, __LINE__, $db->error());

So, it prevents having to join the users table into the query when fetching a quote smile

http://fluxbb.org

Free PunBB Hosting - lots of mods, easy to customize

Re: Username and Userid entered with posts?

To tell you the truth, I can't tell you why it is filled out for non-guest posts. It really shouldn't have to be since the users table is joined in in viewtopic anyway. I have a distinct feeling I put it in for a reason through smile I'll put investigating it on the todo list. Thanks for the tip.

Edit: I haven't looked, but I think user search relies on it as well. Should be a non-issue though.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

8 (edited by lj 2005-11-13 23:33)

Re: Username and Userid entered with posts?

Thanks for the replies guys...

Smartys wrote:

In a quick glance, here was one query I found (from post.php):

$result = $db->query('SELECT poster, message FROM '.$db->prefix.'posts WHERE id='.$qid) or error('Unable to fetch quote info', __FILE__, __LINE__, $db->error());

So, it prevents having to join the users table into the query when fetching a quote smile

What's wrong with joining the users table? It's a single lookup in a BTREE index. The cost should be negligible.

On the other hand, every time the posts table is accessed in a way that can't be wholly covered by the index, all those username fields are being read from disk and taking up any memory cache space. This probably has a higher overall cost than the above.

I'm not saying you're wrong (I only joined today so it would be a bit rude anyway!), but I would caution against assuming joins are bad (unless it's already been performance tested). In general, smaller tables and more joins is better.

Rickard wrote:

The poster column is needed for guest posts.

Oops, I'd forgotten about this functionality (because personally I probably won't use it...)

Well I guess if the column were made nullable and only used for anonymous posts, then it shouldn't hurt. smile

Re: Username and Userid entered with posts?

I think Smartys was being ironic. Having the poster column in the database solely to prevent a join in one script (that isn't even accessed that often) is of course idiotic smile

The poster column will remain, but I will investigate whether it can be null:ed for all non-guest posts. I don't think it should be a problem.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

10

Re: Username and Userid entered with posts?

Rickard wrote:

I think Smartys was being ironic. Having the poster column in the database solely to prevent a join in one script (that isn't even accessed that often) is of course idiotic smile

Heh, looks like a [irony] BB tag would have come in handy there! wink