Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    Unanswered: Member statistics table or main table

    I have a members table. Should I put statistics like blog count, topics count, post count etc in a separate member_statistics table OR the main members table?

    If going for a separate table, should it be named statistics or something else?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    separate table, same table, it doesn't matter

    statistics, some other name, it doesn't matter

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can't you just work out the stats on-the-fly?
    Code:
    SELECT Count(*) As number_of_blogs
    FROM   dbo.blogs
     INNER
      JOIN dbo.members
        ON members.member_id = blogs.member_id
    WHERE  members.member_id = @member_id
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming this is anything more than an utter failure of a site, stuff like post count you wouldn't want to work out on the fly. Can you imagine scanning the entire DBF table to count up every time this page is shown to display your 9k post count and Rudy's 14.7k count?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    touché - I wasn't considering scalabilty in my answer.
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2009
    Posts
    66
    The reason why I wanted to put it in a separate table was to avoid read/write locks. The member table is read more often and because the "count queries" are in a transaction, it would lock the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •