I’m designing the database schema for a e-learning website and I’m working with a document-based database (Firestore), so I ran into a problem of design the way of structure the data in order to not make a lot of reads.
So I design the schema based on these primaries features.
- There are three types of courses
- Lifetime: Once you bought it you can take the course as many times as you want.
- Generational: Once you bought the course, you can only take it between certain dates, if you wanna take it again, you need to buy the next generation.
- Lesson: You bought a pack with 3 tickets, these tickets can be used to watch a lesson, once you use all you need to buy more if you still want you to take another lesson.
- The ? after a column name means that the field is optional.
My mindset when I design the schema was the following
A unique source of truth about courses, his prices, commercial name, maybe more metadata about it, so I created the collection
Do not complicate the design differentiating between course types, for that reason I created the
Batchcollection, where I save all types of courses and generations. For example, If I have a lifetime course I only have I document on
Batch, with the flag lifetime and the course data embedded. If I have a generational course, I have as many documents as generations, with the number of the generation, the initial and the final date, the course could be the current generation or a scheduled one. The behavior of the lessons is similar to that of the courses by generation.
I have the subcollection
Userthat stores the purchased courses, if the course is a generation that is scheduled, instead of saving an embed document I save the reference to the Batch, as scheduled dates may change.
I save the number of tickets available to use on the user document.
So, I know there is a lot of info, but I’ll be glad if anyone could tell me if I’m on the right path, I mean I know that there may be a better solution, but if anyone could give me feedback, it will be great.