Jerseys as Join Tables: Using Real-World Examples to Understand Relationships Between Data

🚀 Introduction

Understanding data relationships, like any new material is easier when you understand the WHY behind the concept instead of just memorizing it. In this post, I want to go over the 3 main types of data relationships with real-life examples.

We'll be using SQL-Alchemy for the coding portion of these examples. We won't be going over the setup portion of using a database with SQL-Alchemy. Instead, we'll just focus on what the models for each object look like and how to create and utilize relationships between them.

📱 One-to-One Relationships

A one-to-one relationship is pretty simple to understand, so we won't spend too long on it.

Real-World Example

Most people have one smartphone that they share with no one. The owner owns one phone and the phone belongs to one owner.

We don't tattoo the model number of our smartphones on our wrist so everyone knows which phone is ours. Instead, we register the phone to an account in our name. This way, if someone stumbles upon a lost phone, they can find the owner.

In Code

In databases, developers follow the same pattern. The owned object keeps track of its owner using the owner's ID as a foreign key. This is consistent with conventions we'll see later on.

We'll use a reference to the owner's ID, also known as a foreign key, to store the relationship.

class Owner(db.Model):
    __tablename__ = 'owners'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    # Define one-to-one relationship with Smartphone
    phone = relationship("Smartphone", back_populates="owner")


class Smartphone(db.Model):
    __tablename__ = 'smartphones'

    id = Column(Integer, primary_key=True)
    model = Column(String)
    owner_id = Column(Integer, ForeignKey('owners.id')) #foreign key

    # Define one-to-one relationship with Owner
    owner = relationship("Owner", back_populates="phone")

SQL-Alchemy also gives us some additional tools to access the relationship, shown as phone and owner. While these values aren't stored in the database, you can access them and make changes. SQL-Alchemy understands the relationship and will set the corresponding values.

For example, if a guy named Jack lost his phone and bought a new one, you could write

new_iphone = new Smartphone(model="iPhone X")
jack.phone = new_iphone
db.session.add(new_iphone) #add new phone to db session
db.session.push()

and a new smartphone object would be created with owner_id set to Jack's ID. We have to add the new_iphone to our db session to make sure it records our changes to the database. We don't have to add jack because the relationship is held in the smartphone!

⚽ One-to-Many Relationships

One-to-many relationships usually related to an object that owns another object. While there are other cases, we'll simplify by assuming all one-to-many relationships can be explained this way.

A Note on Lists

The trouble with holding a list in a database is that it's so inefficient that it should be considered impossible.

When a computer creates an object, it allocates a specific amount of memory for that object. If a list has variable length, the computer won't know how much space to allocate to the list. If it allocates too little and we keep adding onto the end of the list, the computer will have to copy the object, find a new larger space for it, paste the object there, and delete the old version. After all that, then the computer can actually add the new items to the list.

This is a quite simplified version of what would occur in the database, but you can start to see why this would be such an absurd way to plot the information. Let's use an example to understand a better solution.

Real-World Example

To keep with our sports theme, let's imagine a scrimmage between soccer teams from different neighborhoods. Each soccer team brings its own gear to use for the match, most important of which are the soccer balls.

How do the teams keep track of which balls are theirs at the end of the day? Each team could keep a list of which balls belong to them and how many of each ball they own. That way, if there's a dispute between teams they could refer to this list.

But that's silly! Instead, teams label all their gear with a marker. That way, anyone can pick up the ball and know who it belongs to.

Andy uses the same concept in Toy Story. He writes his name on the foot of all his toys instead of tattooing a list of every toy in his toybox on his own foot.

In Code

Developers use the same logic when arranging one-to-many relationships in a database. Each owned object holds a reference to its owner.

The relationship will look identical to a one-to-one relationship.

class Team(db.Model):
    __tablename__ = 'teams'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    # One-to-many relationship with SoccerBall
    balls = relationship("SoccerBall", back_populates="team")

