How to reference categories table in products table?

Question

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

product
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

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

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.