0

I have two tables as below:

Table name: table_1

ID studentId Date enrolled parent name parents annual income
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

I am working on refactoring this table as it has some flaws and redundant data. Also, the date is mentioned in this table as VARCAR(20).

I want to refactor it into 2 tables as below:

Table A --Only 1 entry for each student ID, the first date when entry was made in the table, the latest enrolled status of the student, and parent name (made the entries in the table_1 bold)

Table name: table_2

ID studentId strat_date enrolled parent name
1 12345 2022/01/13 8:03:44 enrolled ABC

Table B -- The rest of the history data

Table name: table_3

ID studentId_1 Date parents annual income
1 1 2022/01/13 14:39:15 12345
2 1 2022/01/13 14:39:06 10345
3 1 2022/01/13 8:03:44 1467

The "ID" column in the "table_2" table is the PRIMARY KEY. The "studentId_1 " column in the "table_3" table is a FOREIGN KEY.

I know how to create table_2.

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;

Which gives an output:

ID studentId strat_date enrolled parent name
1 12345 2022/01/13 8:03:44 enrolled ABC

How do I create table_3 as it has "FOREIGN KEY" in the table as below:

ID studentId_1 Date parents annual income
1 1 2022/01/13 14:39:15 12345
2 1 2022/01/13 14:39:06 10345
3 1 2022/01/13 8:03:44 1467

2 Answers 2

1
  • 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.)

2
  • for students table, with enrolled=enrolled condition, I would get the date of enrolled row. What I am interested in is the first date the entry was made for that student. Commented Jan 25, 2022 at 8:50
  • If there is only one 'enrolled' row, then use WHERE enrolled = 'enrolled', not min()
    – Rick James
    Commented Jan 25, 2022 at 15:25
0
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.