Database

Overview

FW uses MySQL for its SQL (structured query language) database. Briefly, SQL is a language used to retrieve, store, and modify data in a database system. MySQL is an open source implementation of SQL.

Most of the database calls are abstracted out through query builders, meaning minimal MySQL knowledge is required to write FW apps. Taking a class on SQL (many computer science majors end up taking COMP 245: Database Fundamentals) or finding an online tutorial will be helpful, but in general a lot of the database topics can be researched when needed.

For more information, see the following links:

• Overview of MySQL
• Tutorial on basic SQL commands

Database tables

In FW, each app maintains its own tables. The apps generally should not interact with tables from another app.

An exception to this is occasionally apps will interact directly with Group Control tables due to that app’s nature. This should be avoided if possible.

Columns

In SQL, database tables consist of a set of rows and columns. A row represents a single entry in the database, while a column represents a value that exists in that row.

Columns can store only “primitive” data types, such as integers, floating point numbers, and strings. They can also store some more complex values such as dates as internally they are represented as an integer. Columns cannot, on the other hand, hold an array of integers or a model value such as a user.

Primary key

All main tables in FW apps have a primary key as one of the columns. This key is easy to identify as it’s named with a short version of the table name followed by either ID or AID. The primary key must be unique both as a SQL requirement and for compatibility with Ember. Additionally, the primary key cannot be null.

Most models use an auto-incrementing integer ID, called AID in the key. This ensures that new records can easily be added without worrying about finding the next available ID or a unique string ID. Some apps, notably Group Control, use a string ID instead. This should typically be avoided unless there is a case where a unique but memorable ID is needed. An example of a good use of string IDs is for login usernames.

Database migrations

Rather than writing raw SQL to create database tables, we use Phinx Migrations. The initial migration will add all tables to the database based on how it was in the initial app release, while later migrations are patch scripts which add or change tables based on a single later update. This means when installing an FW app for the first time, instead of adding all the current versions of the database tables to the database, Phinx will first add the tables from the original release then run all patches to get the database up to date. For a more detailed guide on how to write a database migration, you can check out our Migration Core Docs.

In recent practice, every time a database migration is added to an app, we increase the second version number. For example, 0.3.1 and 0.3.2 will both have the same database patch version, but 0.4.0 requires running an additional migration. This means it’s possible to run two apps with different versions on the same database provided the first two numbers in the version are the same. Note this will not always be true during early releases of the app, meaning any release before 0.1.0.

Using migrations

Phinx has three basic functions you may see in a migration:

1. up(): This method is called for all relevant migrations when you run fw database migrate. Phinx will not attempt to run a migration if it already ran.
2. down(): This method is called for relevant migrations when you run fw database rollback. When you rollback a migration, it will be removed from the list of run migrations, so it will run when you run fw database migrate again.
3. change(): Some migrations have a change() function instead of up() and down(). change() behaves the same as the up() function on fw database migrate. The difference is when running fw database rollback Phinx will automatically generate the down function. Only specific database commands are allowed in change, for a full list see the Phinx documentation.

Both fw database migrate and fw database rollback take a parameter for the migration number. When given that parameter, Phinx will only migrate up to or rollback to the given migration number. Phinx will never attempt to rollback during the migrate command or vice versa, regardless of the number you type.

For example, fw database migrate 4 will run the first 4 migrations (assuming they have not yet run), but will not attempt to run migration 5, nor will it rollback migration 5. You can also see more details about the fw database command, by checking out its API Docs.

Database relationships

As mentioned before, database columns cannot hold an array of integers or a model such as a user. Instead, the way this is handled in MySQL is by creating a separate table to hold the complex value. After that, foreign keys are used to link the values together.

Foreign keys

A foreign key is a database column whose value is the ID column of another table. Typically these are stored as integer values, but for some tables a string ID is used. In either case, the foreign key column will have the same attributes as the ID column it references (so if the ID is a 10 character string, the foreign key will be a 10 character string).

The convention for FW apps prefixes all foreign keys with FK_, making them easy to distinguish among other keys. For more details on the Informatics Policy regarding naming databases, see Database Naming Conventions.

