Chapter 2 - MySQL structure comparison with Firebase structure

In this chapter, we will discuss how you can decide the structure of your data in Firebase as compared to you do it in MySQL. So lets begin.

Consider a simple example of two tables "students" and "courses" and a relationship table which stores the info of student applying the specific courses as student_course.

Following is the student table with the basic fields firstname, lastname etc.

The second is the courses table with the basic fields name, duration, cost etc.

The third one is the Student_Course as relationship table to store the data of student applying to which course.

Now your normal query in MySQL to fetch a particular course with the applied student would be this.

 select c.name as course, s.firstname as student 
	from courses as c 
	left join student_course as sc on sc.course_id = c.id  
	left join students as s on s.id = sc.student_id  
	where c.id = 1  

For Laravel lovers (assuming you know the Eloquent ORM. Don't know? comment below)

 $course = Course::where('id',1)->first();  

Then

 $course->students();  

Now lets implement the above structure in Firebase > Cloud Firestore

Consider the same above students in Firestore

And the same above courses in Firestore

Now here you have two option either you can create a new collection next Courses and Students as Student_Course or you can create a collection Student inside the Courses. I will show you both and will let you when to use which one.

First one creating a third one Student_Course and storing the reference and ids

Here in above, Student_Course contains the data of student and course by storing there ids and reference of document to fetch the details.

Now the second way is to create Student collection inside the courses

Hope you are clear from above that how student can be linked with a course. Now the answer to when to use which one. If the course is one time process like a exam or an event which would not come again or would not be used, then you can go with step 2 else if the course remain the same every year, the students apply every year, so you have to go with step 1.

Now the query in Firestore PHP for the mysql query mentioned above  (this is for step1)

$studentCourseRef = $db->collection('Student_Course');  
 $query = $studentCourseRef ->where('course_id', '=', 'ZGOGSL0Zzg66n7O4RzOo');  
 $snapshot = $query->documents();  
 foreach ($snapshot as $document) {  
   printf($document->id());  
   printf($document->data()['student_id']);  
 }  

Thanks for reading. I hope you have got pretty much cleared. There is much to go in upcoming next chapter in detailed version. You can share your doubts in comment sections.