4 ms·
You might as well timestamp it (2021)
- kevinob11 4y agoI've frequently just done both, which I suppose is rather silly, but I like having a nicely named boolean field. I suppose I should probably just make those derived properties though.
- sedatk 4y agoIf you need to hold timestamp, hold `deleted` and `deleted_at` together. If you can't add a separate field, just add a log table. Don't optimize for fictional requirements that haven't surfaced, and don't add shortcuts. EDIT: Let me clarify my point here. A timestamp is just a timestamp. Giving a timestamp a role on signifying the state of an entity is a semantic shortcut, and semantic shortcuts lead to problems. I believe that DB structures should be as clear as possible. For example, it's impossible to know whether a timestamp is the authority on an entity's state by looking at a table structure. But a boolean field like "is_deleted" or "deleted" doesn't have that problem.
- echelon 4y agoMake deleted_at nullable and put an index on it. An audit log table isn't a bad idea, though. You can capture rich entity edit histories, attribution, etc.
- deleted 4y ago[deleted]
- manigandham 4y agoNo need for both fields. A nullable timestamp column works fine: NULL until the record/row is deleted, at which point it holds the timestamp of when it happened. You can derive a boolean "is it deleted" flag from that without adding another column to (mis)manage.
- sedatk 4y agoBecause they signify different things. For example, when you want to undo that delete operation, erasing timestamp would actually deprive you of the information that when it was deleted in the first place.
- nightpool 4y agoSure, but that just kicks the can one iteration down the road, and now it's impossible to delete the record a SECOND time without "depriving" yourself of that information. If you need an audit table, use an audit table, but being able to say "deleted_at > 1.month.ago" to grab all records deleted in the past month is super useful.
- manigandham 4y agoWhat is a delete operation that can be "undone"? What is the timestamp supposed to mean in that scenario? What happens when it's deleted again then? If you need a full record of state changes then just upgrade to an audit log, otherwise a nullable timestamp field is perfectly fine.
- sedatk 4y agoThat's actually my main point. I edited my top comment to clarify that. A timestamp doesn't implicate "state." A bool, on the other hand, unquestionably does.
- manigandham 4y agoAgain, the nullable nature of the column denotes both the state and when the state changed. You have more information in a single column, no need for both.
- sedatk 4y agoNullability on timestamp only implies that the timestamp is optional that's all. It doesn't imply that the timestamp decides the state of the entity.
- bob1029 4y agoNullable timestamp columns (64-bit integers) are what I prefer. I don't know how much of a performance improvement I'd get if I had a separate indexed bool column for tracking just the truthiness, but I do know that it probably wouldn't be enough to matter, even if SQL was a meaningful bottleneck. I really enjoy working with these as nullable DateTime in .NET as well: if (!record.CompletedUtc.HasValue) { /* completion logic */ } One source of truth also means only 1 thing to update each time. It makes many kinds of problems impossible.
- rektide 4y agoIndexing a 64 bit versus a 1 bit column actually sounds like a potentially really good win to make the index much much smaller, which could help loads with keeping it in hot memory or cache. On the other hand, on postgres & maybe others, one could perhaps index an expression deleted_at is not null, which would let one have the data shape they want while letting the index shape suit the typical access pattern.
- samwillis 4y agoLog tables are a super good idea in a lot of cases. And if your DB support it add a JSON column to that log table. Being able to dump either stricter data from our own logging or the reponcess from third part APIs in invaluable. You never know in advance what is going to be useful. If you start generating too much data, add a cron job to purge older items or scale back the logging. An example from our e-commerce platform, the "order/cart" tables have corresponding log table that log all changes, user interactions, and all requests/responses from Stripe during the checkout process. It's super useful both for tracking down bugs, but also confirming what happed when customers contact support.
- jrumbut 4y agoYeah I much prefer keeping the deleted column as a boolean and then having a log table. I've done the timestamps instead of boolean thing before and it leads to two problems: 1. What happens when the record is "un-deleted?" Now the timestamp is null again and history has been lost. It's not the end of the world but if you're asked to create an account history report it's an awkward conversation. "I thought we tracked when records were deleted..." 2. Similar to the end of the last problem, it creates an expectation that the times that changes are made are tracked but it doesn't quite live up to that expectation. I've learned that business people love a feature that works well (like a log table), they can deal with a feature not existing at all (like a boolean and no logging), but a half-working feature causes a lot of suffering. Just do logging right from the start if you are writing business software where the data and transaction volume are small compared to the value of the data.
- nightpool 4y ago> For example, it's impossible to know whether a timestamp is the authority on an entity's state by looking at a table structure This is just a matter of convention—if your codebase is consistent about it and there's no other option for "is this record deleted?", then yes it will be absolutely clear that "deleted_at" is the right column to use. What's actually worse for making the database structure clear is to have "vestigial" columns that store old data, like you proposed for "deleted" and "deleted_at". Now, if they disagree, which one should you trust? Maybe some code is using one, while some code is using the other. Having a single source-of-truth column—no matter what the name is—is always going to be better then having two columns that could get out of sync or disagree.
- 0x457 4y agoI think you should have log table anyway because if you need that information storing it as column(s) next to the rest it’s not going to work well. anyways i prefer to have nullable timeshamp columns instead of two like you suggested.
- grahamm 4y agoNever really felt the need but I suppose there has always been an updated_when field, so for something that is deleted the updated_when provides the time stamp.
- echelon 4y agoCreate an `updated_at` with an on update trigger to set it to NOW(). Your code will never have to explicitly remember to set it. Go further and create an integer `version` vector clock that gets set to its value plus one. You can see the edit frequency of individual entities and construct a simple key for cache invalidation.
- RajT88 4y agoI kind of understand why the example was set up this way, but I died inside a little when he wrote to the console log without the timestamp in the message.
- deleted 4y ago[deleted]
- crazygringo 4y agoI'm going to have to respectfully disagree on this one, just because of unintended behavior and complexity. Whether it's in a programming language or in a database... what is false? Is it false or null or 1970-01-01 00:00:00 UTC? What if different parts of the program/table use different versions of false? What happens when I combine two truthy values with a boolean operator -- for all combinations of values, what is the resulting type and is it the value even correct? (E.g. in SQL, NULL XOR 1672254005 is NULL, which is flat-out wrong if you're using NULL in place of FALSE, and it's easy to think of further examples.) There's so much research I'd have to do to determine that this would always be bug-free for a particular language/platform, and then I'd need to remember which languages/platforms it is safe for and which ones aren't.
- echelon 4y agoFor nullable timestamps, the timestamp itself is opaque. Having a value is indication that the thing happened. The time reading is orthogonal to that question. If your language has a tough or flexible time with falsy, I found your problem. You shouldn't even be in that headspace, and languages that put you there introduce so much mental overhead that they're not worth it. You shouldn't need to do research here. This should be a one and done solution from your toolbox that you can pull out at a moment's notice: "How do I handle record soft deletion and store some state around when it happened?" - zero effort, tried and true recipe borne of experience. The database and data stores drive your product. They're the heart and soul.
- crazygringo 4y agoSo the problem is with SQL, because NULL doesn't act the same as false, and boolean operators may give incorrect results if you expect them to? I'm not sure how to respond if you think people shouldn't be in the headspace of SQL.
- echelon 4y agoIn SQL you won't typically coerce null to false, though there are mechanisms to accomplish this (which can be useful for analytics, ETLs, etc). Presence is first class and is probably what you want in your business logic queries. If you let the SQL query predicates handle which records to select, I'm not sure I see the problem you originally outlined. Why do you care what value the timestamp has when the question is whether or not a record experienced an event.
- newbieuser 4y agoThis should definitely be implemented in projects where the use of the feature flag is common. When you go with boolean values, after a while you have a pile of data that you don't know what was done and when.
- simonw 4y agoThis was one of Luke Plant's YAGNI exceptions: https://lukeplant.me.uk/blog/posts/yagni-exceptions/ https://lukeplant.me.uk/blog/posts/yagni-exceptions/
- sgtnoodle 4y agoBut what timebase and precision? UTC in seconds? Unix time in nanoseconds? GPS time in 10us increments?
- jpollock 4y agoThe problem with the timestamp is that it keeps people from thinking that they've got an enum. For example, let's start with an object, which has a boolean flag "active". boolean is_active; We will always want to get a bit more advanced, and get into sub-states as it is brought up. We can't do that with a boolean. So we go to an enum. Enum current_state { not_configured; being_provisioned; active; disabled; deleted; } current_state state; We have an enum in hiding. Converting the boolean to a timestamp stops the conversion to an enum - we can't overload the presence of a timestamp into the multiple values in the enum. The timestamp is "what happened, when", an audit log/change history function. That should be kept separate from the rest of the database. Sometimes, there is a business reason to track a timestamp - charge the customer 30days after the item became "active". How to model that depends on your database and what is required - history table, convert the enum to struct, time_became_active, time_of_last_state_change, etc.
- TheRealPomax 4y agoIf you're gonna use an enum for a true/false field, you might as well timestamp it. If you need enums, you changed the game.
- jpollock 4y agoThe timestamp doesn't give you enough information to answer the questions it's typically going to be used for. It tells you "when", but not "who", and "what else at the same time". A change history is a better solution, which would track: * transaction requestor (user/job/etc) * transaction timestamp * diff encoding before/after state Then if someone asks "why were these customer's disabled", we can answer "the XYZ cronjob went rabid and tore them down." and then hopefully use the diffs to reverse the transactions.
- TheRealPomax 4y agoSure, but now you REALLY changed the game. If you're currently using a boolean, switch it to a timestamp. There are no real world downsides.
- valw 4y agoOr, you know, use a time-aware database like XTDB or Datomic.
- dang 4y agoDiscussed at the time: You might as well timestamp it - https://news.ycombinator.com/item?id=26922759 https://news.ycombinator.com/item?id=26922759 - April 2021 (198 comments)
- helmsb 4y agoI always store a timestamp and have an "is_deleted" flag on the business object that checks if the "deleted_timestamp" is null or depending on the use case, whether the timestamp is in the future for records that are to be deleted. I can also use the timestamp later if I want to move deleted records to a different table or database to improve performance depending on requirements.
- AJRF 4y ago> It’s like the exact opposite of YAGNI. You Ain’t Gonna Regret It? Pagni's? https://simonwillison.net/2021/Jul/1/pagnis/ https://simonwillison.net/2021/Jul/1/pagnis/
- _8j50 4y agoBecareful from a privacy perspective. "User bob is active" is different than "User bob is active since <specific timesamp>". That said, in forensics, random things storing timestamps is a big treasure trove. The most innocent logs or entries can be correlated with something else about a user to figure out what they did.
- Kim_Bruning 4y agoI'm going to steal YAGRI (You Ain't Gonna Regret It) for my own vocabulary.