I'm trying to make relashionships between multi-level categories and subcategories, with product details table.
at first i have categories table :
+----+----------+
| id | name |
+----+----------+
| 1 | vehicles |
+----+----------+
and i have separated table for each category like category_cars table :
+----+-----------+---------+-------+
| id | parent_id | mileage | motor |
+----+-----------+---------+-------+
| 1 | 1 | 1000 | 1600 |
+----+-----------+---------+-------+
and the products table :
+----+-------------+-------+
| id | category_id | title |
+----+-------------+-------+
| 1 | 1 | 1000 |
+----+-------------+-------+
basically the category_id in the products table is the id of category_cars
and when i create a product i set it's title in products table, then add the other attributes of the proudct to the category_cars table like mileage, motor, ...
so what if i need to create a sub-category of vehicles e.g: cars, in this case i have no idea where to create the product.
what i'm trying to achieve is to have multi-level categories, like 9 static parent categories and dynamically created sub-categories
and a product attributes based on which product is this e.g: a car, or a motorcycle. to be able to have separated data (attribute) for each product type.
there's a default columns that will be globally for each product, like name,price and each product will need to have it's extra columns depending on it's type.
so the point is to get product this way :
product =>
[
name = myproduct
price = 200
category [
id = 1
name = vehicles
]
product details [
milage = 1000
motor = 1600
...
]
....
]
and display all products from a category whether it's main category or sub-category.
i can't figure out how i can make a relashonship between these. the logic itself is so complicated for me.
so how can i achieve this?