In my 15+ years of web development, there are very few things I can say are unequivocally a good idea. It almost always does depend. Storing timestamps instead of booleans, however, is one of those things I can go out on a limb and say it doesn’t really depend all that much. You might as well timestamp it. There are pl...
  • Steeve@lemmy.caEnglish
    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.devEnglish
      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