Firebase Realtime Database Many to Many Relationship Schema

Published at Sep 19, 2018

Firebase Realtime Database is a fully managed NoSQL Cloud Database service that is provided under Google’s Firebase mobile platform as one of their core product. It is built under Google infrastructure, so developer without backend/dev-ops expertise can build apps fast without worrying too much about scalability and just focus on building the best apps. Firebase also offers new realtime database called Cloud Firestore that still is in Beta which is even more scalable but different in terms of the schema design compared to current Firebase Realtime Database. Firebase.

Realtime Database (RTDB) stores the data as JSON tree. It offers many client SDKs from iOS, Android, and Web. The Realtime Database uses WebSocket technology under the hood for synchronization, so all the connected clients can receive realtime update when the data in the server changes and vice versa, so when the client updates the data the server also receive the updates within milliseconds latency (bidirectional communication). It also provides offline support, so the client can still display the data even when there is no internet connection and synchronize the data automatically when the connection is back.

Designing Schema in Firebase Realtime Database Effectively (a real case study)

While Realtime Database provides freedom for developers to design the schema for their application because of its NoSQL nature, the developers still need to design the schema carefully so their app can really scale efficiently in terms of cost when the data in the app gets bigger as it grows.

One of many common questions that many developers ask when using Firebase Realtime Database is how to design the schema for model with many-to-many relationship efficiently. In this case, i am going to use many-to-many relationship between students and classes. A single student can register to many classes and a class can have many students. In SQL Database, we can solve this easily by using join Table called enrolments that stores the student id and class id as the foreign keys.

Designing Using Nested Schema (Antipattern)

But how do we translate the SQL design schema into the Firebase Realtime Database JSON tree?. We might try to create 2 top level nodes, a classes and students, then inside each class/student child we embed the classes or students in each child like so:

classes:
  class1:
    students:
      student1: true
  class2:
    students:
      student1: true
      student2: true
students:
  student1:
    classes:
      class1: true
      class2: true
 student2:
    classes:
      class2: true

This is not a recommended pattern to use when handling many to many relationship model because of its nested nature. Here are the cons when using this pattern:

When we query for the children for example student1 or class1, all the data for enrolments will also get fetched from the server although we don’t need to use the data.
We need to query each child class or students to get their enrolments event though we don’t need the metadata for the child.

Those cons make the database not efficient and scalable when we have ten thousands of students or classes. The query will get slower as the data grows and the cost of using the database will be much higher.

Using Schema Denormalization (Recommended)

One of the most recommended solution is to denormalize and flatten the data into 4 top level nodes:

classes: Store the metadata for each class as the children.
students: Store the metadata for each student as the children.
class_enrolments: Store the relationship between each child class and students as the children. We use this to lookup the student enrolments for a class.
student_enrolments: Store the relationship between each child student and classes as the children. We use this to lookup the classes enrolments for a student.

classes:
  class1:
    ...
  class2:
    ...
students:
  student1:
    ...
  student2:
    ...
class_enrolments:
  class1:
    student1: true
    student2: true
  class2:
    student2: true
student_enrolments:
  student1:
    class1: true
  student2:
    class1: true
    class2: true

Using this schema, we can find relationship between students in a class and classes for a student without performing query and just using single lookup by key which is very efficient particularly for huge datasets.

Linking & Unlinking the Relationship with Realtime Database Fanout

To link the relationship between a class and a student, we need to use Realtime Database fanout strategy to update multiple nodes simultaneously. In this case we need to update the children inside studentenrolments and classenrolments top level nodes with their respective class id and student id.

let student1Id = 'student1'
let class1Id = 'class1'let updates = {
  [`student_enrolments/${student1Id}/${class1Id}`]: true,
  [`class_enrolments/${class1Id}/${student1Id}`]: true
}
firebase.database().ref().update(updates)

For the unlink, we use the same strategy as the link but in this case we set the value of the respective keys to null to remove them from the parent nodes.

let student1Id = 'student1'
let class1Id = 'class1'let updates = {
  [`student_enrolments/${student1Id}/${class1Id}`]: null,
  [`class_enrolments/${class1Id}/${student1Id}`]: null
}
// Update to both nodes simultaneously (fanout)
firebase.database().ref().update(updates)

Listing Class Enrolments for a Student & Student Enrolments for a Class

To get the list of students for a class or vice versa, we just need to perform lookup by the respective key like so:

let student1Id = 'student1'
let class1Id = 'class1'
let database = firebase.database()

// List Student enrolments for a class
database.ref(`class_enrolments/${class1Id}`).once('value')
  .then((snapshot) => {
     console.log(snapshot.val())
  })
  
// List Class enrolments for a student
database.ref(`student_enrolments/${student1Id}`.once('value')
   .then((snapshot) => {
     console.log(snapshot.val())
  })

Retrieving Actual Data for a Single Student or Class

To retrieve actual metadata for a single student or single class is pretty straightforward using simple lookup:

let student1Id = 'student1'
let class1Id = 'class1'
let database = firebase.database()

// Get Class Metadata using classId
database.ref(`classes/${class1Id}`).once('value')
  .then((snapshot) => {
     console.log(snapshot.val())
  })
  
// Get Student Metadata using studentId
database.ref(`students/${student1Id}`.once('value')
   .then((snapshot) => {
     console.log(snapshot.val())
  })

One More Consideration…

There is also additional cases that need to be put into consideration if a class or an employee is deleted related to the data in relationship nodes. You can look into Firebase Function Database trigger to solve this problem. When the node is deleted, a function will be triggered that we can use to perform cleanup for the relationship nodes.

Conclusion

Designing Schema for Firebase Realtime Database actually is not pretty straightforward, we need to really analyze our app use case properly and design it carefully. The cost of using Firebase Realtime Database can be quite high and the query can be slow if you don’t design the schema effectively.