Imagine we're building a digital version of a sports league management system. Just like how a real sports league has teams, players, and fans, our database needs to track all these relationships. Think of it as creating a digital mirror of how sports organizations work in the real world.
First, let's create our new migration to establish the relationships. This is like drawing the organizational chart for our sports league:
// migrations/YYYYMMDDHHMMSS-add-relationships.js
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
// Add currentTeamId to Players table - like assigning players to teams
await queryInterface.addColumn('Players', 'currentTeamId', {
type: Sequelize.INTEGER,
allowNull: true, // Players can be free agents
references: {
model: 'Teams',
key: 'id'
}
});
// Add sportId to Teams table - like categorizing teams by sport
await queryInterface.addColumn('Teams', 'sportId', {
type: Sequelize.INTEGER,
allowNull: false, // Every team must belong to a sport
references: {
model: 'Sports',
key: 'id'
}
});
// Create DraftPicks join table - like tracking fan favorites
await queryInterface.createTable('DraftPicks', {
fanId: {
type: Sequelize.INTEGER,
allowNull: false,
references: { model: 'Fans', key: 'id' },
onDelete: 'CASCADE' // If a fan is deleted, remove their picks
},
playerId: {
type: Sequelize.INTEGER,
allowNull: false,
references: { model: 'Players', key: 'id' }
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Players', 'currentTeamId');
await queryInterface.removeColumn('Teams', 'sportId');
await queryInterface.dropTable('DraftPicks');
}
};
Now let's define how our models relate to each other, just like describing the relationships between different parts of a sports organization:
// models/team.js
module.exports = (sequelize, DataTypes) => {
const Team = sequelize.define('Team', {
name: DataTypes.STRING,
homeCity: DataTypes.STRING,
sportId: {
type: DataTypes.INTEGER,
allowNull: false
}
});
Team.associate = function(models) {
// A team belongs to one sport
Team.belongsTo(models.Sport, {
foreignKey: 'sportId'
});
// A team has many players
Team.hasMany(models.Player, {
foreignKey: 'currentTeamId',
as: 'TeamRoster' // Giving a clear name to our player list
});
};
return Team;
};
// models/player.js
module.exports = (sequelize, DataTypes) => {
const Player = sequelize.define('Player', {
firstName: DataTypes.STRING,
lastName: DataTypes.STRING,
number: DataTypes.INTEGER,
isRetired: DataTypes.BOOLEAN,
currentTeamId: DataTypes.INTEGER
});
Player.associate = function(models) {
// A player belongs to one team
Player.belongsTo(models.Team, {
foreignKey: 'currentTeamId'
});
// A player can be drafted by many fans
Player.belongsToMany(models.Fan, {
through: 'DraftPicks',
foreignKey: 'playerId'
});
};
return Player;
};
Let's implement our API endpoints. These are like the different ways people can interact with our sports management system:
// app.js
const express = require('express');
const { Team, Sport, Player, Fan, DraftPick } = require('./db/models');
const app = express();
app.use(express.json());
// GET /fans/:fanId/drafts - Like getting a fan's fantasy team
app.get('/fans/:fanId/drafts', async (req, res) => {
try {
const players = await Player.findAll({
include: [{
model: Fan,
where: { id: req.params.fanId },
through: { attributes: [] } // We don't need DraftPick details
}]
});
res.json(players);
} catch (error) {
res.status(500).json({ message: 'Error retrieving draft picks' });
}
});
// DELETE /fans/:id - Remove a fan and their draft picks
app.delete('/fans/:id', async (req, res) => {
try {
await Fan.destroy({
where: { id: req.params.id }
});
res.json({ message: 'Successfully deleted' });
} catch (error) {
res.status(500).json({ message: 'Error deleting fan' });
}
});
// GET /teams/:id - Get team details with roster
app.get('/teams/:id', async (req, res) => {
try {
const team = await Team.findByPk(req.params.id, {
include: [
{
model: Sport // Include the sport info
},
{
model: Player,
as: 'TeamRoster' // Include the team's players
}
]
});
res.json(team);
} catch (error) {
res.status(500).json({ message: 'Error retrieving team details' });
}
});
// GET /sports - Get all sports, ordered by name
app.get('/sports', async (req, res) => {
try {
const sports = await Sport.findAll({
order: [['name', 'DESC']] // Reverse alphabetical order
});
res.json(sports);
} catch (error) {
res.status(500).json({ message: 'Error retrieving sports' });
}
});
// POST /teams/:id/players - Add a new player to a team
app.post('/teams/:id/players', async (req, res) => {
try {
const player = await Player.create({
...req.body,
currentTeamId: req.params.id
});
res.json(player);
} catch (error) {
res.status(500).json({ message: 'Error creating player' });
}
});
Eager loading in Sequelize is like getting a sports team's complete information package all at once. Instead of asking separately for the team info, their sport, and their players, we get it all in one request:
When we use include in our queries, it's like telling our database "give me everything related to this team that I might need". This is much more efficient than making multiple separate requests.
// Example of eager loading relationships
Team.findByPk(teamId, {
include: [
{
model: Sport, // Include sport information
},
{
model: Player,
as: 'TeamRoster' // Include all players
}
]
});
Sometimes we need to sort our data in specific ways, like organizing teams by their city and name. Here's how we handle complex ordering:
// Example from bonus phase - ordering teams by city and name
Team.findAll({
order: [
['homeCity', 'ASC'], // First sort by city
['name', 'DESC'] // Then by team name in reverse
]
});
This system mirrors many real-world sports management scenarios:
The fan-player relationships through DraftPicks is similar to how fantasy sports platforms let users draft their dream teams.
The team-player-sport relationships mirror how professional sports leagues track their organizations, rosters, and different sporting divisions.
The ability to query related data efficiently is crucial for sports statistics and analysis platforms.
Remember that players can exist without a team (free agents), but teams must belong to a sport. Our schema reflects this with appropriate null constraints.
When a fan is deleted, their draft picks should be removed too. We handle this with onDelete: 'CASCADE' in our migration.
Use eager loading judiciously - while it's convenient to get all related data at once, requesting too much can slow down your application.
Consider these additions to make the system more robust:
Add player transfer history tracking.
Implement team statistics aggregation.
Create endpoints for complex queries like "find all players in a specific sport".
Add support for player trades between teams.