|I'm working on a personal app to help me better manage my family's car maintenance schedules. These are two of the tables being used and their relationship: Schema. For a given car, a schedule is created where at least one row is added to the
service_schedules table and multiple rows are added to the
schedule_intervals table depending on the options chosen.
One example would be if a "rotate tires every 7500 miles" schedule was created, one row would be added to the
service_schedules table and 33 related rows would be added to the
schedule_intervals table, one for each of the 7500 mile intervals up to 250000 miles.
Another example would be if a "change oil every 25000 miles or 12 months" schedule was created, one 'mileage' row would be added to the
service_schedules table and 10 related rows added to the
schedule_intervals table, one for each of the 25000 mile intervals up to 250000 miles. A 'time' row would also be added to the
service_schedules table and 15 related rows added to the
schedule_intervals table, one for each year up to 15 years.
With this schema, I can easily do 'past due services' and 'upcoming services' queries. For example, I can find all upcoming services for vehicle 4 with current mileage of 163451 using:
SELECT * FROM schedule_intervals WHERE schedule_id IN (SELECT _id FROM service_schedules WHERE vehicle_id = 4) AND (163451 < mileage OR 1621540799076 < date)For vehicle 4, it has six service schedules for things like oil/filter (mileage or date), tires (mileage), spark plugs (mileage), transmission (mileage), etc, all of which create 155 rows in the
schedule_intervals table. Using the above query, 114 rows are returned for any upcoming services. The issue I'd like to resolve, if possible, is to only show one of each service type (one of mileage and one of date). In other words, instead of showing all of the oil/filter services that are due (175000, 200000, 225000, and 250000, April 2022, April 2023, April 2024, ..., April 2036), I'd like to just show the next one of each type (175000, April 2022), as all other ones past that are irrelevant.
At this point, I don't know if I need to modify either of the two tables, and/or add a bit more complexity to the query.
Thoughts or ideas?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles