I'm working on an expense tracker app as a side project and I've run into a design challenge. I want to create a category system that includes both shared default categories like 'groceries' or 'healthcare' that all users can access, and also allow users to create their own custom categories. I'm considering predefining the common categories myself and adding an optional user ID field to indicate if a category is specific to a user. This way, I can minimize duplicate records in the database while still allowing for customization. What do you think is the best way to approach this problem?
3 Answers
That sounds like a reasonable plan! It's good to have a mix of common categories coupled with the option for user-specific entries. It helps maintain organization while catering to individual needs.
I say just use a nullable user_id column in your category table. Set shared categories to have a NULL user_id and custom categories will have relevant user IDs. When fetching categories, you can filter with a SQL statement like `user_id IS NULL OR user_id = :current_user_id`. This is a simple yet effective pattern many SaaS apps employ!
I think seeding the database with a default set of categories during user registration is a solid move. It gives users a starting point without overwhelming them with empty fields to fill in first.
But what if multiple users want to add the same custom category? Like if User 1 and User 2 both want to add 'credit card bill'? How do you manage those duplicates?