Regardless of the constraint on the key, the design fact remains that deleting a user record that might, for example, have associated transaction data (like subscription payments) is a little complex.

You don't want to cascade that deletion to a record of credit card charges, but you also need to make sure that all queries respect that the user record might now be deleted - ie make it an outer-join.

It's far more robust to add an active/inactive field.

The longer an organization has been around, the more interconnected the database is, and the more consequent changes are needed to accommodate a core database change.

I would be surprised if many many organizations have some hack like this under the covers.

Do active/inactive fields comply with data privacy laws like those in California and the EU?

IANAL, but if you anonymize (not just pseudonymize) the PII from the user table (and others) I think so, yeah.

In that case it seems simpler to introduce an IsDeleted flag than to have a convention that 1000 goes on the end of the name.

Someone wanted to get this done without waiting on the DBA team so they rolled some implicit schema instead.

This reminds me of a good use case for the Laravel Soft Deletes: https://laravel.com/docs/7.x/eloquent#soft-deleting.

love this feature so much

I'm learning about database design, and I'm learning that this might not be that easy if the relationships between users and other data on the site are ill-defined. There may be multiple tables in their design that will require knowledge of that flag, and it might legitimately be way easier to just add junk to the end of the user account than it is to introduce a new flag.

Sure, but that just covers up the lie better. Maybe a flag "ToBeDeleted" and any stilled logged in sessions see account is scheduled for deletion.

> it goes all the way down to foreign key constraints introduced by a well-meaning but inexperienced developer that unnecessarily couple the accounts table to many other records.

You make it sound like developers doing things the wrong way is the exception instead of the norm.

Good developers don't, but every place I've worked at has a few chunks of the software by people who didn't know or care enough to do things the right way.

> I’d bet (a small amount of) money they have no ability to delete accounts at all

Probably not. I bet that goes for so very many organizations. It is far easier to reinstate an accidentally (user) deleted account if it is merely marked as deleted than if it were truly deleted.

Now, how NYT is handling this is ... well, it's bad.

probably the fact that a good portion account deletes are users doing something they want to undo in the next few days. Soft deletes make this much easier, then going back and deleting anything older than X days is simpler than expensive customer support tickets of "how do i login it says my account is deleted"

Why not just anonymize the data instead? Not using foreign key constraint just for the sake of GDPR sounds weird.

That and why not allow null for the foreign key constraint and set it to nullify upon deletion? Or indeed, anonymise data.

I have used that pattern in my apps and found it works well. But then I've watched video from respected DB experts, that I learn a great deal from, where they practically beg you to stop using nullable columns.

So I'm torn, because I think there may just be a major problem I've not yet grown my apps big enough to suffer. Anyone have thoughts either way?

Any particular videos you'd recommend?

> Not using foreign key constraint just for the sake of GDPR sounds weird.

That isn't what he said.

He said their inability to delete an account is due to poor design of their data schema by a well intentioned developer.