sqldatabase-designdatatablethresholdrisk-analysis

Threshold SQL Table


I have a screenshot of a GUI and need to make it into a SQL table. What would be the best way of going about this?

enter image description here


Solution

  • There are 3 distinct entities in that screenshot, so define 3 tables.

    Create Table ProbabilityThresholds(
      Name varchar (50),
      MinValue int,
      MaxValue int
    )
    
    Create Table RisksMatrix (
      Probability varchar(50),
      MinorSeverity varchar(50),
      ModerateSeverity varchar(50),
      MajorSeverity varchar(50),
      ExtremeSeverity varchar(50),
      SortOrder int
     )
    
    Create Table RiskToCategory (
      Risk varchar(50),
      Category varchar(50),
      SortOrder int
    )
    

    Then Query the 3 tables in multiple result sets (either as 3 direct queries or as a stored procedure). The as expression sets a column alias, and the SortOrder column is populated to sort in the desired layout, but is not selected in the results:

    Select Name
    , MinValue
    , MaxValue 
    from ProbabilityThresholds 
    order by MinValue;
    
    Select  
          Probability 
          ,MinorSeverity as "Minor Harm",
          ,ModerateSeverity as "Moderate Harm",
          ,MajorSeverity as "Major Harm",
          ,ExtremeSeverity as "Extreme Harm",
    from RisksMatrix  
    order by SortOrder ;
    
    Select 
        Risk
       ,Category 
    from RiskToCategory 
    order by SortOrder;