Double reference to a MySQL table

This is the data model that I referred to in my comment. I explain it briefly:

• We have taken the student from the headquarter table, moving it to a new table that will handle the student - > headquarter

• We have created a student_headquarter table capable of handling many to many relationships between student and headquarter . This means that in the data model a student can during all the years as a student be registered in several locations (for each registration in a site there will be a row with the student's ID and the ID of the headquarters ). It is convenient to stop a little on this table:

  • has a column type that would serve to identify what type of registration the student has in that location. For example, type 1 would be an enrollment as a student, type 2 would be a training, type n would be whatever. Note that if the type are many you would have to extend the model, also creating a table type ...

  • this table is even thought for future evolutions of the data model. Imagine that things change and a student may be enrolled in a location in a certain period and in another location in another period. That change would be handled by adding a new column periodo to the table and you're done. It would be necessary to change the restriction of uniqueness and already, our model is ready to handle the new situation without any trauma.

Having said that, I leave you an example based on real data. Next, all the code for creating tables and inserting test data:

/*MYSQL: Estudiantes y centros tabla asociativa - https://es.stackoverflow.com/q/185957/29967*/

CREATE TABLE IF NOT EXISTS student 
(
    student_id       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    carnet           VARCHAR(12),
    student_name     VARCHAR(50),
    CONSTRAINT student_AK01 UNIQUE (carnet)
)ENGINE=INNODB;


CREATE TABLE IF NOT EXISTS headquarter 
(
    hq_id         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    headquarter   VARCHAR(150),
    CONSTRAINT headquarter_AK01 UNIQUE (headquarter)
 )ENGINE=INNODB;


CREATE TABLE IF NOT EXISTS student_headquarter 
(
    carnet          VARCHAR(12),
    hq_id           INT,
    type            TINYINT,
    CONSTRAINT student_headquarter_PK PRIMARY KEY (carnet,hq_id,type),
    FOREIGN KEY (carnet) REFERENCES student(carnet) 
        ON UPDATE CASCADE ON DELETE  CASCADE, 
    FOREIGN KEY (hq_id) REFERENCES headquarter(hq_id) 
        ON UPDATE CASCADE ON DELETE  CASCADE            
)ENGINE=INNODB;


INSERT INTO student (student_name, carnet) 
    VALUES
    ('Pedro', 'P-01'),
    ('Santiago', 'S-01'),
    ('Juan', 'J-01') ;


INSERT INTO headquarter (headquarter) 
    VALUES
    ('Sede 1'),
    ('Sede 2'),
    ('Sede 3'),
    ('Sede 4'),
    ('Sede 5');

INSERT INTO student_headquarter (carnet,hq_id,type) 
    VALUES
    ('P-01',1,1),
    ('P-01',5,2),
    ('S-01',2,1),
    ('S-01',3,2),
    ('J-01',4,1),
    ('J-01',1,2),
    ('P-01',2,2);

Let's now try a query like this:

SELECT 
    s.carnet, 
    s.student_name, 
    h.headquarter, 
    sh.type 
FROM student_headquarter sh
    INNER JOIN student s  ON sh.carnet=s.carnet
    INNER JOIN headquarter h ON sh.hq_id=h.hq_id;

Result:

carnet  student_name    headquarter        type
---------------------------------------------------
P-01    Pedro           Sede 1              1
P-01    Pedro           Sede 2              2
P-01    Pedro           Sede 5              2
S-01    Santiago        Sede 2              1
S-01    Santiago        Sede 3              2
J-01    Juan            Sede 1              2
J-01    Juan            Sede 4              1

What does the result show? If we take the case of Pedro , we see that the student is enrolled in Sede 1 in a type 1 (student) and that has done training ( type 2 ) in Sede 2 and in Sede 5 .

I must also say that this model will allow you to do very easily things that otherwise would be very complicated, such as grouping data by any of the columns involved, making calculations, etc.

Demonstration

Here you can see a COMPLETE DEMONSTRATION of the data used in the response .

I hope you find it useful.