Home / Questions / 151051
Accepted
user75901
Asked: 2020-03-28 15:48:32 +0800 CST 2020-03-28 15:48:32 +0800 CST

One-to-many relationship difference from many-to-many relationship

  • 772

I have an example where I don't quite understand. What is the optimal way to solve it?

A table called usuarios.

A table called publicaciones.

If I plan to save the records of a post with that idof the user who registered it; is the following structure correct?

enter image description here

But for example considering that an intermediate table called publicaciones-usuarioswith a structure similar to the following could be created:

enter image description here

What is the optimal way?, since for example if I make the first structure with a inner joinlike the following:

SELECT titulo, cuerpo, username FROM publicaciones
INNER JOIN usuarios
ON usuarios.id = publicaciones.usuarios_id
WHERE usuarios.id = 2;

I can extract the publications that a user makes taking into account that the link between both tables is that idof the user as a foreign key in the table publicaciones; but I don't quite understand under what scenarios each one is used

It is just an example considering that I seek to clarify this doubt

base-de-datos

1 Answers

  1. Best Answer

    the relationships themselves

    The underlying question to answer your question is not whether using this type of relationship I can obtain such data, while using this other type of relationship I cannot obtain it .

    Actually, the data can be obtained equally with any type of relationship, if they are well planned.

    In short we can say that:

    The one-to-many relationship

    Applies only when:

    • a publication cannot have more than one user .... ( one to ... )
    • a user can have multiple posts ............... ( many )

    Or vice versa:

    • a user cannot have more than one publication .... ( one to ... )
    • a publication can have several users ............... ( many )

    The many-to-many relationship

    Applies when:

    • a publication can have more than one user ........ ( many to... )
    • a user can have multiple posts ............... ( many )

    Depending on how your application is set up, based on the reality of the data it handles , then you will have to decide which type of relationship to implement.

    In other words, what is the reality of the data that your application is going to handle?:

    • One user, multiple posts? : relationship one (users) to many (posts)
    • One post, multiple users? : relationship one (posts) to many (users)
    • multiple users, multiple posts? : many (users) to many (posts) relationship by implementing an associative or join table (users_posts)

    The optimal mode

    As for the optimal way to do things, as @gbianchi has said in his comment. The optimal mode is the one that optimizes what you want to do in the database, adhering to business rules.

    It is not enough to have a type of relationship in accordance with the reality of the data you handle. There will still be loose ends that you must tie up in the definition of the tables, such as indexes to speed up data retrieval, control duplicate records, add referential integrity constraints that prevent the existence of orphan records, etc.

    Get the data

    Whatever relationship you use, you can always get the data you want from the tables.

    Let's see:

    Getting data in a one-to-many relationship

    SELECT 
    u.nombre, 
    p.titulo 
FROM usuarios u
INNER JOIN publicaciones p ON u.id_usario = p.id_usuario

    Getting data in a many-to-many relationship

    SELECT 
    u.nombre, 
    p.titulo 
FROM usuarios_publicaciones up
INNER JOIN usuarios u ON up.id_usario = u.id_usuario
INNER JOIN publicaciones p ON up.id_publicacion = p.id_publicacion

    In both examples the same columns are obtained. The only thing that varies is the way of obtaining the data by doing the JOINcorresponding, based on the way in which we have created the relations.

    What I mean is that you can't define a relation thinking that it has some implication on how you get the data . It has no implication. In both examples we get the same columns nombrefrom table usuariosand titulofrom table publicaciones. And so, we can get any column that participates in the relation.

    Another thing would be the fact of obtaining grouped data, for example in the case of many-to-many relationships. For that there are also grouping functions, such as GROUP_CONCAT, in the specific case of MySQL.

    • 12