Extend database model for recurring expenditures

[forgetaboutit]

It should allow the following occurance patterns:

Daily: [every x days] [Starts: Date] [Ends: Date | Number of occurrences | Never] Every weekday: [Starts] [Ends] Weekly: [every x weeks] [On weekdays: Set Weekday] [Starts] [Ends] Monthly: [every x months] [Repeat by: DayOfMonth x | DayOfWeek x] [Starts] [Ends] Yearly: [every x years] [Starts] [Ends]

Rough Haskell translation:

data Date
data PosInt

data End = Date | NumOfOccurrences PosInt | Never
data MonthlyRepeat = DayOfMonth PosInt | DayOfWeek PosInt
data RecurrenceRange = RecurrenceRange {
    start :: Date
  , end   :: End }

data DailyRecurrence = DailyRecurrence {
    every :: PosInt }

data WeeklyRecurrence = WeeklyRecurrence {
    every      :: PosInt
  , onWeekdays :: Set Weekday }

data MonthlyRecurrence = MonthlyRecurrence {
    every    :: PosInt
  , repeatBy :: MonthlyRepeat }

data YearlyRecurrence = YearlyRecurrence {
    every :: PosInt }

data RecurrenceType = Daily DailyRecurrence
  | EveryWeekday 
  | Weekly WeeklyRecurrence
  | Monthly MonthlyRecurrence
  | Yearly YearlyRecurrence

data Recurrence = Recurrence {
    type    :: RecurrenceType
  , range   :: RecurrenceRange }

Full denormalization could look like this:

[Id: Int]
[Type: RecurrenceType] -- [1..5], ID per type
[StartDate: Date]
[EndMaxNum: Int]
[EndDate: Date] -- Both End fields NULL: Never
[Every: Int] -- Unset for EveryWeekday
[WeeklyMonday: Bool] -- only set for WeeklyRecurrence
[WeeklyTuesday: Bool] -- only set for WeeklyRecurrence
[WeeklyWednesday: Bool] -- only set for WeeklyRecurrence
[WeeklyThursday: Bool] -- only set for WeeklyRecurrence
[WeeklyFriday: Bool] -- only set for WeeklyRecurrence
[WeeklySaturday: Bool] -- only set for WeeklyRecurrence
[WeeklySunday: Bool] -- only set for WeeklyRecurrence
[MonthlyDayOfMonth: Int]
[MonthlyDayOfWeek: Int] -- Only for MonthlyRecurrence, mutually exclusive with MonthlyDayOfMonth

Having all the fields may seem wasteful and unstructured. However, querying recurrences of different types would be a lot more difficult to fetch efficiently and conveniently. Joining or unioning them would give a result similar to the given format anyway.

The weekly fields could be represented as a single array field as well. According to the Hasql.Postgres docs, Postgres arrays are supported.