Last time, we chatted about the schema for all seasons — the One Big Table (OBT). As a refresher, OBT is a great schema for getting started and flexing into other schemas as your doc and data set evolve. If you want to get the most out of your OBT, make sure to read our tips in One Big Table: Tracking made simple.
Our next schema gives your data a bit more space and a whole new meaning to the word star chart.
Star schema
If you start to repeat data in your OBT, it’s time to scale up to a new schema. The logical progression for most makers is star, a schema that breaks your OBT down into smaller tables that reference each other.
Star is ideal for:
- Larger scale projects or initiatives
- Ensuring consistent data
- Referencing the same data in multiple places
- Data with 1-to-1 relationships
- Summarizing all projects with a certain status
Learn more about star schema in this episode of Designing Docs:
Declutter your data with lookups.
Imagine you’re using the table below to keep track of your renovation project, with dedicated columns for tasks, room of the house, and vendors. You probably have additional information you’d like to track about each, like the budget for each room and contact information. If you used OBT schema for your table, descriptions would repeat every time the task, room, or vendor was mentioned. While OBT keeps your data in a single place, the duplicate data in this instance clutters your view. And any changes needed would need to be made manually for every single mention of that description.
The beauty of star schema is that it allows you to reference data located elsewhere in your doc. Your descriptions are still within arms reach when you need them, but they live in smaller tables that feed into the main table using lookups. And now that your tables are linked, updates are made to other tables automatically, even in your display column. So whatever typos you fix or context you appear wherever that data is referenced.
Turning your OBT into a star is simple. Convert any select list into a table of its own using the “Convert to table” button in the select list options. Then add as much information to your new table as you’d like. From there, you can create as many connected tables as you need.
And connect existing tables.
Star schema isn’t just an evolution of OBT. The lookup column type can also link two (or more) existing tables. Let’s say you want to connect the Room and Vendor tables. Add a lookup column to the Room table, and select New Lookup > Vendor. Now you can select the vendor working in each room.
A bit of inspiration.
Remember, there’s no right or wrong schema for your doc — you’re simply looking for the most efficient method to display and interact with your data.
Star schema is often the cleanest representation of what is in the real world. Your renovation has its own table, but because your rooms and vendors are different, with their own set of context, they need their own tables. Star schema allows you to cleanly organize all of this data while also keeping everything connected.
Looking for some stellar examples of the star schema? Here are two docs worth exploring:
- Sales CRM by John Scrugham — Star schema turns a simple contact list into a comprehensive sales pipeline.
- An inside look at how Figma ships product by Yuhki Yamashita — By reducing redundant data, star schema provides the framework for the Figma product team’s efficient single source of truth.
Thanks to the authors of this post: Ben Lee, Maria Marquis, and Hannah Rochau.
