database_design_question.md

Have a data modeling question I'd be interested in your take on (if you have time):

One of the entities in our database is the Case (as in, legal case). We send a Case to a lawyer, and they can indicate if they are interested in talking to the case or not (we call this "precleared").

Then the lawyer reaches out to the client, and the lawyer can decide if they want to represent the Case or not. (we refer to this as "referred" (or not))

Then the lawyer sends a retainer agreement to the client, and the client signs that agreement (or not). (we refer to this as the Case is "retained" (or not))

If a Case is rejected at any stage, we want to capture why the Case was rejected.

We see two possible ways to represent this:

Approach 1

The first approach (entity model above) is to have an enum property caseStage, boolean properties for each of those yes/no points, and string properties for reason why at each of those points.

When a Case starts out, the stage is set to awaiting-preclearance and all the boolean properties are NULL. When the Case is precleared (approved), the precleared property is set to true and caseStage is updated to precleared. If rejected at that stage, the precleared property is set to false, caseStage is updated to closed, and we add the reason why in the preclearedFailedReason property. (And so on with referral and retained)

Approach 2

The second approach (entity model above) is to have an enum property caseStage, and a property for failed reason (failedReason). When a Case starts out, caseStage is set to awaiting-preclearance. When the Case is precleared (approved), the caseStage is updated to precleared. If rejected at that stage, the caseStage is updated to closed, and we add the reason why in the failedReason property. (edited) Which of those two do you think is better database design? Or am I missed the best approach altogether?