I was just going to make this a comment but it is too long to just be a comment, and I think it is one viable solution though I would say that there are many solutions and it will depend on how large the tables are going to be, how frequently they are used, how they are used, etc...as to what is going to be the best for your situation.
In this case, I think having your Type, Status and SubStatus tables all be separate and having a table that links them together, seems to make sense.
Here is the code that I put together to make that happen for your data.
create table tblType
TypeID int identity(1,1) primary key clustered
Create table tblStatus
StatusID int identity(1,1) primary key clustered
Create table tblSubStatus
SubStatusID int identity(1,1) primary key clustered
create table StatusSubStatus
StatusSubStatusID int identity(1,1) primary key clustered
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Type
FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Status
FOREIGN KEY (StatusID) REFERENCES tblStatus(StatusID);
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_SubStatus
FOREIGN KEY (SubStatusID) REFERENCES StatusSubStatus(StatusSubStatusID);
insert into tblType
Values ('Type 1'), ('Type2'); --id 1, 2
insert into tblStatus
Values ('A'); --id 1
insert into tblSubStatus
Values ('X'), ('Y'), ('Z'); --id's 1, 2, 3 respectively
insert into StatusSubStatus
Values (1, 1, 1), (1, 1, 2), (2, 1, 3);
The advantage to this is that you get to keep your Status/Type tables from being large. The disadvantage is that you have a linking table that can get quite large. But it is a clean way to get all of this stuff together cleanly.
If you wanted to see all of the data for TypeID of 1 you would just do the query
Select TypeName, StatusName, SubStatusName
from StatusSubStatus sss
join tblType tt on sss.TypeID = tt.TypeID
join tblStatus ts on sss.StatusID = ts.StatusID
join tblSubStatus tss on tss.SubStatusID = sss.SubStatusID
Where tt.TypeID = 1;
There are many solutions to making this work, this is one that I like but it will certainly depend on your situation as to whether this is the best one for you or not.