Three tables joined by a many-to-many relationship?

Question

I'm working on creating a db model for the following requirements:

I have many Types that have many different Statuses.

Depending on the Type and Status, the Status can have certain SubStatuses.

So let's say I have

Type 1 with Status A, that has SubStatuses X and Y.

I can also have

Type 2 with Status A, that has Substatus Z

I created a Many-to-Many relationship between Type and Status and a Many-to-Many relationship between Status and SubStatus. Am I going about this the right way? Is there a better design?

Answer 1

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
    ,TypeName varchar(20)
);
GO
Create table tblStatus
(
    StatusID int identity(1,1) primary key clustered
    ,StatusName varchar(20)
);
GO
Create table tblSubStatus
(
    SubStatusID int identity(1,1) primary key clustered
    ,SubStatusName varchar(20)
);
GO
create table StatusSubStatus
(
    StatusSubStatusID int identity(1,1) primary key clustered
    ,TypeID int
    ,StatusID int
    ,SubStatusID int
);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Type 
FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Status 
FOREIGN KEY (StatusID) REFERENCES tblStatus(StatusID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_SubStatus 
FOREIGN KEY (SubStatusID) REFERENCES StatusSubStatus(StatusSubStatusID);
GO
insert into tblType 
Values ('Type 1'), ('Type2'); --id 1, 2
GO
insert into tblStatus 
Values ('A'); --id 1
GO
insert into tblSubStatus 
Values ('X'), ('Y'), ('Z');  --id's 1, 2, 3 respectively
GO
insert into StatusSubStatus
Values (1, 1, 1), (1, 1, 2), (2, 1, 3);
GO

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.