1. 43
  1.  

  2. 10

    I’d go further to suggest an audit log of all changes.

    In my industry it’s required for most apps I work in, so I’m always surprised when I come across a scenario where data is changed and we don’t know who/when.

    1. 6

      This kind of thing makes me think applications should be using log-structured databases. You get history and auditing for free. The downsides are quite obvious though; few good libraries/databases for it, and they could get quite bloated.

      1. 5

        Not to mention that out of all the places I’ve worked in precisely none got temporal data right: https://en.wikipedia.org/wiki/Temporal_database#Using_three_axes:_valid_time,_decision_time,_and_transaction_time

        Throw in changing schemas that need to be kept in lock step with the data and you have an endless source of nightmares.

        What did you think the value of row A column B would be on date Y at date Z, noting that the schema split the column into three separate columns at dates Y.0 and Y.1?

        The correct answer is of course updating your resume.

        1. 1

          The correct answer is of course updating your resume.

          I LOLed at that. Thanks for making my morning!

    2. 12

      Ah, the world where storing a string vs a Boolean has “negligible” overhead. Whereas my inner bean-counter immediately starts hopping up and down yelling about heap allocations and locality of reference, and even if you store the time stamp as a double that’s 8 bytes vs 1 and do you know the size of a cache line…?

      Not saying one world is truer or better, of course! I wasn’t the one stating a practice that’s “almost always” right. I’m just amused at how people think their domains are all there is (like the web-dev folks who can’t believe SQLite is more widely deployed than Postgres.)

      1. 7

        If you are working with those restraints then it’s pretty obvious that his won’t work for you, but I think it’s very obvious that this post wasn’t aimed at you.

        1. 4

          Yeah. “Web development” features in the very first sentence of the post, and I’m sure the blog author knows their audience. Comments like the one you replied to don’t elevate the discussion whatsoever. (cf. @dbove’s, which is germane and an interesting discussion point.)

          1. 2

            there are tons of other web development languages where people would cringe at:

            is_published = new Date();
            if (is_published)
            

            that’s… not how types should work, really (I know that in Python it could also be None, but in this case I find this a horrible combo of naming+type)

            1. 2

              Sure, say if published_at != nil or whatever the equivalent idiomatic truth test in your language of choice is.

              The article maybe spends too much time on the ergonomics in JavaScript with the large code block, drawing attention away from its whole point: that you’re better off storing a nullable timestamp than a boolean in most cases.

              How you then test that field is kind of secondary to the data modelling concern. (“And you can pretty this up with a helper function called isPublished() which checks the published_at attribute.”)

          2. 4

            Mea culpa — by the time I reached the end I must’ve forgotten the first sentence, “ In my 15+ years of web development…” 😫

          3. 2

            A string? Is that a typo?

            1. 3

              If you’re using sqlite3, it is. https://www.sqlite.org/datatype3.html explains:

              2.2. Date and Time Datatype

              SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

              • TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
              • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
              • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

              Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

              And sure enough:

              > sqlite3 tmp.sqlite3
              SQLite version 3.32.3 2020-06-18 14:16:19
              Enter ".help" for usage hints.
              sqlite> create table asdf (id integer primary key, created_at timestamp);
              sqlite> insert into asdf (id, created_at) values (1, CURRENT_TIMESTAMP);
              sqlite> ^D
              > strings tmp.sqlite3
              SQLite format 3
              tableasdfasdf
              CREATE TABLE asdf (id integer primary key, created_at timestamp)
              32021-04-25 03:57:55
              > xxd tmp.sqlite3 | tail -2
              00001fe0: 0000 0000 0000 0000 1601 0300 3332 3032  ............3202
              00001ff0: 312d 3034 2d32 3520 3033 3a35 373a 3535  1-04-25 03:57:55
              
          4. 5

            Well, wait.

            What if you want to keep track of when your field became false, too? Would you then have both hidden_at and shown_at? What if you wanted to keep track of every time it was hidden or shown, not just the most recent happening of each?

            The proposal doesn’t quite extrapolate out cleanly, but, in fairness, I see the point that the “NULL or timestamp” communicates more information than “false or true”.