Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a products, and subproducts.


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.

share|improve this question
Do you also have sub-subproducts (subproducts of subproducts of products)? And sub-sub-subproducts? – ypercubeᵀᴹ May 10 '13 at 15:14
Can something be a subproduct of 2 or more different products? – ypercubeᵀᴹ May 10 '13 at 15:16
No, to both questions – jsedano May 10 '13 at 15:20
up vote 2 down vote accepted

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:

( IDproduct INT NOT NULL

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

( IDproduct INT NOT NULL
, IDsubProduct INT NOT NULL

      PRIMARY KEY (IDproduct, IDsubProduct)
, CONSTRAINT SubProduct_Name_UQ
      UNIQUE (IDproduct, Name)
, CONSTRAINT Products_SubProducts_FK
      FOREIGN KEY (IDproduct)
      REFERENCES Products (IDproduct)
) ;
share|improve this answer

A hierarchical relationship is probably fine:

parent_id (nullable)

insert into product values (1, 'Proc A');

insert into product values (2, 'SubProA1', 1);

For performance reasons, you will need special queries. I'd use postgres over mysql if I were you. Use WITH queries.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.