0

I have two entities: Course and User. Each course can have many users attending it. At the same time, each user can participate in many courses. Each user can give a rating to each course. Each course also has a completion rate (the percentage of users who finished the course with the minimum passing requirements).

I want to query two lists: a class list and a user list. I want the list to be sortable when queried too. The expected sorting behavior is the following.

  • Course: sort by highest to lowest and/or lowest to highest ratings, completion rates, and number of users attending it
  • User: sort by highest to lowest and/or lowest to highest courses attended and completion rates

What I have thought of is creating two separate collections with the following relevant fields for the two lists.

  • course: each course should have a rating field, a completion rate field, and a number of users attending field. These fields will be updated whenever there are triggers that update the rating, completion rate, and number of attendance of the course.
  • user: each user should have a completion rate field and the number of courses attended field. Similar to the course collection, the fields will be updated on some triggers.

The ratings will reside in a different collection. About the user progress etc, there will be other subcollections/collections to store the details.

I would like to know if there is a better way to model the data such that I can only use one collection to do the above fetching of the lists with the expected sorting behavior.

Rohit Kharche
  • 2,541
  • 1
  • 2
  • 13
Richard
  • 7,037
  • 2
  • 23
  • 76
  • In my opinion, your question is a little too broad to reasonably answer, here on StackOverflow. Besides that, please also note that there is [no "perfect", "the best" or "the correct" solution for structuring a Cloud Firestore database](https://stackoverflow.com/questions/53053768/what-is-the-correct-way-to-structure-this-kind-of-data-in-firestore). However, there is something I don't understand, what's wrong with your approach, are your queries returning the desired results? – Alex Mamo May 24 '23 at 06:06
  • You can use the following [**data model**](http://surl.li/hgiot) to get your query working. Reply if this is what you are looking for. – Rohit Kharche May 24 '23 at 06:41
  • @AlexMamo With my approach, I'd have to do a write on two different collections and I'd have to maintain two different collections so that the info is up to date. I was wondering if there's a way to do a write on only one collection so that I only need to maintain that one on the Cloud Functions Triggers. – Richard May 24 '23 at 08:14
  • @RohitKharche The one you referred to is what I am already planning to do. – Richard May 24 '23 at 08:15
  • @AlexMamo Oh to answer your question, my queries do return the desired result with the above data model. – Richard May 24 '23 at 08:21
  • @Richard This is the only way I think this can be done without introducing any complexity as with this approach the queries will be simpler. If possible can you share more info to narrow down the modeling. – Rohit Kharche May 24 '23 at 08:22
  • So if you get the desired results, then I recommend you go ahead with that structure. – Alex Mamo May 24 '23 at 09:21
  • Did Rohit's answer help? – Alex Mamo May 25 '23 at 05:30
  • @AlexMamo Unfortunately, it doesn't. What I needed is to sort based on `completionRate`, `noOfUsersAttending`, and `ratings` on course. Based on his structure, I cannot query order by `completionRate` for the course. Similar issue with querying for user. – Richard May 25 '23 at 06:55
  • @AlexMamo For now, I have decided to stick with the structure I already had in mind (what I described in the question). – Richard May 25 '23 at 06:55
  • Ok, Richard. That's the reason why I said yesterday, to go ahead with your structure. – Alex Mamo May 25 '23 at 07:00
  • @Richard You can query order by `completionRate` for the course as follows : `query(courseRef, orderBy("completionRate", "desc"));` does this didn't work for you ? – Rohit Kharche May 25 '23 at 07:07
  • @AlexMamo Thanks. I was just wondering if there's a better way to do it that I missed. – Richard May 25 '23 at 07:13
  • @RohitKharche Can you give an example of what `courseRef` is? To clarify, the completion rate of each course is calculated with `number of users that completed the course/number of users attending * 100%`. Then, I intend to query the courses based on the highest to lowest completion rate and vice-versa. – Richard May 25 '23 at 07:14
  • @Richard Yes you can get your query regarding completionRate work like what I have shared in this [snippet](https://codefile.io/f/b0BzfOoNHe) – Rohit Kharche May 25 '23 at 08:35

1 Answers1

2

Based on the information you shared, the following data model is the only one I can think of that will satisfy your queries and make the data entities many-to-many while keeping it simple.

Assumptions made :

  • The documents in the courses collection must have a document ID that is either their course_name or course_id, and the documents in the users sub-collection must have a document ID that is user.uid.
  • The documents in the users collection must have a document ID that is user.uid, and documents in the courses sub-collection must have a document ID that is either their course_name or course_id.
  • Every time we create any document in the sub-collections the noOfUsersAttending for courses collection and noOfCoursesEnrolled for users collection should be updated respectively.

Based on the above assumptions your data model structure should look something like follows :

Data Model Structure :

Firestore Database
├── Courses : collection
│   └── {courseId}
│       ├── rating : number
│       ├── noOfUsersAttending : number(1)
│       └── users : subcollection
│           └── {uid}
│               ├── completionRate : number
│               └── isCompleted : boolean
└── Users : collection
    └── {uid}
        ├── noOfCoursesEnrolled : number
        └── courses: subcollection
            └── {courseId}
                ├── completionRate : number(2)
                └── isCompleted : boolean

(1) - this needs to be updated every time a new user registers for this course
(2) - this needs to be updated every time the user enrolled for this course
  1. To get courses sorted by *:
const courseRef = collection(db, "courses");
// To get courses sorted by completion rates 
const q = query(courseRef, orderBy("rating"), orderBy("completionRate", "desc"));
// To get courses sorted by the number of users attending
const q = query(courseRef, orderBy("rating"), orderBy("noOfUsersAttending", "desc"));
    const snaps = await getDocs(q);
  1. To get users sorted by * :
const usersRef = collection(db, "users");
// To get users sorted by no of courses enrolled
const q = query(usersRef, orderBy("noOfCoursesEnrolled", "desc"));
// To get courses sorted by their completion Rate
const q = query(usersRef, orderBy("completionRate", "desc"));
const snaps = await getDocs(q);

I have found one article by Louis Coulet which explains the one-to-many relationship with security rules too, so you can also check that out.

Reference and credit to : How to model a many-to-many relationship in Firestore

Rohit Kharche
  • 2,541
  • 1
  • 2
  • 13