• Why would you not have just a single Status dimension which would include (as a minimum) StatusKey, StatusCode (the natural key, e.g. 'O' and 'C' in your example) and StatusDescription? To handle a Type 2 SCD you would then just need to add a row with a new StatusKey.

    The Case data would be stored in a Fact table surely rather than a Dimension? The Fact table would include a StatusKey column which would be a FK to the Status dimension.

    If the Status is used in more than one context in the same or different Fact tables then it becomes a role-playing dimension.

    Regards

    Lempster