Need help designing a database

Hi,
I am building an app where users can take courses on various topics. Each course consists of lessons. Each lesson can have status “New”, “Finished”, “Skipped”

How should I design a database to keep each lesson status for each user?

I want to be able to:

  1. show the user his next not watched lesson from a specific course
  2. list of finished lessons

You’ve got a many to many relationship here (each student can have multiple lessons, and each lesson can be taken by many students). In general, there are two ways to handle many to many relationships:

  1. Keep a list (e.g. List of Lessons on the user)
  2. Create an “instance” item (e.g. A “User-Lesson” item that links a User and a Lesson)

In your case, it gets more complicated with the addition of the Course item. One way you could set it up that will work is:

Data types:

Course:
-lessons: List of Lesson
-Other info

Lesson:
-Other info

-User:
-course: List of Course

-User Lesson:
-lesson: Lesson
-user: User
-status: text or option set
-parent course: Course

Of course, this is just one way to do it. The best way to do it depends on your app, and takes into account things such as:
-How many lessons per course (in general)?
-How many courses per user (in general)?
-What are the most common things a user does? What is the data you need the user to see quickest?
-Is this a quick MVP (prioritize easier development) or a full-fledged app (prioritize user experience)

Hope this helps. If you need more help, feel free to reach out to us at our website. We offer data design as part of our services.


Stuck on your bubble app? Reach out to us at https://protomakr.bubbleapps.io/

Hi Austin, thanks for your reply.

Right now there are 5 courses and 50 lessons in each course. This number probably wouldn’t grow too much. Each user can get up to 5 courses at the same time.

The most common action from user is to go to list of unwatched lessons, pick one, watch it, push button “Watched” and get back to the list of unwatched lessons.
We want to load a list of unwatched lessons as fast as possible. Also in a future we’d like to add comments for each lesson, so users can discuss it.

It is just an MVP for b2c mobile app (i’m going to use BDK native). I really want to finish web version by the end of the next week, so easiness of development is important factor.

My ultimate goal is to validate hypothesises that:

  1. People are going to watch lessons (firstly on the web version, then - in the mobile app, submitted to appstore)
  2. People are going to get back for new lessons and keep them watching (will need push notifications here)
  3. People are going to buy subscription (will need to integrate apple in-app purchases)