A database allows you to save, organize, and re-use data. Ultimately, this is accomplished by building apps that send data to, or pull data from, your database.
In this lesson, you will learn
Every Afterpattern project comes with a database with an undefined schema:
A database's schema is its structure; it reflects how you categorize data. The power of Afterpattern is that you can build almost any database schema that you can think of.
Building a database schema is conceptually challenging but technically simple.
Conceptually, you'll need to think carefully about the taxonomy of your data (do you break everything into Contacts and Matters? Do you have other categories for things like Assets, Related Parties, Shareholders, etc... ?).
Technically, all you need to do in order to build a database schema is add tables and columns to your project's database. For example, this database schema organizes data into the following categories (i.e. tables): Matters, People, Companies, Tasks, and Documents:
After you add tables to your database, you must add columns to each table. Columns have specific data types and this determines what sort of data you can store in that column.
Every column in your database has a specific data type that determines what kind of data can be stored in it. You specify the data type when you create the column (you can't edit this later):
The remainder of this lesson explores each column data type, including how you send data to, and pull data from, these columns.
This lesson concludes by introducing you to the concept of relational data.
Every table includes a key column, which you can identify by the key icon in the column label:
The key column is special. It is the only column in your table where the values must be unique.
The key column ensures there is a unique identifier for the records (i.e. the rows) in your table.
You can change the key column's label but you can't change its data type (plain text).
Use plain text columns to store text that doesn't include any formatting commands (rich text includes formatting commands such as headers, lists, hyperlinks, etc...).
If you pull data from a plain text column into your app, you can use it as you would any plain text variable.
You can make it so that your plain text column should only accept specific values:
When you populate this column, either manually or by sending data to it from an app, you'll be able to choose from these pre-defined specific values.
For example, here it was it looks like to manually update this column:
And here it what it looks like to send data from an app to this column:
Use number columns to store numbers.
If you pull data from a number column into an app, you can use it as you would any number variable.
Use true / false columns to store boolean values (True or False).
If you pull data from a true / false column in an app, you can use it as your would any boolean variable.
Use date columns to store date values.
If you pull data from a date column into an app, you can use it as you would any datetime variable.
Use a file column to store files and images.
If you pull a file / image from this column into an app, you can use it as you would any file / image variable.
Use a rich text column to store styled text (known as "rich text"). Styled text includes formatting commands like headers, lists, hyperlinks, etc...
If you pull data from a rich text column into an app, you can use it as you would any rich text variable.
Use a belongs to column to indicate that a record from one table "belongs to" a record from another table.
For example, you may have a table for storing matter data and each matter may belong to a contact from your contacts table. This is how you link related concepts together to create a relational database.
Use a has many column to indicate that a record from one table "has many" records from another table.
For example, you may have two tables for parents and children and each parent "has many" children.
What makes your Afterpattern database so powerful is the ability to link related data together. There are two types of relationships you can create:
One-to-many relationships are very common and relatively easy to build. The canonical example is the relationship between a parent and a child:
A parent has many children, and each child belongs to a parent. You build this type of relationship using the has many and belongs to data type columns.
Many-to-many relationships are less common and more challenging to build. An example of a many-to-many relationship is that between lawyers and matters, where multiple lawyers work on multiple matters.
To build this type of relationship you'll need a 3rd table known as a junction table. This is because in many-to-many relationships, the relationship itself is a piece of data that must be stored in a table. This also allows you to store attributes of the relationship. For example, where multiple lawyers work on multiple matters you may want to store data about the lawyer's relationship to the matter, i.e. is that lawyer the "first chair" on the matter? This is something you can save in the junction table.
