Page 1 of 2
What's more efficient?
Posted: Tue Dec 25, 2007 10:42 am
by JellyFish
What's more efficient. I'm making this rating system. And I can go about this two ways.
The first is that I have two fields: rating and num_rates. In the rating I'll store a number that grows every time someone rates and num_rates would hold the number of people that rated. For example, if someone rates, let's say, 5 stars 5 is added to rating and 1 is added to num_rates.
Then all I need to do is divide rating by num_rate and I'd get the average.
The second way would be having only one field: rating. But this time storing an array of rates. For example if someone rates 5, it will not add to rating, but instead concatenate a new item in a list of comma separated number that range from 1 to 5.
Which method is better? Which would you use? The first idea requires two fields but might have less space. The second requires only one field but the array might get out of hand.
Which is more efficient then the other?
Thanks for reading, and merry Christmas!
Posted: Tue Dec 25, 2007 10:54 am
by John Cartwright
I would personally store each rating as it's own row and the date of which it was rated. Databases are designed to handle many rows easily assuming you index them properly, so I wouldn't be worried about performance until it actually becomes a problem.
I would choose flexibility over performance 99.99% of the time, plus you can implement those cool rating features such as, 19%+ since last week like imdb.com does
Posted: Tue Dec 25, 2007 11:00 am
by JellyFish
Jcart wrote:I would personally store each rating as it's own row and the date of which it was rated. Databases are designed to handle many rows easily assuming you index them properly, so I wouldn't be worried about performance until it actually becomes a problem.
I would choose flexibility over performance 99.99% of the time, plus you can implement those cool rating features such as, 19%+ since last week like imdb.com does
How would creating another table for ratings work? An example would be much appreciated.
Posted: Tue Dec 25, 2007 11:15 am
by John Cartwright
Example:
Code: Select all
id | item_id | date | rating
------------------------------------------------------------------------------------
1 | 1 | 2007-12-25 12:11:13 | 5
2 | 1 | 2007-12-25 12:12:13 | 2
3 | 1 | 2007-12-25 12:13:13 | 1
4 | 1 | 2007-12-25 12:14:13 | 3
5 | 1 | 2007-12-25 12:15:13 | 3.5
6 | 1 | 2007-12-25 12:16:13 | 4
Code: Select all
SELECT AVG(`rating`) FROM `ratings` WHERE `item_id` = 1
Posted: Tue Dec 25, 2007 11:40 am
by JellyFish
Okay. If you had to pick only between my first two options, what would it be?
Posted: Tue Dec 25, 2007 11:43 am
by John Cartwright
Neither, but if someone put a gun to my head the first.
Posted: Tue Dec 25, 2007 9:48 pm
by Kieran Huggins
It's OK Jcart - I'd be watching your back. No one should have to suffer the horrors of Database Denormalization in this day and age.
@JellyFish: The ratings table is worth doing. As an added bonus, the time it takes you to set it up is likely less than the time it would take to implement either of your earlier suggestions (queries and all). Give it a shot - we're here to help debug!
As a side note, MySQL is much faster at doing joins, etc... than PHP is of simulating them. MUCH faster.
Posted: Tue Dec 25, 2007 10:26 pm
by John Cartwright
Kieran Huggins wrote:It's OK Jcart - I'd be watching your back. No one should have to suffer the horrors of Database Denormalization in this day and age.
Most Hilarious Torontonian of the Year++
Posted: Wed Dec 26, 2007 12:20 am
by JellyFish
I think I'm going to need some good mySQL references on joined tables.
What benefit will a separate table give me, opposed to having a two extra fields in a current table?
Posted: Wed Dec 26, 2007 12:47 pm
by Kieran Huggins
It will be faster to program, it will perform better, and it will be easier to visualize when programming. It's not as scary as it sounds!
Posted: Fri Dec 28, 2007 3:08 pm
by JellyFish
I haven't built a rating system of any kind yet.
So before I do, I must say I realize that I have implemented my keyword/tagging system all wrong.
I have a table like so:
username
name
etc...
keywords
and like Kieran said using php to loop through each one of them is ridiculous. I should separate it into two tables:
table1:
username
name
etc...
and
table2
username
name
keywords
and then select all from table one where username and name equal to the table2 username and name or something like that.
Could someone give some great SQL tutorials or good documentation? I find that mysql.com to be little confusing.
Posted: Fri Dec 28, 2007 3:20 pm
by Kieran Huggins
almost!
users:
id
username
name
etc...
tags:
id
tagname
tags_users: (the join table)
id
tag_id
user_id
A tag can have many users, and a user can have many tags. Also, you can change the tag's name (spelling, etc...) or add a context column without killing your other tables.
Ratings are a many (ratings) to one (user) relationship. A user has many ratings, but a rating was only made by one user.
ratings:
id
user_id
rating
Posted: Sat Dec 29, 2007 12:35 am
by JellyFish
Kieran Huggins wrote:almost!
users:
id
username
name
etc...
tags:
id
tagname
tags_users: (the join table)
id
tag_id
user_id
A tag can have many users, and a user can have many tags. Also, you can change the tag's name (spelling, etc...) or add a context column without killing your other tables.
Ratings are a many (ratings) to one (user) relationship. A user has many ratings, but a rating was only made by one user.
ratings:
id
user_id
rating
Well, it's not really a tag system for users, but a tag system for information created by a user. When I wrote:
username
name
etc...
It may appear like a user table, and I realize that now. But the 'etc...' part is more fields such as heading and description. Username is really a field that specifies who the "article"(I'll call them) belongs to. And name isn't a first name or last name or both, but instead the name of the "article".
I guess virtually it would make no difference weather it was a table for users or articles. But just in case, I thought I'd might clear that up.
Now with your example, Kieran, how would I select all the records in users where...I haven't go a real clue on where to start?
Could I maybe get an example on how I would select rows from users with the tags from a php string: $_GET['search']?
Posted: Sat Dec 29, 2007 2:34 pm
by Kieran Huggins
To select all the tags for a user (untested):
Code: Select all
SELECT * FROM tags INNER JOIN tags_users ON tags.id = tags_users.tag_id WHERE (tags_users.user_id = $user_id )
To select all users with a specific tag (also untested):
Code: Select all
$tag_list = implode(',',$tag_array);
Code: Select all
SELECT * FROM users INNER JOIN tags_users ON users.id = tags_users.user_id WHERE (tags_users.tag_id = tag.id AND tag.name IN $tag_list)
Posted: Sat Dec 29, 2007 5:16 pm
by JellyFish
Kieran Huggins wrote:To select all the tags for a user (untested):
Code: Select all
SELECT * FROM tags INNER JOIN tags_users ON tags.id = tags_users.tag_id WHERE (tags_users.user_id = $user_id )
To select all users with a specific tag (also untested):
Code: Select all
$tag_list = implode(',',$tag_array);
Code: Select all
SELECT * FROM users INNER JOIN tags_users ON users.id = tags_users.user_id WHERE (tags_users.tag_id = tag.id AND tag.name IN $tag_list)
What does the ON clause do? Is it just like the WHERE clause?