I have a database design question. I have a table for "actions" (tasks) and a table for "logs", which is intervals of work done on an "action". The models pseudocoded look like this:

Action {
  id: string
  name: string
  status: string
}
Log {
  startTime: date
  stopTime: date
  action: ActionId (relation to Action)
}

I want a user to see a list of tasks and, separately, an activity log. Unfortunately, I'm restricted from deleting actions (I get a

violates foreign key constraint

error) until I've deleted all logs that have a foreign key of that actions ID, which I don't want to do because just because a user deletes an action doesn't mean I want it to disappear from the activity log. What are some approaches/solution to this problem? Appreciate any help 🙂

Seems like you just want to write your action info into the log if it can disappear. It does no good to have the id if it is deleted.

If you want to have the ActionId, just add it as a normal column and not a column with a reference to the Action table

Well the problem is I want the name and status from the action to be saved/associated with the Log so that the info still shows up in the log. I could write that with the log but I found an answer somewhere else which is to just use soft deletes by adding a deleted property to the Actions