Database Design for representing category relationships So I am new to Databases.Recently I was asked this question in an interview where they expected me to come with a schema for storing and retrieving categories for an E-commerce website in an RDBMS. Each category could have many sub-categories. But each sub-category could have just one parent category. So this is the idea that I gave them:
First I created the table like so:

create table category(category_id TEXT PRIMARY KEY,
category_name TEXT,parent_id TEXT);


Then I created an index on the parent_id, like so:

 Create index idx_category_table on category(parent_id);


Then I used the following queries to get the parent and child queries:

Select B.category_name as CategoryName from category A,
category B  where A.category_id='3' and 
A.parent_id = B.category_id;

Select B.category_name as CategoryName from category A,
category B  where A.category_id='1' and A.category_id = B.parent_id;


I was rejected from the interview. However, I am curious to know how to better this.
I have a small concern about your approach that I think you should have addressed during the interview because it could raise potential issues in your design.

Do you want/need nested product categories?

As it currently stands you're allowing nested categories and even allowing loops of categories where a category can reference itself or one of it's own subcategories.

If you don't want nested categories then one of your restrictions will have to be that category with a parent should check it's parent doesn't also have a parent. The easiest way to do this is with two extra virtual columns(not stored), the first will simply say if a table has a parent(parent_id IS NOT NULL) and the second defines the value we need to create said relationship(namely that the value should be 0).

CREATE TABLE category(
    category_id TEXT PRIMARY KEY,
    category_name TEXT,
    parent_id TEXT,
    is_subcategory INT GENERATED ALWAYS AS (parent_id IS NOT NULL) VIRTUAL,
    can_be_child_of_subcategory INT GENERATED ALWAYS AS (0) VIRTUAL,
    FOREIGN KEY (parent_id, can_be_child_of_subcategory) REFERENCES category(category_id, is_subcategory) ON UPDATE RESTRICT
);


And now without changing the definition of the virtual columns we can't add a subcategory to a subcategory.



That said there's a few less major issues that might be worth considering in a bit more detail:

You should be using a foreign key, not an index:

FOREIGN KEY(parent_id) REFERENCES category(category_id)


You appear to be using TEXT as the field type, but accessing it with integers. If you're using a text type it's worth making it clear it's a GUID, either way you should use the right type for the field.

category_id INT
category_id = :sub_category_id
-- OR
category_id CHARACTER(32)
category_id = :sub_category_guid


It's ANSISQL standards to write keywords in upper case, obviously you don't need to keep to that standard as every SQL language supports lower or mixed case, but almost every documentation will have keywords in all caps so it's worth keeping to the convention:

CREATE TABLE category(category_id CHARACTER(32) PRIMARY KEY,
category_name TEXT,parent_id CHARACTER(32));


I also suggest making things not null

CREATE TABLE category(category_id CHARACTER(32) NOT NULL PRIMARY KEY,
category_name TEXT,parent_id CHARACTER(32));


I'd also suggest adding a bit more whitespace and at least for an interview document choices

CREATE TABLE category(
    category_id CHARACTER(32) NOT NULL PRIMARY KEY,
    category_name TEXT,
    parent_id CHARACTER(32),
    FOREIGN KEY (parent_id)
        REFERENCES category(category_id) ON UPDATE RESTRICT
);


Finally I'm really not a fan of your naming on the aliases for the table. Much nicer to use a descriptive name to make it easier to understand:

-- Fetch all child subcategories for a given category
SELECT
    SubCategory.category_name AS CategoryName
FROM category ParentCategory,
     category SubCategory
WHERE
    ParentCategory.category_id = :parent_category_guid
    AND
    ParentCategory.parent_id = SubCategory.category_id
;

-- Fetch the parent category for the child
SELECT
    ParentCategory.category_name AS CategoryName
FROM category ParentCategory,
     category SubCategory
WHERE
   SubCategory.category_id = :sub_category_guid
   AND
   ParentCategory.category_id = SubCategory.parent_id
;