Deeply nested one to many relationships in database design

Question

I am designing a database model that is based on a risk analysis table which is similar to the following (apologies for the example quality):

Basically, the user should be able to create such tables to do risk analysis through the UI. From the example above I hope it becomes clear that there can be multiple main steps, which can have multiple sub steps, which in turn can have multiple risks, which can have multiple measures.

A quick draft of a model that would do the job is:

RiskAnalysis:
id: number
name: string

MainStep:
id: number
description: string
risk_analysis: fk to RiskAnalysis

SubStep:
id: number
description: string
main_step: fk to MainStep

Risk:
id: number
description: string
sub_step: fk to SubStep

Measure:
id: number
description: string
risk: fk to Risk

However, my concern is that this design may be terrible for performance and querying.

An alternative model would be:

RiskAnalysis:
id: number
name: string

RiskAnalysisEntry:
id: number
main_step: string
sub_step: string
risk: string
measure: string
risk_analysis: fk to RiskAnalysis

While this reduces complexity at the database level, data is duplicated potentially resulting in more complexity in the application layer.

A minor improvement on this design may be the following:

RiskAnalysis:
id: number
name: string

RiskAnalysisEntry:
id: number
name: string
risk_analysis: fk to RiskAnalysis
main_step: fk to MainStep
sub_step: fk to SubStep
risk: fk to Risk
measure: fk to Measure

MainStep:
id: number
description: string

SubStep:
id: number
description: string

Risk:
id: number
description: string

Measure:
id: number
description: string

The improvement here would be that we only "duplicate" references (foreign keys) instead of values.

Unfortunately, I am unable to figure out which solution best fits my needs. Therefore my question is:

Would you advise against using the deeply nested model presented as the first option? And if so, what would be the most elegant way to deal with deeply nested data? Does one of my other designs qualify or are there beter options to consider?

Answer 1

This is a common design pattern. The usual example is a Bill of Materials.

In your case, you can just have one table.

Step
----
Step ID
Step Name
Parent Step ID

The risk analysis would have a null Parent Step ID.

The main step would have a Parent Step ID pointing to the risk analysis.

And so on, for as many levels deep as you need.