what does a ratio of 1 to many mean? [closed]


I have a question. in the design of a database, in the entity-relation part, which means or for what it is worth saying that it is a relation of 1: M M: 1 N: M

asked by Jdavid Barajas 27.05.2018 в 05:43

1 answer


It depends on the problem that you are solving, but I will put this example to try to explain


Cardinality between entities

  • One to Many: It means that within the problem to solve you have at least two tables that share characteristics with each other, that is, the elements registered in table B depend on a primary key in table A

    Usually the table structure is read from right to left,   that is, table B is on the right and table A is on the left


    Within a blog, we have that a user can have or make multiple posts / publications; therefore the design of our tables should be as follows


    The following table represents my user entity as the principal   of the 2 required in this example, whose primary key is the id

    describe usuarios;
    | Field | Type         | Null | Key | Default | Extra          |
    | id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(100) | YES  |     | NULL    |                |
    | clave | varchar(20)  | YES  |     | NULL    |                |

    Now as our relationship is one to many, where a user has multiple publications, the structure of the publications table is as follows

    describe publicaciones;
    | Field   | Type         | Null | Key | Default | Extra          |
    | id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
    | title   | varchar(100) | YES  |     | NULL    |                |
    | body    | varchar(100) | YES  |     | NULL    |                |
    | user_id | bigint(20)   | NO   | MUL | NULL    |                |

    What do I see in my publications table?

    As you can see I have a user_id field of the same type of BIGINT data that will be linked to the id of the user table; it is this same field which is going to become our foreign key; given that now every time we register a new publication it will be saved as follows

    INSERT INTO usuarios(name, clave) VALUES("alfred", "password");
    INSERT INTO publicaciones (title, body, user_id) VALUES("php", "es un lenguaje", 1);

    Where the number 1 that I insert at the end of my SQL statement is the id of   the users table; but instead of putting the user's name I put   said id that is linked to the user_id of the publications table

    To finish as you already separated your entities, you respected the normalization of databases, which is not having repeated data; so if you want to get those records now you can use the JOINS

    As I now want to show all the publications associated with a user I do the following

    select name, title FROM usuarios
    JOIN publicaciones
    ON usuarios.id = publicaciones.user_id
    WHERE usuarios.id = 1;

    What results in the following

    | name   | title |
    | alfred | php   |
    | alfred | js    |
    answered by 27.05.2018 в 05:56