Not all data sets can be contained to a single table, or even two. And as we’ve said at the start of this series: More data is better—until it isn’t. The final and most complex schema in our series equips you with the structure to handle large data sets, allowing you to weave your data together as it evolves over time.
If you need to catch up, you can always check out the other pages in this doc:
Multidimensional schema is ideal for large data sets that are connected in multiple ways. Think tables in a single doc that serves many functions, like a CRM with a to-do list or a with a feature request ticketing system.
Choose this schema when:
You have a large data set, and there's no clear hierarchy among your tables. You have a lot of different questions you need to answer with your data. Your data has many-to-many relationships.
Learn more about multidimensional schema in this episode of Designing Docs:
Lookups and formulas: Using the power of star and base/detail schemas
Like our two previous schemas, lookup columns and formulas are key in making multidimensional schema work. But, rather than having one table with multiple spokes, every table in a multidimensional schema is equally important.
Think back to the remodeling project we discussed for . In our original example, the remodel tasks table served as a base, with the budget and vendor tables connected by spokes. If we were to evolve their schema star to multidimensional, we would have: A table of vendors and their information connected to the task table by lookup column. A table of contacts connected to the table of vendors by lookup formula.
In other words, instead of both the vendor and contact tables both connecting up to the task table, each table would connect and pull in information from each other.
Each table is equally important, but the lookup columns show the multiple dimensions (get it?) of how the information works together.
Large data sets and a word of caution
Multidimensional schema is rare, and for a reason. The entire purpose of a data schema is to prevent data from becoming unwieldy. Because all tables are equal in this schema, it’s easy to create table after table, building yourself into an unnecessarily complex situation. If your data has outgrown other schemas, here are a few things to consider before moving on to multidimensional:
Is the data you want to track in a new table unique? Does the data you want to track in a new table have something in common with data you’re already tracking? If the answers to these questions are no, you may need to adjust your table organization instead of evolving your schema. For example, consistently adding columns to a table might be a sign that your columns should actually be rows. Remember that rows track items and columns are the descriptors of those items. Try your table by columns to arrange your data differently.
As your data complexity increases, the doc map can also be a great resource for navigating your tables. You can access your doc map by clicking the gear in the top right of your doc.
A bit of inspiration
Want to check out a multidimensional schema? Explore these docs: