1

I'm building a web application, in which users have their own set of Topics. Since Topics are recorded for reporting purposes, it's important that Topics used previously are not deleted, so that they can be referenced by future reports.

In my relational database, Topics are associated to users through a user_id. My idea is to, rather than users deleting a Topic, have it be orphaned by setting its user_id to null. This way, the Topic can be referenced by id in reports, but from the user's perspective, it is deleted.

Is this good practice? Should I instead move the Topics to a deleted_topics table?

Improve this question

1 Answer 1

Reset to default
8

I always favour making this kind of thing explicit. Rather than using user_id == null to mean "deleted", just having a deleted boolean and set that to true when appropriate. Just make sure your repository layer or similar knows about the flag and returns deleted topics only in the circumstances you actually want them.

In most cases, it wouldn't have two separate tables for these things - my experience is that you end up with a whole load of queries running across both tables.

Improve this answer
2
  • 1
    I have also often seen the deleted flag being a timestamp, which is set at the time of deletion and set to NULL for active records.
    – vlumi
    Jun 19, 2019 at 0:19
  • @vlumi that's a somewhat interesting approach. The WHERE deleted IS NOT NULL would still allow efficient indexing, although how that compares to a generic updated timestamp column (which would then contain the time of deletion, assuming deleted rows can't be updated) is debatable.
    – Kayaman
    Jun 19, 2019 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.