Adding post-count columns..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Adding post-count columns..

Post by seodevhead »

Hey guys, I have a rather large DB with many tables that specializes in product reviews. I want to make an addition of "post counts" (much like here on forums like vB and phpBB) for each user. I have a "users" table that has all the information on the members. Is this the most applicable place to add some columns such as "post_count", "num_reviews", etc?? Any suggestions on setup would be greatly appreciated. Thanks.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes, the user table is most often used to store such values.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

You can think of making a new table Reviews_tbl with MemberId, PostCount but this would be a overhead when you want postscount of the member's information often...this would require join which is expensive.

but in a case, where you will use postcount more often than other details in the Members table then it would be worth developing it.

But why do you actually want to store postscount, why cannot you calculate dynamically. But sometimes, it is faster to have postcount as a column because you increment postscount everytime a post is made by a member in the other case when calculating dynamically, if 10000 people are accessing a post/topic like page at the same time, calculating postscount for many users at the same time is expensive.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

calculating a postcount can be time prohibitive, true.. but also, if you have a pruning system, where old posts/threads/whatever are removed from circulation, you don't want to drop people's post counts.. they'll get annoyed..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I do not know about phpbb, how does phpbb calculate posts count for each user?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Because of the pruning etc, the post count isn't a value that can be calculated anyway. So they are stored somewhere... ;)

According to http://www.phpbbdoctor.com/doc_columns.php?id=24 they are in the phpbb_users table ;)
Post Reply