I like working with services that need little to no provisioning effort — these are typically termed as Fully Managed services by different Providers.
The most provisioning effort is typically required for database systems, I remember having to operate a Cassandra cluster in a previous job and the amount of effort spent on provisioning, upkeep was far from trivial and I appreciated and empathized with the role of a Database administrator dearly during that time.
My objective in this post is to explore how a one-to-many relationship can be maintained in 3 managed database solutions on Google Cloud — Firestore, Bigtable and Spanner.
Data Model
The data model is to represent a Chat Room with Chat Messages in the rooms.
Chat Room just has name as an attribute. Each Chat Room has a set of Chat Messages, with each message having a payload and creation date as attributes. A sample would look something like this:
So now comes the interesting question, how can this one-to-many relation be modeled using Firestore, Bigtable and Spanner. Let’s start with Firestore.
One-to-many using Firestore
Managing a One-to-many relation comes naturally to Firestore. The concepts map directly to the structures of Firestore:
- Each Chat Room instance and each Chat Message can be thought of as a Firestore “Document”.
- All the Chat Room instances are part of a “ChatRooms” “Collection”
- Each Chat Room “Document” has a “Sub-Collection” to hold all the Chat Messages relevant to it, this way establishing a One-to-Many relationship
One-to-Many using Bigtable
A quick aside, in Bigtable information is stored in the following form
Each Chat Room and Chat Room message can be added in as rows with carefully crafted row keys.
- A chat room, needs to be retrieved by its id, so a row key may look something like this: “ROOM/R#room-id”
- Chat Room message row key can be something like this: “MESSAGES/R#chatroom-id/M#message-id”
Since Bigtable queries can be based on prefixes, a retrieval of messages by a prefix of “MESSAGES/R#chatroom-id” would retrieve all messages in the Chat Room “chatroom-id”. Not as intuitive as the Firestore structure as it requires carefully thinking about the row key structure.
One-to-Many using Spanner
Spanner behaves like a traditional relational database with a lot of smarts under the covers to scale massively. So for a one-to-many data model perspective, the relational concepts just carry over.
Chat Rooms can be stored in a “ChatRooms” table with the columns holding attributes of a chat room
Chat Messages can be stored in a “ChatMessages” table with columns holding the attributes of a chat message. A foreign key, say “ChatRoomId” in Chat Message can point to the relevant Chat Room.
Given this, all chat messages for a room can be retrieved using a query on Chat Messages with a filter on the Chat Room Id.
Conclusion
I hope this gives a taste of what it takes to model in these three excellent fully managed GCP databases.
