Immutable Identification of Maximum/Latest Minimum/Earliest
As you gain experience with SQL and database queries, their power becomes increasingly evident. However, it’s important to understand that there are both optimal and suboptimal design patterns in database design. Recording all relevant data and leveraging queries to uncover insights is a key principle.
One such design approach is the concept of Slowly Changing Dimensions (SCD) or ensuring that important data is held in a fashion that considers immutability. Immutability is not a feature of technology but a arises from the design of how the data is held. In accounting double entry uses immutable procedures to ensure that an audit trail is preserved. It’s less about the type of database and more about the structural methodology. For example, double-entry accounting—despite originating as a paper-based system—embraces immutability at its core. When canceling an invoice, a bookkeeper doesn’t simply delete the invoice record. Instead, they create a credit entry that references the original invoice. The account’s final position is the net effect of both the invoice and the credit, maintaining a transparent audit trail.
This pattern can be applied effectively in planning systems. For instance, when recording ownership or development information for land, we can track changes over time. Knowing who owned a property at the time of a specific planning application and how it differs from previous applications becomes essential for maintaining accurate and historical records.
A short but important pattern for obtaining the latest child record per parent record based on a child value. I use this pattern in my discussion on a structure for recoding developer contribution policies link here
This can easily be altered to oldest or minimum and see the bottom table to see the structure that I was referring to in the parent table (MS Access example)
Please note that the FKID key is a foreign key that references a parent table which holds unique primary keys for each venue of an imaginary set of stadiums or venues ( my created example )
SELECT *
FROM public.T02AttendanceGame T1
WHERE Attendance = (
SELECT max(Attendance)
FROM public.T02AttendanceGame T2
WHERE T1.FKID = T2.FKID
);And and example where the latest date is used – substitute minimum both below and above to get alternate information
SELECT *
FROM public.T02AttendanceGame T1
WHERE gamedate = (
SELECT max(gamedate)
FROM public.T02AttendanceGame T2
WHERE T1.FKID = T2.FKID
);Substitute min to obtain minimum / earliest
For a very deep dive which I record here should I ever need to go into it in more details there is an excellent article by