Looking for some advice on which approach you experienced FM devs would take.
I'm making a backoffice for a startup that's leasing electric bikes to company employees.
Lease period is typical 3 or 4 years.
Once a bike has been handed over to the employee, they get a yearly service budget. Each year they can consume their budget, or what was left over from previous years.
Each service expense is written in a related record
Tables (relevant for this question):
- order: the actual order, with the start & end period for the lease
- service: each service record, related to an order record. This service record has a date & amount filed, that is needed for the further calculation below
Say an employee has a yearly service budget of €120, and has spent already €50.
In the first year, I would like to show this:
In e.g. the third year, it could become this:
- year 1 = the period starting on first day of lease, until (startday + 1 year - 1 day). So it's a floating window year, not a clean calendar year
- budget = the yearly amount + what was left over from previous period
- spent = sum of amount of service records found in the mentioned year
Since this table is very small, I'm kinda not really convinced I should make a related table for this.
I'm also considering adding 4 fields to the Order table, and have the Repetition set to the number of years the lease is running.
What would you recommend?
Is there even a third way (likely!) that would be better/faster/easier to code/more sustainable in long run?