New entries only: Update recently added fields

Question

I'm trying to find a way to be able to update the unit code for any new unit product, but keep the original unit code for unit products that have already been created.

That is, if the unit code changes between the topics provided, I want the unit code to reflect the exact code at the time the unit course was created.

This is my form

CREATE TABLE Units (
    UnitsID                 INT(10) AUTO_INCREMENT,
    UnitsName               VARCHAR(255) NOT NULL,
    UnitsCode               VARCHAR(20) NOT NULL UNIQUE,
    PRIMARY KEY (UnitsID)
);

CREATE TABLE UnitsOffering (
    UOfferingID             INT(10) AUTO_INCREMENT,
    UOUnits                 INT(10) NOT NULL, 
    YIIntake                VARCHAR(255) NOT NULL,
    YIYear                  INT(4) NOT NULL,  
    PRIMARY KEY             (UOfferingID),
    FOREIGN KEY             (UOUnits) REFERENCES Units(UnitsID)
);

I can update it using the current stored procedure, but when I list the unit products and unit details, it reflects the new unit code, not the code that was in the database when the product was created.

CREATE PROCEDURE UpdateUnit(IN vUnitsID VARCHAR (255), IN vUnitsCode VARCHAR (20))
BEGIN
UPDATE Units 
SET UnitsCode = vUnitsCode
WHERE UnitsID = vUnitsID;
 END;

This is the select statement I use when creating the list.

SELECT  O.UOfferingID, U.UnitsCode, U.UnitsName, O.YIIntake, O.YIYear
        FROM (UnitsOffering O
        INNER JOIN Units U
        ON  O.UOUnits = U.UnitsID)
        ORDER BY O.UOfferingID;

I'm not sure if this can actually be done. If that makes sense, I'm not sure if there's something wrong with the way my table is set up, the update statement, or the list statement.

Any help would be greatly appreciated. I tried looking at some documentation to see how to do this but couldn't find the correct keyword.

Below is an example of what I hope to be able to list. For this example, what I want it to display if the unit code changes after 2021 ends.

COS10000 - Introduction to Programming - Semester 2 2021
INF30011 - Database Implementation -  Semester 1 2022
COS10059 - Introduction to Programming  - Semester 2 2022

Answer 1

There are two possible methods:

1. When creating a new record in UnitsOffering, physically write the UnitCode value to the record (in a new column) instead of linking to the value in the Units table via FK
2. Redesign the unit table so that it holds history, e.g. all changes are treated as inserts, not updates, and each record has a start/end date (or something used to identify when a record is the current version other mechanisms). There then needs to be logic to ensure the correct value is written to UnitsOffering.UOUnits