Take the 2-minute tour ×
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.

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.

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
add comment

2 Answers

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:

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)
) ;
share|improve this answer
add comment

A hierarchical relationship is probably fine:

PRODUCT
id
name
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
add comment

Your Answer

 
discard

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.