Foreign keys are used to perform several different relationship types, which will be discussed in the following sections. It should be noted that while relationships are all discussed as existing between two models, it is possible in some cases for a model to have a relationship with itself.

Entry value relationship

The simplest way to describe a relationship is through the entry value, though as will be discussed later this is not technically one of the relationship types. In this relationship, a log entry has a value which points to one of the models in the app. This signifies that value was somehow involved in the log entry.

In the database, this relationship is represented by a foreign key on the log entry pointing to the other model’s ID. On the client side, this is represented by a belongsTo relationship in the entry model.

An example of this relationship is in Status. Status task history contains a reference to a location, a responsibility, and an optional note. In the database, this can be seen by three foreign key columns: two required columns pointing to location and responsibility, and one nullable column pointing to note. On the client side, a belongsTo can be seen in the task history model for each value.

One to one relationships

The next simplest relationship is the “one to one” relationship. In most cases, it would make more sense to simply put the data inside the original table instead of using a relationship, though a common use for this is when a portion of a table’s data is pretty large. Using a “one to one” relationship makes it possible to fetch the data in a separate request or to skip requesting it entirely.

In the database, this relationship is represented by a foreign key in one of the involved tables linking to the ID of the other table. Typically, the foreign key will be on the “main” table, that is the one fetched the most often. In rare cases a foreign key exists in both involved tables, but that is typically avoided as it allows an invalid state when the keys do not match. On the client side, “one to one” relationships are represented by the belongsTo relationship.

An example of this type of relationship is images in checkout management. Each available item can have up to 1 image, and each image is only used in 1 item. In this case, the foreign key exists in the item table which is more commonly fetched, while the image table has no immediate indications that the relationship exists.

One to many relationships

A “one to many” relationship is the most common type of relationship. In this relationship, the “one” side is linked to many IDs within the “many” table, meanwhile the “many” side is only linked to a single model from the “one” side.

In the database, this relationship is structured similarly to a “one to one” relationship, except the foreign key is always on the “many” side. When fetching data from the “one” side, the query should also fetch a list of IDs to populate the “many” side that uses the “one”’s ID. On the client side, this would be represented by a hasMany relationship on the “one” side and a belongsTo relationship on the “many” side.

An example of this relationship services in Service Stats. Services “have many” fields. In the database this is represented by a foreign key in the field table that contains the service ID. On the client side, this is represented by a hasMany relationship in the service model and a belongsTo relationship in the field model.

Context examples

In the example above with services and fields, one detail that should be noted is that fields are closely related to the service; you never fetch a field without the corresponding service and rarely fetch the service without fetching all fields. In this case, the relationship can be thought of as services containing an array of fields.

Another case from service stats is with services and service categories. In this case, categories are sometimes fetched without fetching the child services, and services are often fetched without fetching their category. Essentially, the two models are related, but not dependent.

One last case of a “one to many” relationship that should be mentioned is the “entry value” relationship described above. That relationship has the same structure as a “one to many” relationship; using the example from above it can be thought that a location “has many” task history entries. However, we rarely think of that relationship as a “one to many” as history entries are typically fetched using additional filters to prevent fetching too much data.

Many to many relationships

A “many to many” relationship is the most difficult type of relationship. In this relationship, both sides of the relationship essentially have an array of values of the other model type.

The foreign keys for “many to many” relationships exist in a cross reference table, typically suffixed with Xref in FW apps. Cross reference tables contain only two columns, which are foreign keys to each of the two tables involved in the relationship. When fetching data from either side, the query also fetches a list of IDs from the cross reference table to determine the related models. On the client side, this relationship is represented by a hasMany relationship in both models.

An example of a “many to many” relationship is transactions and payments in Point of Sales. Transactions have a list of payments which are used to populate the payment options on the main page. However, a payment can be used in multiple transactions, meaning it does not make sense to use a single transaction foreign key in the payment table. Using a “many to many” relationship allows a transaction to have multiple payments while also allowing a payment to belong to multiple transactions.