mysqlentitydiagramentity-relationship

How to recreate an hierarchy in ER diagram?

I'm doing this project from school, about a supermarket database, and i have a restraint that i need to represent but i don't know how. Here is the restraint:

"Every product has a named category. There are no products without category and each product belongs to only one category.

There can be a category within another category (sub-category). Meaning, the categories form an hierarchy. Categories that are formed of sub-categories are named "Super Categories" and the others are just "Simple Categories".

One category can only belong to one Super Category.

The system must determine, for each Super Category, how many sub-categories there are."

I was thinking about doing a "IS A" relation, like this one: https://prnt.sc/gtzmuh

What do you think? How can I make this work?

Thanks in advance! :)

Solution

  • I don't think inheritance is quiet usful in this case ... I believe this figure below should help you ... ER

    And then your tables in SQL should be something like this:

    1. Category(category_id, ...) -> key(category_id)

    2. Product(product_id, category_id, ...) -> key(product_id, category_id)

    3. A_is_subCategory_of_B(A_category_id, B_category_id) -> key(A_category_id, B_category_id)

    Any super category and sub category pair will be inside the "A_is_subCategory_of_B" table.