Define relationship between tables in MySQL

3

Today I needed to create a table that had a similar purpose to Add the netflix queue where the movies that the user wants to see are saved (in the application the videos and photos will be stored), but soon I started to create table but I noticed that this being redundant and her aesthetic was not legal, then some member has had a similar problem or have any idea how to solve with the least amount of resources possible? remembering that more than one user can have the same movie in the play queue and the queue size is variable

Images of Tables

    
asked by anonymous 21.12.2014 / 11:17

1 answer

8

Apparently I did not see any problems in your tables, however to do what you want it is necessary to create another one, for example:

TABELA LISTAS
| id | id_usuario | id_video |

Then you would have something like this:

TABELA USUARIOS
| id  | login | pass |
|  1  | user1 |  123 |
|  2  | user2 |  abc |
|  3  | user3 |  1b3 |

TABELA VIDEOS (resumi ela)
| id  | titulo |      urlvideo       |
|  1  | Video1 | http://www.1.com.br |
|  2  | Video2 | http://www.2.com.br |

TABELA LISTAS
| id  | id_usuario | id_video |
|  1  |     1      |     1    |
|  2  |     2      |     2    |
|  3  |     3      |     1    |
|  4  |     3      |     2    |

So you could get the full list with users and videos like this:

SELECT
    usr.login,
    vdo.titulo,
    vdo.urlvideo
FROM
    LISTAS lst
    INNER JOIN USUARIOS usr ON (lst.id_usuario = usr.id)
    INNER JOIN VIDEOS vdo ON (lst.id_video = vdo.id)

Select above, you could use WHERE and filter only a specific user.

Just to explain a bit more, in the VIDEOS table, the videos will be registered only once and can be used in the list of any user without the need to register them again. The LISTAS table will be responsible for relating users to the videos, thus generating your list or queue, whichever you think best.

    
21.12.2014 / 13:55