Data Design

In most applications, regardless of programming language, a traditional database is used to store persistent system- and user-based information. What makes this database “traditional” is its use of independent relations (or tables) of related information. Each table consists of a set of tuples (or rows) comprised of attributes (or columns); with each tuple uniquely identified by a key, allowing links between relations to be defined.

Object-relational mapping (i.e. ORM) is a strategy to map relations directly to programming objects. Each row becomes a logical model of database attributes, identified by the relation’s unique key. Links between relations are maintained, allowing for data relationships to be traversed between models. These relationships are the fundamental structure for data design.

Many programming frameworks come bundled with an ORM, e.g. Django, Laravel, Ruby on Rails; while other libraries such as SQLAlchemy and Sequelize can be integrated as desired.

A one-to-one relationship is defined by two entities A and B having exactly one relationship between them. It can be said that “A has one B”, and vice-versa. This is modeled in the database by one of the tables storing the other’s primary key (PK) as a related attribute, referred to as a foreign key (FK).

Along with our Player model above, a second data model could list batting records, including strike outs, hits, bases gained, and scoring runs totaled. This model would need to have a foreign key to relate each record back to a single player. Batting records would have one (i.e. belong to a) player record, and players could have one (or possibly no) batting record.

class Player < ApplicationRecord
  has_one :batter
end

class Batter < ApplicationRecord
  belongs_to :player
end

use Illuminate\Database\Eloquent\Model;

class Player extends Model 
{
    public function batter()
    {
        return $this->hasOne(Batter::class);
    }
}

The benefits of a one-to-one relationship are not always apparent. After all, the batting records could be attributes of the player relation itself. Consider , however, players who might not have any batting statistics, such as relief pitchers. The one-to-one relationship prevents this by “extending” the player table when appropriate.

The final entity-relationship model to discuss involves many-to-many relationships: an entity in table A can have many entities in table B, and an entity in table B can have many entities in table A. Sometimes this will be referred to as “A has and belongs to many B”. Unlike our previous relations, we can no longer use a single foreign key in one of the associated models to identify our relationships. Instead, an intermediary (or association) table is needed, where each row is comprised of foreign keys identifying each of the related models.

The final expansion of our baseball app will add games to our logical models. Players and games will comprise a many-to-many relationship whereby each player will play in many games, and each game will have many players.

With an ORM, the association table is often hidden behind the many-to-many implementation. In the following example, three tables would be generated by Django, one each for the Player and Game models, as well as an association players-games table with one foreign key each for unique combinations of player and game:

from django.db import models

class Game(models.Model):
    # ...
    players = models.ManyToManyField(Player)

This hidden association table, however, is not difficult to build on our own, and would offer many additional benefits beyond a simple ORM association. For example, consider using our batters model as the intermediary between players and games. By adding a foreign key to games, our batters model now can be used to calculate player statistics using game filters, i.e. opponent or date.

const Player = sequelize.define('Player', { /* ... */ });
const Game = sequelize.define('Game', { /* ... */ });
const Batter = sequelize.define('Batter', { /* ... */ });

Player.belongsToMany(Game, { through: Batter });
Game.belongsToMany(Player, { through: Batter });

Most ORM associations can be extended with custom tables, but vary in complexity and usage. Ultimately, however, they’re all just syntactic sugar to access distant models using custom intermediary models as association tables.