MySQL table inside table

Question

I'm wondering what is the right way to store the following data inside an MySQL DB.

Let's say an object for example a video. and I want to store the the rating which was give to it by other user, so on option is create a column and store the ID of the users and their rating in a complex way, like that:

| ID | video_name | rating (usrID,rating)    |
| 5  | cool video | (158,4),(5875,1),(585,5) |

I guess that it is not the most efficient way to do so. Is there a way to store a table inside of a row? what is the most efficient way to do it so I can use SQL query on the rating without processing the data via a PHP code?

Answer 1

Normalize it

Have 2 tables.

I.e.

ID   Video_name
5    Cool Video

The second being

vid_id    userid   rating
5         158      4
5         5875     1
5         585      5

Answer 2

Create a second table, ratings:

+----------+---------+--------+
| video_id | user_id | rating |
+----------+---------+--------+
|        5 |     158 |      4 |
|        5 |    5875 |      1 |
|        5 |     585 |      5 |
+----------+---------+--------+

You can then group and/or join this table in queries, as desired; for example:

SELECT   videos.*, AVG(ratings.rating)
FROM     videos JOIN ratings ON videos.id = ratings.video_id
GROUP BY videos.id