updating database items users are "subscribed" to

Question

I have some categories that users can favorite and track, the actual category names and details are stored in the DB but the items belonging to each category are fetched via api.

When a user adds a category to its favorite list I call the api fetch all the items belonging to the category, and store the actual category and items copy under the user scheme, so I can track what they ticked off as complete as I add a complete property under each item of the selected category before storing it under the user.

The problem is that new category items are added sometimes and I need to update the user stored lists with new items.

How do I go about redesigning the DB so the users have up to date information. Should I pull the items to the DB and set my users to only have references to categories they follow and references what items they completed? How do I achieve this?

Its a MongoDB single page JavaScript application.

Answer 1

store the actual category and items copy under the user scheme

You need another few tables, mapping users to categories and users to their checked items.

Also, I don't know why your items are not in the database as it sounds like those are effectively database entities but for some reason there's an API to fetch them, even though a lot of metadata about them is stored in your main database.

Your users table should not be storing that sort of relationship data as items under it. There should be at least five tables:

• Users
• Categories
• Items
  • Reference categories
• User categories
  • Using references to both the above tables
• User items
  • Using references to Users and Items

This means when a user "ticks of as complete" an item it gets updated in your User Items table. When they subscribe to a category, it gets added to the User categories table.

The result of this is that you can trivially query any combination you want. If you want all unticked user items, run a query that pulls all items matching items the user is subscribed to that are not "ticked." If you want all user ticked items, do the same thing.