Define relationship between tables in Mysql

Asked

Viewed 5,118 times

3

Today I needed to create a table that had a purpose similar to Add the Netflix queue where are saved the movies that the user wants to see (in the application would be stored the urls videos and photos)As soon as I started to create table but I noticed that there was data redundancy and its aesthetics was not cool, so some member has already experienced 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 the Tables inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

mysql database

  • the column data on the table VIDEOS represents what? The date the video was registered?

  • yes date of video registration

  • What is the aesthetic problem you’re seeing? What do you want to know? What have you tried to do to assemble the missing table? The exercise statement no longer contains any relevant information?

  • The logic would require a table "n:m" usuarios_videos , relating users to their videos.

  • The problem is that when I created the display queue the data gets duplicated between the lines, another point is that the amount of movies in the display queue is variable EX: user with id:1 has in the queue the movies with id:2, id:7, id: 4, id:3. user with id 2: queued movies with id:5, id:7, id:8.

1 answer

8


Apparently I saw no problem in your tables, but to do what you want it is necessary to create one more, for example:

TABELA LISTAS
| id | id_usuario | id_video |

Then you’d 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    |

Then 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)

With the select above, you could use the WHERE and filter only one specific user.

Just to explain a little more, in the table VIDEOS, videos will be registered only once and can be used in any user’s list without the need to re-register them. The table LISTAS will be responsible for relating users to the videos, thus generating their list or queue, which you find best.

  • But if for example a user had 20 videos in the list, he would have 20 entries in the table, which would be 20 lines

  • he would have 20 records on the table LISTAS, in my reply, the user with id = 3 has 2 records, because has 2 videos in your list, already the id = 1 and id = 2, has a record each in the table LISTAS, result 4 records in total

  • But there is no saving Alexandre ? since a user in your example has multiple entries, so if he had twenty videos in the queue he would have 20 entries in the table

  • if we are talking that a user can have several different videos, I can not view another way, it may be that someone knows better the MySQL and have another vision!

  • 3

    In this case the usual way to solve is this. Relational database is for this, to work with a lot of lines. There are those who put everything in one field, with separators, but usually this is sign of serious problems of architecture.

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.