Home / Questions / 85678
Accepted
HeckDan
Asked: 2020-07-09 09:22:13 +0800 CST 2020-07-09 09:22:13 +0800 CST

Table Relation Many to Many to Many (m:n:k) SQL TERNARY RELATIONSHIP

  • 772

There is a lot of information about mam, but I can't find anything concrete to help me understand those complex tables where there is a Ternary Relationship (between 3) ; I know that for these cases an intermediate table must be created, something like the child table of a trio.

This table will have to inherit the primary fields from its mothers and convert those primary fields into a "composite primary field" (help me with the definitions in case I'm wrong, I'm not very clear on the topic that's why I ask the question ) if so let's see the following example ( not complete ):

CREATE TABLE `taller_bdphp`.`t3_asignacion` (

        `idst3` INT(7) NOT NULL AUTO_INCREMENT ,    /*este seria el id para
                                                    identificar el registro, ejemplo: Registro numero: 203*/

         `placa` VARCHAR(10) NOT NULL ,                 /*esto seria una FK y a 
                                                    su vez una PK de la tabla vehiculos*/

        `cedula_p` INT(8) NOT NULL ,                /*esto seria una FK y a 
                                                    su vez una PK de la tabla personal, 
                                                    sirve para indicar el mecánico que esta trabajando con el vehiculo y que servicio le realiza a su vez*/

        `idservicio` INT(3) NOT NULL ,              /*esto seria un FK y asu vez una PK de la tabla servicios, 
                                                    (existen determinados servicios, por ejemplo: Revision, Mantenimiento, Lavado.
                                                    y se le debe poder asignar uno o mas servicios al vehiculo.*/

         `fecha_iniciot3` DATE NOT NULL ,           /*esto seria un campo propio de la tabla ternaria,
                                                    indica la fecha en que se inicia el servicio asignado*/

        `fecha_fint3` DATE NULL ,                    /* otro campo que puede estar nulo,
                                                     indica la fecha que se termina el servicio (cuando el vehiculo se va)*/

        `Estadot3` VARCHAR(1000) NULL ,             /*otro campo simple para escribir o comentar sobre el estado del servicio*/

         PRIMARY KEY (`idst3`)) ENGINE = InnoDB;

(Uff I get tired doing that hehe...) I hope that you can understand it better... If you understood the table... the FK is NOT COMPLETE and the declaration of the "COMPOSITE PRIMARY KEY" ARE NOT DONE... That's my question: How do you do that?

  • Would the foreign fields have to be placed or declared again? is repeating them required? Here I don't know, because since they are also primary I don't know how to consider them in this case...
  • On the other hand, after that, how would the "COMPOSITE PRIMARY KEY" be declared? PRIMARY KEY ( idst3, placa, cedula_p, idservicio)) <<< would that be the correct way?
  • Lastly, how would the foreign keys be declared, those if they remain the same one by one with the same method as always? Well the method I use is:

FOREIGN KEY(placa) REFERENCES vehiculos(placa),

Is that method correct? although there are people who place a CONSTRAINT on it that I never really knew what it was for because even if it is not placed it still works, there are also those who add the

ON DELETE CASDADE ON UPDATE CASCADE,(to update the tables in "cascade" [if one is modified, the others are modified]) ¿In phpmyadmin, the ones that will be foreign and part of the primary compound are placed as index. You must then choose to create an index of a single column or create compound index??!

Reference Source: https://www.genbetadev.com/bases-de-datos/bases-de-datos-y-relaciones-ternarias

https://www.w3schools.com/sql/sql_foreignkey.asp

mysql

1 Answers

  1. Best Answer

    Hi, I don't know if this would make it clear, when you create a table resulting from a ternary relationship, the primary key and its foreign keys would be declared like this:

    FOREIGN KEY (fk1) references tabla_padre1 (pk);
FOREIGN KEY (fk2) references tabla_padre2 (pk);
FOREIGN KEY (fk3) references tabla_padre3 (pk);
PRIMARY KEY (fk1,fk2,fk3);

    If you add the foreign keys during the creation of the table, it is not necessarily necessary to add the reserved word Constraint(or restriction), but it is necessary when we add the reference of a foreign key after having created the table (you can declare the table and the composite primary key without the need to indicate at that time that the three fields are foreign keys). I hope that has clarified something. Regarding when to establish an n:m:k relationship, we can assume the case that for energy management in a nuclear power plant, we have an entity Central Nuclear, an entity Proveedor(some of the Uranium suppliers to the plant) and an entity Transportista(any of the truck drivers who carry out the transportation).

    If we want to know who the uranium supply is Proveedor, what it is Transportistacarrying and what Central Nuclearit is being transported to, as well as, for example, the amount of material it is transporting, the only way to collect it would be through a ternary relationship. (It is possible that there are clearer examples, this was the one with which I understood it better).

    • 1