[eluser]TheFuzzy0ne[/eluser]
[quote author="Dam1an" date="1243290702"]You're creating a forum? How did I miss that?
[/quote]
I'm terribly sorry, I must have forgotten to tell you about it.
[quote author="Dam1an" date="1243290702"]One option would be to keep it normalized and do the count in real time, but thats not what you're asking, I I'll assume you've decided you're definatly storing the post/thread count[/quote]
Indeed, I'm putting performance over normalization, just like most of the other major forums out there. PHPBB3, at least, has tools in place for resynchronising the thread/post counts manually, which I think should be more than adequate.
The post count is not something I'd consider to be critical data, and I have no doubts that PHPBB has put a lot more though into it than I have, so if it's good enough for them, it's good enough for me. Also, it means I can get the thread and post count for forums, topics, and users in a single query. I'm quite sure I'd need two or more if I counted it on each request. Plus, a COUNT query with a WHERE clause will no doubt be more resource intensive than a single call to a single row. There's no work to be done as such, just a simple search and read. Hopefully this makes sense.
As much as I'd love a 5NF database, I think in this case it's going to make things more difficult - especially when it comes to adding something like a topic, as multiple inserts are needed to different tables, which goes against the normalisation principles. I'm going to be realistic and just aim for 3NF, since it's a more realistic goal.
[quote author="Dam1an" date="1243290702"]From the 'make it as easy for the user' point of view, I would go with a seperate table, which would have user_id (both PK and FK), thread_count and post_count.
Maing users manipulate their current tables will potentially put off a lot of users (also, as unlikely as it is, what if they already have those columns in the user table, or whatever names you choose)[/quote]
I agree. Perhaps it should have it's own user table, which will essentially replicate the necessary data from the original users table? I'm just thinking that it's one less join. This could also allow users in the existing table to sign up for a forum account with a different name to their username. I'm unsure why this would be necessary, but I've been on quite a few Web sites where I've had to pick a nickname for the forum that's not the same as my username.
The forum will most likely grab the users information from the forum by the user ID that's stored in the cookie, but that would mean the cookie has to be encrypted.