class SoccerBall(db.Model):
    __tablename__ = 'soccer_balls'

    id = Column(Integer, primary_key=True)
    color = Column(String)
    team_id = Column(Integer, ForeignKey('teams.id'))

    # Many-to-one relationship with Team
    team = relationship("Team", back_populates="balls")

The only change we make as changing is making the Team's property to a plural balls so we know to expect a list of balls. SQL-Alchemy does the rest of the work to show us the list of balls when we ask for them.

We can buy a new ball for the soccer team, the Springfield Blazers, the same way we replaced Jack's smartphone earlier.

new_ball = new Ball(color = "green")
springfield_blazers.balls.append(new_ball)session
db.session.add(new_ball) #add new ball to db session
db.session.push()

Again, we dont have to add the changes to springfield_blazers to our db session, because the relationship is stored in the new_ball object.

🎽 Many-to-Many Relationships

Many-to-many object relationships are a little trickier to figure out. These relationships typically are not owner-owned relationships. They are usually participating-in or part-of relationships.

The tricky part of these is that no matter which side you hold the relationship on, one side will have to hold a list of the other object ...right? (And we saw how tough that would be for the computer to manage) Nope. Let's check back in with our soccer teams for another example.

Real-World Example

Let's imagine the goalkeeper of our soccer team. Her name is Mia. Mia is a goalkeeper on her neighborhood team, the Springfield Blazers in the summer. However, in the spring she plays for her school team, the Memorial High Wildcats. Her performance on these 2 teams has been so excellent that she joined the Youth National Team for her country.

When Mia's on the field, how do spectators, coaches, and other players know which team she plays for? It would be silly if the coach held a huge poster with the roster up on the sideline.

Instead, Mia wears a jersey. Her jersey has the team's name and mascot on the front, and her name and number on the back. Spectators can look at any jersey and immediately know the identity of the player and the team they represent. This jersey represents an intermediary object that holds a relationship to Mia and to her team.

In Code

Relationships between objects in a database work the same way. We need an intermediary object that can hold a reference to both sides of the relationship. This way, neither side has to hold a list of references to objects from the other side. This intermediary object is called an association table or join table.

# The intermediary object
player_jersey = db.Table('association',
    db.Column('player_id', db.Integer, db.ForeignKey('players.id')),
    db.Column('team_id', db.Integer, db.ForeignKey('teams.id'))
)

class Player(db.Model):
    __tablename__ = 'players'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    teams = db.relationship("Team", secondary=player_jersey, back_populates="players")


class Team(db.Model):
    __tablename__ = 'teams'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    players = db.relationship("Player", secondary=player_jersey, back_populates="teams")

In this case, editing the player_jersey table looks a little different, but accessing a team's players looks the same.

# adding Mia to the Youth National Team
new_association = association_table.insert().values(player_id = mia.id, team_id = youth_national_team.id)

db.session.execute(new_association)
db.session.commit()


# getting the roster of Youth National Team Players
print (youth_national_team.players)

For simplicity's sake we're not including Mia's number on her jersey. If we wanted to do that, we would have to create a new model with properties, like this. The other models would remain the same.

class PlayerJersey(db.Model):
    __tablename__ = 'player_jersey'

    player_id = db.Column(db.Integer, db.ForeignKey('players.id'), primary_key=True)
    team_id = db.Column(db.Integer, db.ForeignKey('teams.id'), primary_key=True)
    jersey_number = db.Column(db.Integer)

    player = db.relationship("Player", back_populates="teams")
    team = db.relationship("Team", back_populates="players")

In this case, we can modify the PlayerJersey like any other object and the relationship will update automatically. We can also access a team's players or a player's teams the same was as with the association table.

😉 Conclusion

Hopefully that was helpful! SQL-Alchemy is a powerful tool once you understand it and it can do far more than I showcased here. Happy coding!