Professional Web Applications Themes

Design doubt: extra column or extra query? - MySQL

Imagine the following scenario: Article - aid - title - body - user_id Votes - vid - rate - user_id - article_id Votes keeps a list of tuples composed by the rate, the article being voted and the user who is casting his/her vote. Assume that a vote is a number between 0 and 10. I need this table, in order to make sure that a user votes only once and also for statistics purposes. In my web application, every time I display an article, I need to show what's the average vote for that article.   followings? 1) Add ...

  1. #1

    Default Design doubt: extra column or extra query?

    Imagine the following scenario:

    Article
    - aid
    - title
    - body
    - user_id

    Votes
    - vid
    - rate
    - user_id
    - article_id

    Votes keeps a list of tuples composed by the rate, the article being
    voted and the user who is casting his/her vote. Assume that a vote is a
    number between 0 and 10. I need this table, in order to make sure that
    a user votes only once and also for statistics purposes. In my web
    application, every time I display an article, I need to show what's the
    average vote for that article.
     

    followings?

    1) Add the columns "sum_of_votes" and "number_of_votes" to the
    'Article' table, in this way I won't have to query 'Votes' everytime I
    need to just display an average value. I will just retrieve
    sum_of_votes/number_of_votes to get the average. Everytime a vote is
    cast, beside the insert into 'Votes' I will also perform an insert into
    'Article' to update the values of "sum_of_votes" and "number_of_votes".

    2) Keep the database structure as it is, and perform a query on 'Votes'
    to retrieve the average of all the votes for the given article. 'Votes'
    I assume, can become a pretty big table.

    3) Other solution...

    I often encounter a similar situation for average, sums, or similar
    data that needs to be retrieved, and I wonder what's the best approach.
    Please help me understand and learn from your expertise.

    Thank-you.

    lookmarge@gmail.com Guest

  2. #2

    Default Re: Design doubt: extra column or extra query?

    com wrote:
     
    >
    > followings?
    >
    > 1) Add the columns "sum_of_votes" and "number_of_votes" to the
    > 'Article' table, in this way I won't have to query 'Votes' everytime I
    > need to just display an average value. I will just retrieve
    > sum_of_votes/number_of_votes to get the average. Everytime a vote is
    > cast, beside the insert into 'Votes' I will also perform an insert into
    > 'Article' to update the values of "sum_of_votes" and "number_of_votes".
    >
    > 2) Keep the database structure as it is, and perform a query on 'Votes'
    > to retrieve the average of all the votes for the given article. 'Votes'
    > I assume, can become a pretty big table.
    >
    > 3) Other solution...
    >
    > I often encounter a similar situation for average, sums, or similar
    > data that needs to be retrieved, and I wonder what's the best approach.
    > Please help me understand and learn from your expertise.
    >
    > Thank-you.[/ref]

    Personally, I'd opt for solution #2. Less implementation, and less chance of error. As long as 'Votes' is indexed correctly, then there shouldn't be much of a performance hit. You will only ever be interested in the votes for a given article, so an index on this field should be sufficient for the query.

    --

    Murdoc Guest

Similar Threads

  1. Query yzer adds extra space/newlines
    By Mark in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 08:15 AM
  2. Extra windows
    By Renee in forum Windows XP/2000/ME
    Replies: 3
    Last Post: July 5th, 10:47 AM
  3. XML extra for Director
    By bgc104 webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 1
    Last Post: July 1st, 07:07 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139