Schema design principles

Inside this article

Schema 1 - One Big Table
Schema 2 - Star
Schema 3 - Overview/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, Task Stage, Due Date, and the Room they apply to

Steps to build

1. Write /table in your doc and hit ENTER to create a new table.

2. 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

3. That most important part will become your rows and the attributes will be your columns (remember you can always change the column format)

4. 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. Write /table in your doc and hit ENTER to create a new 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 dropdown in the column header, choose Change column type, hover over Lookup, and hover over + 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

Overview Detail

What is it?

• A summary table (overview) 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 Overview/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

1. Write /table in your doc and hit ENTER to create a new table - this will be your Detail.

2. Populate it with all of the data you're tracking

3. Write /table again and create another Table - this will be your Overview.

4. Determine the questions you need your Overview 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 base or overview

• 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

1. Determine the information you need to work with and identify connection points

2. Write /table in your doc and hit ENTER to create a new 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 has attributes of its 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.