What's more efficient?
Moderator: General Moderators
What's more efficient?
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!
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!
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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
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.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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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 | 4Code: Select all
SELECT AVG(`rating`) FROM `ratings` WHERE `item_id` = 1- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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.
@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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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.
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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
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: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
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']?
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
To select all the tags for a user (untested):
To select all users with a specific tag (also 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 )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?Kieran Huggins wrote:To select all the tags for a user (untested):To select all users with a specific tag (also 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 )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)