• Steeve@lemmy.ca
    link
    fedilink
    English
    arrow-up
    2
    ·
    1 year ago

    But if it can be disabled we’d also need a disabled_date, however this implies that the state can switch from enabled to disabled and vice versa an infinite number of time, so we should create n*2 fields (enabled_date_1, disabled_date_1, …, enabled_date_n, disabled_date_n) where n is the maximum amount of state switches/2. Of course we’ll have to implement stream logging of events into a database, or at least some sort of counter, to determine the value of n, and then dynamically create new fields as needed.

    Problem solved!

    • towerful@programming.dev
      link
      fedilink
      English
      arrow-up
      2
      ·
      1 year ago

      I think having an enabled_at field as nullable timestamp is enough.
      If it’s present, it’s enabled. If it’s null, it’s disabled.
      It’s a Boolean with context.

      If you really need to track the history of a record being enabled/disabled, I’d suggest this should be in another table. With postgres (not sure if it’s all DBs) you could create a trigger that when a record’s enabled_at field is updated, it creates a record in the log table with a from state, a to state, a timestamp, even a role/user.

      That way, you could then extract the history of that record if required.
      Tbh, if using postgres, you could just make a logging table that stores a JSON of the entire old record, and a JSON of the entire new record.
      Would let you rewind the history of a record, see who did what, etc.

      Saves having an enabled and an enabled_at where there are potentially multiple sources of truth, or faffing around with arrays, multiple fields, over-pulling data