CODEWITHSUNDEEP
mysqlsql
Kirill Kulakov
Kirill Kulakov

Reputation: 10245

MySQL table inside table

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?

Upvotes: 5

Views: 26373

Answers (3)

zen
zen

Reputation: 122

The normal way is an entity relationship diagram.

Then you have

ratings * --- 1 video

That is, you have a table "video"

ID | Name
5  | cool video

And a table "ratings"

ID | value | USERID | video_ID
1  |  4    | 158    |  5
2  |  1    |  5875  |  5
3  |  5    |  585   |  5

Upvotes: 2

Ed Heal
Ed Heal

Reputation: 60017

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

Upvotes: 6

eggyal
eggyal

Reputation: 125925

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

Upvotes: 13

Related Questions