5 ms·
The 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 fla
by jpollock 4y ago
The 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.
- idealmedtech 4y agoI recently converted some generation code to use a plan, so you can make a plan and then execute the plan. Architecting your code this way gives you things like dry runs and rollbacks for free; I feel like diff storage is in a similar realm of usefulness.
- maxbond 4y agoTimestamps are a huge observability win relative to the amount of effort they take. "When" will give you hints to pull the other information out of logs etc. What you're proposing is better, but is a heavier lift; if you have capacity for that, awesome, if you don't, timestamps go a long way, take very little time to implement, and not having any information will really sting.
- bwilliams 4y agoI think this may miss the point of the article, which is pointing out that you can get a lot of value for very little effort by using a timestamp instead of a boolean. I don't think it's intention is to replace a complete change history/audit log implementation, which would require a significant amount more time/effort to implement.
- horsawlarway 4y agoSure - this is objectively better for tracking/auditing, but... now we're getting back into trade-off territory. Where is that change history stored? How much data are the diffs generating? Are we deriving the final state from the log, or can the log the and record disagree? Basically - this is now back in the "What is this history buying us?" realm, not in the "easy win" realm. In some cases it can be absolutely worth it. But probably not all.
- cryptonector 4y agoValidity periods are superior to an is_active/is_expired boolean, as you can set an expiration ahead of time without any write transactions needed at expiration time to make the expiration happen. For your example, however, I'd be tempted to have an enum as you suggest and associated history/audit table that has timestamps, mainly because there's no sense in setting timestamps into the future for the first two enum values. Still, I'd keep a timestamp for inactivation for the reason I gave above.
- Retric 4y agoExpiration times still need a status flag for cleanup. What should the state be vs what is the current state vs when should the state change.
- cryptonector 4y agoWhy repeat yourself? If you can have an expression-valued column, do that. For expiration it's essential that it be able to happen at the scheduled time (when it is scheduled anyways) without having to execute a transaction at that time.
- Retric 4y agoIt’s fine for a DB be the source of truth as long as it’s the ultimate arbiter of what’s going on, but any kind of real world event has dependencies outside of the databases control. A digital rental works as a pure time stamp in a DB, a car rental however can’t be rented out to the next person simply because the last persons rental period ended as you might not physically have the car etc.
- Dylan16807 4y agoThe return of the car should have its own entry, separate from when the rental expires. It doesn't mean you shouldn't have a an expiration field.
- nine_k 4y agoThis enum is a list of states. Immediately should one think about a proper tool to handle it, which usually is a finite state machine. FSMs are somehow underappreciated outside of comm protocols and GUIs. They are an adequate tool for quite a few "backend" or "data representation" tasks though. What the original post suggests is to timestamp the latest state transition. The approach is trivially obvious if you explicitly track state transitions as a part of the logic of your FSM.
- jpollock 4y agoThat's a great way of phrasing it. A boolean can be a FSM in hiding. Tracking FSM transitions is the change history/transaction log, isn't it? It would be: {timestamp, old_state, event, new_state} If we convert booleans to timestamps for the FSM, I end up with: timestamp not_configured; timestamp being_provisioned; timestamp active; timestamp disabled; timestamp deleted; Which would cause confusion when there are multiple FSMs in the same database record. We could isolate each FSM into its own column. It still makes it kind-of hard to decide what's the current state. The set of timestamps needs to be considered and sorted, going from field to {field_name, timestamp} and then sorting by timestamp and extracting the max. It would make "select count(*) from customers where current_state = 'active'" brittle - the addition of a new state would require all existing queries to change.
- dools 4y agoI can't see how this is simpler or more effective than having 5 state fields, each with a timestamp. I guess it would make writing the query slightly easier, but you can put your clause for any given state into a builder function anyway. What's the motivation for having an enum then holding in some other system a history of when that enum changed?