We need to build a sports management system that handles relationships between Teams, Players, Sports, and Fans. Think of this like managing a sports league where we need to track not just the teams, but their players, the sports they play, and their fan base. This requires understanding several key concepts:
First, imagine you're organizing a professional sports league. Just as a real league has teams that belong to specific sports, players who belong to teams, and fans who follow multiple players, our database needs to model these same relationships. We'll need to handle cases like players changing teams, fans following multiple players, and making sure we can efficiently retrieve all this connected information.
Let's create a new migration to add our foreign key relationships:
// migrations/XXXXXX-add-relationships.js
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
// Add currentTeamId to Players table
await queryInterface.addColumn('Players', 'currentTeamId', {
type: Sequelize.INTEGER,
allowNull: true,
references: {
model: 'Teams',
key: 'id'
},
onDelete: 'SET NULL'
});
// Add sportId to Teams table
await queryInterface.addColumn('Teams', 'sportId', {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: 'Sports',
key: 'id'
}
});
// Create DraftPicks join table
await queryInterface.createTable('DraftPicks', {
fanId: {
type: Sequelize.INTEGER,
allowNull: false,
references: { model: 'Fans', key: 'id' },
onDelete: 'CASCADE'
},
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) => {
// Reverse all changes
await queryInterface.removeColumn('Players', 'currentTeamId');
await queryInterface.removeColumn('Teams', 'sportId');
await queryInterface.dropTable('DraftPicks');
}
};
We need to set up the relationships between our models:
// 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) {
// Team belongs to a Sport
Team.belongsTo(models.Sport, {
foreignKey: 'sportId'
});
// Team has many Players
Team.hasMany(models.Player, {
foreignKey: 'currentTeamId',
as: 'TeamRoster'
});
};
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) {
// Player belongs to a Team
Player.belongsTo(models.Team, {
foreignKey: 'currentTeamId'
});
// Player can have many Fans through DraftPicks
Player.belongsToMany(models.Fan, {
through: 'DraftPicks',
foreignKey: 'playerId'
});
};
return Player;
};
Now let's implement our API endpoints that use these relationships:
// GET /teams/:id with eager loading
app.get('/teams/:id', async (req, res) => {
try {
const team = await Team.findByPk(req.params.id, {
include: [
{
model: Sport, // Include associated Sport
},
{
model: Player,
as: 'TeamRoster' // Include all team's players
}
]
});
if (!team) {
return res.status(404).json({ message: 'Team not found' });
}
res.json(team);
} catch (error) {
res.status(500).json({ error: error.message });
}
});
// GET /sports with ordering
app.get('/sports', async (req, res) => {
try {
const sports = await Sport.findAll({
order: [['name', 'DESC']] // Order by name in reverse alphabetical
});
res.json(sports);
} catch (error) {
res.status(500).json({ error: error.message });
}
});
These database relationships mirror real-world sports management scenarios:
Just like how a real basketball player belongs to one NBA team at a time, our Player model belongs to one Team. When a player is traded, we update their currentTeamId, just like in real life.
Similar to how sports fans might collect player jerseys or cards, our Fan model can be associated with multiple Players through DraftPicks. This many-to-many relationship reflects how fans can follow multiple players and players can have multiple fans.
Just as the NFL consists of football teams and the NBA of basketball teams, our Teams belong to specific Sports. This helps organize teams by their respective leagues.
Here are some typical challenges when working with database relationships:
Circular Dependencies: Be careful when setting up relationships that might create circular references. For example, if Team references Player and Player references Team, you need to handle this carefully in your migrations.
N+1 Query Problem: Without proper eager loading, you might end up making multiple database queries when one would suffice. Always use include when you know you'll need the related data.
Cascade Deletion: Be thoughtful about what should happen to related records when a record is deleted. Should players be deleted when a team is deleted? Should draft picks be removed when a fan is deleted?
To deepen your understanding, try these additional challenges:
Add a Match model that references two teams (home and away) and tracks game results. This introduces the concept of a model referencing the same table multiple times in different contexts.
Implement a team transfer history system that tracks when players move between teams. This introduces temporal data tracking in relationships.
Create a statistical summary that requires joining and aggregating data across multiple relationships. This practices complex queries with relationships.
To verify everything works correctly:
npx sequelize-cli db:migratenpx sequelize-cli db:seed:allnpm test