enter image description here

I am using modified preorder traversal algorithm to store product categories in database (like image shown above). But i don't understand how to reference it in my products table. Here is my products table

id |  name        | supplier         | price | description
1  |  T shirts    | Rana Enterprises | 200   | Run faster than light!
2  |  Shoes       | RS Shirts        | 100   | If you're going to San Francisco...

This is my categories table

enter image description here

Tell me how to link my categories table to products table ?

  • 1
    Can you show us your categories table, like you did for products? Commented Jan 19, 2015 at 13:40
  • @ypercube yes, pls see i updated my question
    – Cody
    Commented Jan 19, 2015 at 14:16

1 Answer 1


I see from your diagram and from the columns named lft and rgt that you are using the nested sets technique for organizing the categories into a tree. So far so good.

Now all you need to do is add a foreign key to the products table that references category id in the categories table. You might as well call it "category_id", same as the column it references. The benefit of declaring it as a foreign key is that the DBMS will now prevent the introduction of products with an invalid category_id. It's up to you in the application to provide a valid category_id at product insert time.

All of this is very elementary relational database design. If it's covering material you already know, then please reword the question to make it clear just where you need an answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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