0.6.13 Entity Relationship Diagrams

ERDs: Organize Soccer Records and Their Relationships

The Wildcats league stores teams and player registrations in structured records. Repeating a team's full name in every player record makes updates harder: one spelling change may need many edits. A relationship based on a stable identifier gives each record a clear connection to its team.

An entity relationship diagram, or ERD, describes kinds of stored records, their attributes, keys, and permitted relationships. It describes data structure, not the order of a training session or the current contents of one running Player object.

Choose Entities and Keys

For this small roster model, Team records have TeamId and Name. Player records have PlayerId, Name, JerseyNumber, and TeamId. TeamId identifies a Team record; PlayerId identifies a Player record. A primary key uniquely identifies a row within its entity. A foreign key records a connection to an identified row in another entity.

Assume each player registration belongs to exactly one team, and a team may have zero or more registered players. These are this model's requirements, not universal rules about every soccer league.

Each Player row has its own primary key and a TeamId foreign key. Each belongs to one Team; a Team can have zero or many Players.
A team key connects many player records
  • New
  • Changed
  • Removed
Read the Relationship in Both Directions

From Player to Team: each player record belongs to exactly one team. From Team to Player: one team can have no players yet or many players. The crow's foot marks many; the circle permits zero; the bars indicate exactly one at the Team end.

For example, player record P007 might store Jordan, jersey 7, and TeamId T01. Team record T01 might store Wildcats. The foreign key joins those facts without copying the complete team record into the player record.

Build from Requirements
  1. Identify the kinds of records, not each sample row.
  2. Choose keys and place each attribute with the record it describes.
  3. State minimum and maximum counts in both directions before drawing the relationship.
  4. Test a zero-player team and an attempted player with a missing TeamId against the stated rule.