AnsweredAssumed Answered

Repeating fields vs related table for small data set?

Question asked by wimmmmm on Oct 26, 2018
Latest reply on Oct 28, 2018 by wimmmmm

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:

YearBudgetSpentAvailable
1€120€50€70

 

In e.g. the third year, it could become this:

YearBudgetSpentAvailable
1€120€50€70
2€190€165€25
3€145

 

Detail clarification:

  • 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?

Outcomes