reverse query n level to first level

in my project i have 3 level.

  1. category
  2. subcategory
  3. product

in product table i have foreign key of subcategory and in subcategory i have foreign key of category. if i need category of product i retrive subcategory_id the compare with category so on.but it is long method for n how i design my database for n level. i method is that i put category and subcategory id as foreign key but i personally dislike it. any other design logic, concept or query.enter image description here

Answer

You can use next tables structure here:

  1. Categories table (each category have own id, name and possible parent category id): MySQL code provided for example
    CREATE TABLE Categories (
        id int primary key auto_increment,
        name varchar(64),
        parent_id int
    );
  1. Products table with foreign key to Categories:
    CREATE TABLE Products (
        id int primary key auto_increment,
        name varchar(64),
        category_id int,
        foreign key (category_id) references Categories (id)
    );

This DB structure give you ability to not limited sub categories with 2 tables only

Here SQL live fiddle where it can be tested