We have an Accounts table in a Postgres DB that has a unique index on
product. Users can have many accounts, there's no product table. Occasionally users will create an account accidentally, and we will need to add that account back to their original
User record, after they've collected some information on it that we can't just toss.
Because of the unique index on
user_id, we need to do some serious linking/unlinking on the
Account record, and record data from one account record into a JSONB field on the other record before connecting it to the proper
We have a
deleted_at column on the Accounts table that is moderately used, and I've proposed adding it to the
(user_id, product_id) index. That way, any record that has a
deleted_at for a given
user_id, product_id is the "active" account, and accounts can be marked deprecated/inactive by just setting a
This would require some re-writes in our lookups, but they wouldn't necessarily be extensive, because our ORM provides a scoping feature we can apply to all queries.
I've gotten pushback on the idea, but nothing related to performance impact or technical drawbacks, only statements along the lines of "Do people really do that?" or "I've never seen that before." I swear I have seen companies do this, but it would be hard to provide proof.
Can anyone provide a performance or modeling criticism of this approach? Has anyone seen this sort of approach? If not, have you ever fixed this sort of problem?