I have two tables in a DB.

users

• ID

things

• ID
• rating
• rated_by

The users will be rating the things and as such I want to keep track of what users rated what things and with what "rating".

What I originally had planned was to have the "rated_by" field be a delimited array string of user "IDs" and have a parallel delimited array string in the "rating" field of the associated "rating" that user gave that stuff.

Like so:

users

• ID
  • 1
  • 2
  • 3
• name
  • Adam
  • Bob
  • Charlie

things

• ID
  • 1
  • 2
  • 3
• name
  • apple
  • banana
  • chocolate
• rating
  • 6.5 5.0 4.5
  • 2.5 4.0 8.0
  • 9.0 1.5 7.5
• rated_by
  • 1 2 3
  • 2 3 1
  • 3 1 2

Example:
user "ID" 1 gave thing "ID" 2 a "rating" of 8.0

However, I've been told that using delimited strings to store information like this is bad form.

The users and things tables will change at randomly different rates. (as users join and things are added by the users)

This wouldn't normally be a problem except that I want to do things like take the average rating of a particular thing on the fly and more importantly sort the things table by highest average rating. Likewise, I want to be able to able to do a mySQL command that selects all the user "names" that rated a particular thing.

Is the only way to do this is to keep that special information (obtained and/or calculated from existing info) in it's own table column, or is there a better way to construct the two tables? If so, what's the better method?

Is there some syntax that is like the explode() command in PHP in mySQL?

Originally Posted by Invisionary

things

• ID
  • 1
  • 2
  • 3
• name
  • apple
  • banana
  • chocolate
• rating
  • 6.5 5.0 4.5
  • 2.5 4.0 8.0
  • 9.0 1.5 7.5
• rated_by
  • 1 2 3
  • 2 3 1
  • 3 1 2

Example:
user "ID" 1 gave thing "ID" 2 a "rating" of 8.0

Originally Posted by Invisionary

Is the only way to do this is to keep that special information (obtained and/or calculated from existing info) in it's own table column, or is there a better way to construct the two tables? If so, what's the better method?

A much better solution in my opinion would be to create a third table, such as thing_ratings with fields: id, thing_id, rated_by, and rating. This way each rating has its own row. You won't have to mess with delimiters or serialization, which should make for cleaner code, as well as likely improve performance.

Originally Posted by Invisionary

This wouldn't normally be a problem except that I want to do things like take the average rating of a particular thing on the fly and more importantly sort the things table by highest average rating. Likewise, I want to be able to able to do a mySQL command that selects all the user "names" that rated a particular thing.

Using the third table, the queries would be on the simpler side compared to dealing with delimiters or unserializing data. I wrote a few below, but I didn't test them.

Average would be something like:
SELECT thing_id, AVG(rating) FROM thing_ratings GROUP BY thing_id

Order by average:
SELECT thing_id, AVG(rating) as rate FROM thing_ratings GROUP BY thing_id ORDER BY rate DESC

Users who rated an item (where $id is the id of the item)
SELECT users.name FROM thing_ratings JOIN users ON things_ratings.rated_by=users.id WHERE thing_id=$id

Frost gave a very good answer, probably worthy of some reputation, but more importantly, this is really worth a lot of consideration.

For most people, relational database theory is a pretty foreign concept when they first approach it. What's really important at the end of the day is that modern databases are meant to be able to achieve the things you've said you need, and to do it very well using relational schemas, like Frost designed. Apart from what you'd like to do now, this approach will make it easy to do do plenty of other types of analysis that might be useful down the line.

Using a delimited list in one column in one row instead of multiple rows - often in a different table - won't make most analysis impossible, but will drastically slow everything down. That sounds counter-intuitive, especially if you've done procedural or object oriented development, but the way rdbms's work is very different.

Hi,

+1 : a "join table" ("table de jointure" in french) is probably here the most logical and efficient way : a user can be associated with 0 to n things, and a thing with 0 to m users, rating being a property from this relation, not from a user or from a thing.