Django - Create a model that holds aggregated Foreign Key values

Question

I have a Django Model defined as a ServiceEvent, and another defined as Part.

class ServiceEvent(Model):
    date = DateTimeField(null=False, blank=False, db_column='date', 
                         default=datetime.now())
    vehicle = ForeignKey(Vehicle, on_delete=models.CASCADE)
    description = TextField(null=True, blank=False, db_column='description')
    
    # The contents of the notifications to create for this event.
    notification_contents = TextField(null=True, blank=False, 
                                      db_column='notification_contents')
    
    # The mileage the event should occur.
    target_mileage = DecimalField(max_digits=10, decimal_places=2, 
                                  null=True, blank=False, 
                                  db_column='target_mileage')
    
    # The mileage at the time of the event.
    event_mileage = DecimalField(max_digits=10, decimal_places=2, 
                                 null=True, blank=False, 
                                 db_column='event_mileage')

class Part(Model):
    part_number = CharField(max_length=100, null=False, blank=False, 
                            db_column='part_number')
    description = CharField(max_length=100, null=False, blank=False, 
                            db_column='description')
    price = DecimalField(max_digits=10, decimal_places=2, null=False, 
                         db_column='price')
    service_event = ForeignKey(ServiceEvent, on_delete=models.CASCADE)

There can be multiple Parts per ServiceEvent.

What is a way to store a column in the ServiceEvent table that contains the price of all of the parts added up that belong to a given event?

I would like to do this for efficiency.

I want to be able to do something along the lines of:

price = service_event_obj.total_price

I'm using SQLite.

Answer 1

What is a way to store a column in the ServiceEvent table that contains the price of all of the parts added up that belong to a given event?

You could do this with a TRIGGER BUT there is likely no need as the sum of the prices could be generated/calculated/derived from existing data when extracting data.

Demonstration

Here's a demonstration based loosely upon the schema according to your code:-

/* Cleanup test environment (just in case)*/
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS serviceevent;
DROP TABLE IF EXISTS vehicle;
CREATE TABLE IF NOT EXISTS vehicle (
    description TEXT PRIMARY KEY, 
    otherData TEXT DEFAULT 'blah'
);
CREATE TABLE IF NOT EXISTS serviceevent (
    date TEXT PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
    description TEXT REFERENCES vehicle(description) ON DELETE CASCADE ON UPDATE CASCADE,
    notification_contents TEXT,
    target_mileage REAL,
    event_mileage REAL

);
CREATE TABLE IF NOT EXISTS part (
    part_number TEXT PRIMARY KEY,
    description TEXT,
    price REAL,
    service_event TEXT REFERENCES serviceevent ON DELETE CASCADE ON  UPDATE CASCADE
);
/* Add some vehicles */
INSERT INTO vehicle (description) VALUES ('CAR001'),('CAR002'),('CAR003');
/* Add some serviceevents for the vehicles */
INSERT INTO serviceevent VALUES
    ('2023-03-14 10:20:00','CAR001','10k Service',10000.00,9100.01),
    ('2024-03-13 11:00:00','CAR001','20k Service',20000.00,19100.01),
    ('2023-03-15 14:20:00','CAR002','10k Service',10000.00,9100.02),
    ('2024-03-16 15:00:00','CAR002','20k Service',20000.00,19100.02),
    ('2023-03-17 18:20:00','CAR003','10k Service',10000.00,9100.01),
    ('2024-03-18 19:00:00','CAR003','20k Service',20000.00,19100.01)
;
/* add some parts to service events (not all for brevity)*/
INSERT INTO part VALUES
    /* 10k service for Car001 (i.e. serviceevent '2023-03-14 10:20:00')*/
    ('p001','Air Filter',25.68,'2023-03-14 10:20:00'),
    ('p002','Spark Plug Set',15.20,'2023-03-14 10:20:00'),
    ('p003','OIL (5L)',32.32,'2023-03-14 10:20:00'),
    /* 10k service for Car002 (i.e. serviceevent '2023-03-15 14:20:00)*/
    /* Note just the 2 parts */
    ('p004','Air Filter',25.68,'2023-03-15 14:20:00'),
    ('p005','Spark Plug Set',15.20,'2023-03-15 14:20:00')
;
SELECT 
    vehicle.*, /* include ALL of the vehicle columns in the output */
    serviceevent.*, /* include ALL of the serviceevent columns in the output */
    
    /* Use Aggregate functions to show all the parts and the sum of the prices */
    group_concat(part.description),sum(part.price) AS total_parts_price
FROM vehicle 
    JOIN serviceevent ON vehicle.description = serviceevent.description
    JOIN part ON part.service_event = serviceevent.date
GROUP BY service_event /* group the output insto sets per service event (to which the aggregate functions apply) */
;
/* Cleanup test environment (After run)*/
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS serviceevent;
DROP TABLE IF EXISTS vehicle;

This results in:-

• The 10k service for CAR001 done on 14/03/2023 @ 10:20 had/used parts (Air Filter, Oil Filter and Oil) that totalled $73.20
• the 10k service for Car002 done on 15/03/2023 @ 14:20 had/used parts (Air Filter and Oil Filter) that totalled $40.88
• i.e. the expected results WITHOUT the need for an extra column

Extra

You could even do something along the lines of the following:-

WITH cte AS (
    SELECT 
        'Service for '||vehicle.description||
        ' done on '||serviceevent.date||
        ' re '||serviceevent.notification_contents||' ('||serviceevent.target_mileage||' miles)'||
        ' @ '||serviceevent.event_mileage||' miles.' AS printline,
        1 AS printorder,
        serviceevent.date
    FROM vehicle
        JOIN serviceevent ON vehicle.description = serviceevent.description
    UNION ALL SELECT
        '\t'||'Part '||part.description||' Cost $'||part.price,
        2,
        serviceevent.date
    FROM serviceevent JOIN part ON part.service_event = serviceevent.date
    UNION ALL SELECT
        '\t\tTotal Parts Cost $'||sum(part.price),3,part.service_event
    FROM serviceevent JOIN PART ON part.service_event = serviceevent.date
    GROUP BY service_event
)
SELECT printline FROM cte ORDER BY date,printorder
;

Which would produce a crude invoices detailing the parts and the total:-

• again showing that there would be little need for a column to store a value that can be derived from the underlying data.