CODEWITHSUNDEEP
mysqldatabase-schema
redGREENblue
redGREENblue

Reputation: 3126

Multi category database schema with repeated data

Here's the data involved,

enter image description here

Here's the schema I have come up with

Scope

id name (example : regional ...)

Category

id, name (example : resources), scope_id

SubCategory

id, name (example : legal, sponsored ...), category_id

The problem is, as you can see, there are a lot of repeated data as the same category appears under all scopes, same sub categories appear under all categories. Of course, there will a few unique values in the case but they are very few. Also, even though the name of the category/sub category is same they will be holding references to different set of data depending on their parent scope/category. This will be used in segregating the content and generating a menu to browse those contents. What I want to know is if this is a proper way to address this or there are better ways/schema anyone can suggest me?

Upvotes: 1

Views: 149

Answers (1)

Benny Hill
Benny Hill

Reputation: 6240

Based on the limited information you've provided, this is what I'd do:

categories
    id                  unsigned int(P)
    name                varchar(15)

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Assets    |
|  2 | Resources |
| .. | ......... |
+----+-----------+

categories_subcategories
    id                  unsigned int(P)
    category_id         unsigned int(F categories.id)
    subcategory_id      unsigned int(F subcategories.id)

+----+-------------+----------------+
| id | category_id | subcategory_id |
+----+-------------+----------------+
|  1 |           1 |              4 |
|  2 |           1 |              1 |
| .. | ........... | .............. |
+----+-------------+----------------+

levels
    id                  unsigned int(P)
    name                varchar(15)

+----+----------+
| id | name     |
+----+----------+
|  1 | National |
|  2 | Regional |
|  3 | Zonal    |
| .. | ........ |
+----+----------+

levels_categories
    id              unsigned int(P)
    level_id        unsigned int(F levels.id)
    category.id     unsigned int(F category.id)

+----+----------+-------------+
| id | level_id | category_id |
+----+----------+-------------+
|  1 |        1 |           1 |
|  2 |        1 |           2 |
| .. | ........ | ........... |
+----+----------+-------------+

subcategories
    id                  unsigned int(P)
    name                varchar(15)

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Contributed |
|  2 | Financial   |
|  3 | Legal       |
|  4 | Sponsored   |
| .. | ........... |
+----+-------------+

Upvotes: 1

Related Questions