Question: This question is more about performance.

Assume that there are two tables one with millions of records and one with some thousands of records in a DB like MySQL. By this example, I am trying to show a very unbalanced situation. So the question is, how expensive would be the (inner) join operation? (join on for example user\_goup from the 1st table and the id from the second). do you think that this architecture can be scaled to more records in tables?

Answer: 
* MIN(enrolled) is kludgy. It works only because of string choices and comparison.
* BUG! `MIN(`Date`), MIN(enrolled)` cannot be trusted to get data from the same row. You have a "groupwise-max" problem; see the tag.
* You need INDEXes; FKs are optional.
* `ID` on table\_3 is unnecessary; the PK can be `PRIMARY KEY(student_id, date)`
* Do use `DATETIME`, not `VARCHAR`.
* Table\_2 does not need `ID`; `PRIMARY KEY(student_id)` is really what you need.

Since you seem to be building `students` (`table_2`) and `income_history` (`table_3`) from `table_1`, these two steps might work:

```
INSERT INTO students (...)
    SELECT ... FROM table_1
        WHERE enrolled = 'enrolled';
INSERT INTO income_history (...)
    SELECT ... FROM table_1;

```

(If there are more options for `enrolled` or a student can be "enrolled" twice or ..., then the above won't work.)
```
CREATE TABLE table_1 (
    ID INT,
    studentId INT,
    `Date` DATETIME,
    enrolled VARCHAR(255),
    `parent name` VARCHAR(255),
    `parents annual income` INT
);
INSERT INTO table_1 VALUES
(1, 12345,  '2022/01/13 14:39:15',  'enrolled', 'ABC',  12345),
(2, 12345,  '2022/01/13 14:39:06',  'not-enrolled', 'ABC',  10345),
(3, 12345,  '2022/01/13 8:03:44',   'not-enrolled', 'ABC',  1467),
(4, 67890,  '2022/01/12 14:39:15',  'enrolled', 'DEF',  12345),
(5, 67890,  '2022/01/14 14:39:06',  'not-enrolled', 'DEF',  10345),
(6, 67890,  '2022/01/14 8:03:44',   'not-enrolled', 'DEF',  1467);
SELECT * FROM table_1;

```

```
CREATE TABLE table_2 (
    ID INT AUTO_INCREMENT,
    studentId INT,
    `start_date` DATETIME,
    `status` VARCHAR(255),
    `parent name` VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (studentId)
);
CREATE TABLE table_3 (
    ID INT AUTO_INCREMENT,
    studentId_Id INT,
    `Date` DATETIME,
    enrolled VARCHAR(255),
    `parents annual income` INT,
    PRIMARY KEY (ID),
    CONSTRAINT fk_studentId_Id  FOREIGN KEY (studentId_Id) REFERENCES table_2 (ID) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

```

To Insert the data into table\_2 from table\_1:

```
INSERT IGNORE INTO table_2 (studentId, `start_date`, `status`, `parent name`)
SELECT studentId, MIN(`Date`), MIN(enrolled), `parent name` 
FROM table_1
GROUP BY studentId, `parent name`;
SELECT * FROM table_2;

```

To Insert the data into table\_3 from table\_1 in relation with table\_2:

```
INSERT INTO table_3 ( studentId_Id, `Date`, enrolled, `parents annual income`)
SELECT t1.ID, t2.`Date`, t2.enrolled, t2.`parents annual income` 
FROM table_2 AS t1, table_1 as t2
WHERE t1.studentId = t2.studentId;
SELECT * FROM table_3;

```