Table design when one data is dependant of another and the second one has repeated values

I have a products, and subproducts.

Like:

Product Identifier - Product Name - Subproduct Identifier - SubProduct Name
1 ProA 1 SubProA1
1 ProA 2 SubProA2
1 ProA 3 SubProA3
2 ProB 1 SubProB1
2 ProB 2 SubProB2
2 ProB 3 SubProB3
3 ProC 1 SubProC1
3 ProC 2 SubProC2
3 ProC 3 SubProC3

As you can see, the subproduct identifier is not unique. My input is gonna be the product and the subproduct, and I need to return the names.

I did this:


Table Products: ID, Name


Table SubProducts: IDproduct, IDsubProduct, Name

But I know that is wrong, I wanted to make 3 tables, products, subproducts and a relation between the two, but I don't know how to design it.

If I was to save the product and subproduct into another table, I would have to save both values, that table would have to have the productID and subproductID columns, that's why I feel having a relation product subproduct table would be the best.

But I'm having a hard time doing it.

With the clarifications in your comments, that there are only 2 levels of products, e.g. only Products and SubProducts and no subproduct is related to two or more products, your design is fine.

I would only add two unique constraints on table SubProducts, on (IDproduct, IDsubProduct) and on (IDproduct, Name) - or make one of them PRIMARY KEY and the other UNIQUE:

CREATE TABLE Products
( IDproduct INT NOT NULL
, Name VARCHAR(100) NOT NULL

, CONSTRAINT Products_PK
      PRIMARY KEY (IDproduct)
, CONSTRAINT Product_Name_UQ
      UNIQUE (Name)
) ;

CREATE TABLE SubProducts
( IDproduct INT NOT NULL
, IDsubProduct INT NOT NULL
, Name VARCHAR(100) NOT NULL

, CONSTRAINT SubProducts_PK
      PRIMARY KEY (IDproduct, IDsubProduct)
, CONSTRAINT SubProduct_Name_UQ
      UNIQUE (IDproduct, Name)
, CONSTRAINT Products_SubProducts_FK
      FOREIGN KEY (IDproduct)
      REFERENCES Products (IDproduct)
) ;

StackExchange Database Administrators Q#41880, answer score: 2