I've got some entities that are tagged under different categories. However, some of the categories have various sub-categories (up to 2 levels), but the hierarchy is not uniform - some with heights of 1 - 2.
My idea was to create a separate table for each category and one for each of the levels, then link the entity ID to the leaf however, it doesn't seem very efficient.
Love to hear your thoughts!
There is a more flexible design:
When searching for entities belonging to the given category, first search in CATEGORY for all of its sub-categories, sub-sub-categories etc. Depending on the DBMS, this could potentially be done in a single recursive query.
After you have all the categories in the hierarchy, just JOIN the CATEGORY_ENTITY and ENTITY and filter by these CATEGORY_IDs.
BTW, should the category name be unique globally or only among the siblings? Depending on that, you might want to add a UNIQUE constraint on NAME or on {PARENT_ID, NAME}1 respectively.
1 But check how your DBMS handles NULLs in composite UNIQUE constraints.