Inside this article
Schema 1 - One Big Table
Schema 2 - Star
Schema 3 - Master/Detail
Schema 4 - Multidimensional
Helpful schema guidelines
Learn schema design by doing
One Big Table
What is it?
- Just like the name says, you'll have one big table that holds all of your data
- The one big table schema is basically a list of all the information you want to track. Keep in mind, that this means that everything will need to fit into the row/column setup in that core table
Why would I use it?
- You have a small team
- You are working on a smaller project
- Everything is an attribute of a primary type of data. For example, if I'm tracking tasks (like we see in the table above), all of my columns describe those tasks - owner, vendor, status, due date, and the room they apply to
Steps to build
- Click the + icon and choose Table
- Decide which items connect all the information (e.g. tasks) - you'll know it's the most important part if it has a lot of elements that describe it
- That most important part will become your rows and the attributes will be your columns (remember you can always change the column format)
- Set the display column - in other words, what you want to show up when you type the @ symbol
Star
What is it?
- A larger table that pulls data from other smaller tables using Lookups
- In Coda, you can link tables together using Lookups which are the back bone of a Star schema. Rather than typing the same values in multiple places, you reference data located in other places in your doc. In the example below, we're actually pulling the vendor name from the vendor table, and the room name from the room table. Each of these smaller tables act as spokes of a wheel feeding into the main table (hence the name star!)
Why would I use it?
- Larger scale projects or initiatives
- You want consistency
- You want to reference the same data in multiple places
- Your data has 1 to 1 relationships
Steps to build
1. Click the + sign and choose Table. If you know the tables you want to lookup from, repeat that process for each lookup table
2. If you realize you need a lookup table for an existing column, no worries, just click the column header, choose Lookup from table and +Create new Table. Coda will automatically create a lookup table for you
3. You can then add columns in your core or lookup tables to account for all the other details you have
Master Detail
What is it?
- A summary table (master) populated by a larger dataset (detail)
- The point of tables and docs is to answer specific questions you have, and to help you make informed decisions. Part of allowing for that is creating specialized ways to make sense of the massive dataset underneath. In Master/Detail, you create a summary table to answer your questions and use formulas to pull the answers you need from the more granular detail table
Why would I use it?
- You have a large dataset
- You need to answer specific questions with that data in a summary or dashboard experience
Steps to build
- Click the + sign and choose Table - this will be your Detail.
- Populate it with all of the data you're tracking
- Click the + sign again and create another Table - this will be your Master
- Determine the questions you need your Master summary to answer, and build formulas pulling from the Detail view to create your dashboard
Multidimensional
What is it?
- Many tables (large and small) that are interconnected via Lookups and Formulas. No one table acts as the master
- When you're working on larger initiatives, the data starts to weave together, and the amount of data you'll have grows over time. With a multidimensional schema your data flows across all parts of your doc. You'll have lookup tables that power other tables, formulas pulling from multiple tables, and more
Why would I use it?
- You have a large dataset 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
Steps to build
- Determine the information you need to work with and identify connection points
- Click the + sign and choose Table - create as many tables as you need using lookups or formulas for the connection points you identified
Helpful schema guidelines
- Don't make another table unless what you are tracking as attributes of it's own
- Use Lookups for like items
- Schema can change and grow with your doc, but always do a little up front thinking
- Before building a new table, ask yourself what does it have in common with what you've already built
- If you're going to be adding columns consistently to a table, this is a signal that your columns should be rows. Remember that columns are attributes and rows are items. You can always group your table along columns for organization.