Why can't databases just remember stuff we delete, like a trash can?
YAGRI: You are gonna read it
131–140 of 161 posts
Re: YAGRI: You are gonna read it
#132Earlier quoted context omitted.
So what? I've worked places with lots of regulation. Part of every development job is learning the product domain. In that case devs become comfortable with reading standard/law/regulations and anticipating when software implementation might interact with the areas covered. Sure there were people who's job was to offload as much compliance work from everyone else; by turning it into internal requirements, participati…
> So what? Think before you act. The machine has no brain. Use yours. > Part of every development job is learning the product domain. Yes. > In that case devs become comfortable with reading standard/law/regulations and anticipating when software implementation might interact with the areas covered. This is what I'm saying, too. A developer needs to think whether what they are doing is OK by the regulation they're fl…
Oh, I thought you were disagreeing with hamandcheese's point that every little decision doesn't need to go through a product owner before anything happens.
Re: YAGRI: You are gonna read it
#133Earlier quoted context omitted.
> So what? Think before you act. The machine has no brain. Use yours. > Part of every development job is learning the product domain. Yes. > In that case devs become comfortable with reading standard/law/regulations and anticipating when software implementation might interact with the areas covered. This is what I'm saying, too. A developer needs to think whether what they are doing is OK by the regulation they're fl…
Oh, I thought you were disagreeing with hamandcheese's point that every little decision doesn't need to go through a product owner before anything happens.
No, not at all. by "the book", I meant regulations, not the management. :)
Re: YAGRI: You are gonna read it
#134Why can't databases just remember stuff we delete, like a trash can?
Assuming you're serious, there are two main reasons. You want to regain storage space after you delete things, and sometimes you want to actually delete things (e.g. to be in compliance with regulations).
Re: YAGRI: You are gonna read it
#135The problem with updated_at and updated_by is that a given record could experience multiple updates by multiple people at multiple times, and you'd only have visibility into the most recent.
The logical conclusion here is to log the updates (and creations and deletions and undeletions and such) themselves:
CREATE TABLE foo_log (id,
foo_id,
whodunnit,
action,
performed_at,
column_1,
column_2,
-- ...
column_n);
This is a pretty typical approach for things like bookkeeping and inventory management (though usually those are tracking the deltas between the old and new states, instead of recording the updated states directly as the above example would imply).
Re: YAGRI: You are gonna read it
#136Just curious, how do people feel about this general style of soft deletes currently? Do people still use these in production or prefer to just delete fully or alternatively move deleted rows to a separate tables / schema? I find the complexity to still feel awkward enough that makes me wonder if deleted_at is worth it. Maybe there are better patterns out there to make this cleaner like triggers to prevent deletion, s…
In our product, we have different strategies depending on the requirements. Sometimes, we just delete. Sometimes, we do soft delete with timestamps. Sometimes, we have a history table with or without versioned entities. Sometimes, we have versions in the table. Sometimes, we have an audit log. Sometimes, we use event sourcing (although everyone in the team hates it ;-)
Re: YAGRI: You are gonna read it
#137I don't get why all of the big RDBMSes (PostgreSQL, MariaDB/MySQL, SQL Server, Oracle, ...) don't seem to have built in support for soft deletes up front and center? CREATE TABLE ... WITH SOFT DELETES Where the regular DELETE wouldn't get rid of the data for real but rather you could query the deleted records as well, probably have timestamps for everything as a built in low level feature, vs having to handle this wi…
Oracle has this already. SELECT ... AS OF timestamp.
It needs to be enabled of course and it's not free.
Re: YAGRI: You are gonna read it
#138I don't get why all of the big RDBMSes (PostgreSQL, MariaDB/MySQL, SQL Server, Oracle, ...) don't seem to have built in support for soft deletes up front and center? CREATE TABLE ... WITH SOFT DELETES Where the regular DELETE wouldn't get rid of the data for real but rather you could query the deleted records as well, probably have timestamps for everything as a built in low level feature, vs having to handle this wi…
Temporal tables in SQL server fit this use-case[0], I think. 0: https://learn.microsoft.com/en-us/sql/relational-databases/t...
Oracle has flashback queries (SELECT ... AS OF timestamp).
It's one of these things that are available but most people ignore it and implement it manually with created_at updated_at deleted_at columns etc. I think one reason for this is lack of support in ORMs and lack of standardization between RDBMSes.
Re: YAGRI: You are gonna read it
#139These are not decisions that should be taken solely by whoever is programming the backend. They need to be surfaced to the product owner to decide. There may very well be reasons pieces of data should not be stored. And all of this adds complexity, more things to go wrong. If the product owner wants to start tracking every change and by who, that can completely change your database requirements. So have that conversa…
This entirely depends on the company culture. I worked in teams where every small decision is in the hand of the PO and I've worked in teams where a software engineer is a respected professional enabled to make their own technical decisions. I found the second option to create higher quality software faster. Also not sure what you mean by additional effort? Created_at, updated_at or soft-deletes are part of most prop…
But what if it's not a technical decision? What if there are legal implications around data retention that it's not your job to be aware of?
I've been parts of teams where features had to be totally thrown out and rebuilt because developers made big assumptions that turned out to be wrong, because they didn't think it was worth it to check with the product owner. Because they assumed it was only a "technical decision", or they assumed they understood the customer needs despite never actually asking the customer.
This doesn't mean checking with product around each line of your code, obviously. But deciding what information gets stored in the database, what level of event tracking you do, whether deletes are hard or soft -- these have massive product implications, and potentially legal ones.
And it is additional effort. Now you have to write tests for all those things. Are the timestamps being stored correctly? Are the permission bits being stored correctly? Is "created_by" coming from the right user? Are we sure a malicious user can't spoof that? Do we care? Is "updated_at" actually being updated on every row change? But are we making sure "updated_at" is not getting changed when we import data from a separate table? How often do we remove soft-deleted data in order to comply with privacy policies and regulations, and with what cron job, and who maintains that? Where do alerts go if the cron job fails? What happens if that employee leaves? I could go on and on and on.
So that's what I mean by additional effort. It's not "around a few seconds". Because it's not just a technical question, it's a product one. It's a proper feature that needs to be properly defined and properly scoped out and properly tested.
Re: YAGRI: You are gonna read it
#140These are not decisions that should be taken solely by whoever is programming the backend. They need to be surfaced to the product owner to decide. There may very well be reasons pieces of data should not be stored. And all of this adds complexity, more things to go wrong. If the product owner wants to start tracking every change and by who, that can completely change your database requirements. So have that conversa…
> And all of this adds complexity, more things to go wrong.
This argument is one of the reason why a backend engineer could just add the created_at and updated_at fields without asking a product owner.
It doesn't make much sense from the pure product perspective, so the standard answer will be "no, let's add them when we have a real case they're needed". The product I'm inheriting right now misses these fields on half of the tables. Except when you really want the data, it won't be there as you're not going back in time.
Trying to convince someone that it's worth it will also give the impression they're optional, when you already decided you need them. So at the end of the day, it's your responsibility as an engineer to do what's required, without punting it to non technical stakeholders to push your back.
I also wouldn't ask a product manager if they think table schema should be orthogonal.
Now keeping or not IPs or user IDs in a table is a whole different story and requires a lot more consulting, and not just with the PO.