If you're not sure whether your data should go in a column, linked sheet or pick list, first look at how it relates to the other data.
Here's a simple rule to keep in mind:
If it’s a one-to-one relationship, use one sheet.
If it’s a one-to-many or many-to-many relationship, use many sheets.
Not sure what type of relationship it is? Let's take a look at each type...
One-to-one relationship (1:1)
If data only relates to a single row (and no other row should duplicate that), then we call it a one-to-one relationship. That data can all be stored on the same sheet.
For example, let's take a sheet called "Contacts". For each name on our contact list, there should only ever be one home address. Similarly, that home address should only ever be assigned to one name. Ignoring potential family members or roommates, we wouldn't want multiple people listed as having the same home address. Likewise, we probably wouldn't want multiple home addresses listed for one name, as this would likely mean that one of the home addresses is incorrect in some way.
So, each row on our contact list should contain one name and one home address. Each name has only one address, and each address has only one name (1:1).
One-to-many relationship (1:M)
If one piece of data relates to many rows, then we can say it has a one-to-many relationship. If that data also has its own one-to-one relationships, then it should be tracked on a separate, linked sheet.
For example, let's say we have a company associated with each name on our Contacts sheet. One company may be associated with many contacts (e.g. employees), but that company also has its own 1:1 data we want to track (e.g. mailing address). The list of companies should be stored on a separate "Companies" sheet so that we can keep a single record of each company's mailing address, and that Companies sheet can then be linked to our Contacts sheet.
In this case, we would have two sheets linked together: Companies and Contacts. Many contacts can now share one company, and one company can be linked to many contacts (1:M).
Pick list (1:M exception – without other unique data)
If we have a one-to-many relationship where the "one" piece doesn’t have its own unique data associated with it, we can store that on the same sheet using a pick list.
For example, think of a "Status" column. We could have one status relate to many companies (let’s say, Active or Inactive status), but that status doesn’t have any other unique info associated with it. So, we would simply add a Status pick list column to our Companies sheet to give ourselves a dropdown with an option for either Active or Inactive on every line. This will help us to sort, group and filter on status by ensuring data consistency (i.e. preventing data entry errors).
Many-to-many relationship (M:M)
Taking it one step further, we can also look at many-to-many relationships such as tags or multi-pick lists.
For example, let’s say we’re selling products to these companies, but each product has its own unique info associated (e.g. pricing info). We would make a third sheet called "Products" and link that to the Companies sheet. Many companies can now link to (purchase) many different products. Therefore, we can say that Companies and Products have a many-to-many relationship (M:M).
Can I use this logic outside of Fieldbook?
Yes! This kind of logic can be useful regardless of what platform you’re using. Fieldbook simply makes creating these relationships very easy via the linking feature. Thus, we describe Fieldbook as a relational database.