database with Category, (Optional subcategory) and sub-subcategory (service)

Question

Thought I can handle it all by myself, but... I thik I've wasted too much time... Hello everyone! I'm struggling with Room database structure - I'm not sure if wheter my approach is correct or not. What I'm trying to achieve is to be able to add Services (sub-subcategories) to Categories, but there may be optional Subcategories as well:

Category -> Service

Category -> optional Subcategory -> Service

What I've already did (skipped some basic parts of code):

a) created entities

@Entity
class Category
int id
String name

@Entity
class Subcategory
int id
String name
int categoryID

@Entity
class Subcategory
int id
String name
int categoryID
int subcategoryID

b) created join table

@Entity
class Join
int id
int categoryID
int subcategoryID
int serviceID

It's straight-forward when a category has subcategory with any services:

id | categoryId | SubcategoryId| ServiceId|
-------------------------------------------
1  |          1 |            1 |        1 |
1  |          1 |            1 |        2 |

but in case there's no subcategory and service belongs to category only:

id | categoryId | SubcategoryId| ServiceId|
-------------------------------------------
1  |          1 |         null |        1 |

I put null as SubcategoryId and check if subcategory is null

So the question is: is this approach correct in terms of relational database design ?

Or should I: 1. create category-subcategory, subcategory-service, category-service join tables and conncet them somehow eventually in one, big join ? 2. or add categoryId and subcategoryId to the service ?

Any comments, tips & tricks much appreciated! :)

Jeel Vankhede Jeel Vankhede · Accepted Answer · 2018-10-01T17:23:45

Rather than joining it into other relational tables, i would use following approach:

Let's say you have only two categories, one is primary (Main category) & second is secondary category (Sub category).

I would create entity for Category in single table like this:

@Entity
class Category
int id // It's our primary key here
String name
int parentId // To detect if category is Main/Sub. How? "Null" if Main, "main category" id if child

Now about Service entity:

@Entity
class Service
int id
String name
//This is our foreign key from Category table
int categoryId //It can be Main/Sub category id from "Category" Table, how to detect it's Main/Sub? Check it from category table using "parentId".

Why this approach?

Because,

1 Service relies upon Category, so it doesn't matter for Service whether Category is Main/Sub. All it knows is that it's Category for itself.

2 Now for Category, Why no separate tables for both of them? because, even though, it is Sub-category, we need to treat them like Category(and it really is), So better treat it like Category and differentiate both by one new parameter named parentId for it.

database with Category, (Optional subcategory) and sub-subcategory (service)

2 Answers