Appendix C: The Wrox United Database
Chapter 10 in this book, we worked through examples that were based around a fictitious soccer league team called Wrox United. These examples relied on a database for match and team information. In this appendix, we'll look at:
How the Wrox United database is structured
Downloading the database from the Wrox Web site and preparing it for use
As we've seen in the examples in Chapters 10 to 13, the Wrox United database has several different tables that store data about teams, players, matches, and much more. Let's take a look at a diagram of the database. The Figure C-1 was produced in the Relationships view in Microsoft Access:
Figure C-1
Let's look at each of the different tables in turn, and see what columns they contain.
This table holds the details for each player in the club. These players can be members of one or more teams and can play in different positions. The
Players table holds the core information for each player and the foreign key information to link to the
Status table, which means that each player can be flagged as active, injured, or retired. This is a one-to-many relationship, where each player can be one of many possible status types.
|
Column |
Type |
Description |
|---|---|---|
|
PlayerID |
Integer / AutoNumber Primary Key, |
Unique Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
PlayerName |
String / Text, 50 characters |
The name of the player. |
|
JoinDate |
DateTime |
The date that the player joined the club. |
|
Status |
Integer / Number Foreign Key (to the StatusID column on the Status table) |
A link to the Status table, used for specifying whether a player is active, injured, or retired. |
|
Profile |
String / Text, 255 characters |
A brief description of the player. |
|
SiteLogin |
String / Text, 20 characters |
A login name used to access restricted parts of the site. |
|
SitePassword |
String / Text, 20 characters |
A password used to access restricted parts of the site. |
| Note |
Primary key-foreign key one-to-many relationships are a core part of relational database design, and enable us to minimize repetition of data in the database. It also aids consistency, and avoids several different variations of the same data being stored. For example, without this relationship, we could include a Status column in the Players table to store text describing the active status of a player. This is a fine solution if you have tight control over the data that's inserted into the database, but if you allow users to enter custom strings of text for the values in this column, you could end up describing a player as being injured using the text "injured", "Injured", "sick", or "ill" instead of simply selecting the Injured status from the Status table. |
Status table stores the availability and status information that each player can select from or specify. The available status types in the samples provided in the code download are
Active ,
Injured , or
Retired . However, you can enter whichever type you like in your database.
|
Column |
Type |
Description |
|---|---|---|
|
StatusID |
Integer / AutoNumber Primary Key, |
Unique Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
Status |
String / Text, 50 characters |
Description of the status (injured, active, retired). Each player in the Players team has to select one of these values. |
The
Teams table stores information about each team in the Wrox United club. The name of the team and any associated notes can be stored here.
|
Column |
Type |
Description |
|---|---|---|
|
TeamID |
Integer / AutoNumber |
Primary Key, Unique Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
TeamName |
String / Text, 50 characters |
The name of the team. |
|
Notes |
String / Memo |
Description of the team, plus any additional information that may be useful. This field is long enough to hold several thousand characters of data. |
This is a join table between the
Players table and the
Teams table. One player can be in many teams, and each team consists of many players. In this situation, we have a many-to-many relationship, and thus need to include a join table between these two tables to store information about both sides. In this way, the
PlayerTeams table now stores many unique combinations of players and teams (hence if you were one of the players, you could join Team A only once but you could also join Team B).
The other item of interest in this table is the
Position column, which is a foreign key link to the
Positions table. This enables you to specify the position for each player in a given team.
|
Column |
Type |
Description |
|---|---|---|
|
PlayerID |
Integer / Number Part of Primary Key |
Link to the ID of the Player. |
|
TeamID |
Integer / Number Part of Primary Key |
Link to the ID of the Team. Used together with the PlayerID column, the Primary Key constraint specifies that each combination of PlayerID and TeamID must be unique. |
|
Position |
Integer / Number Foreign Key (to the PositionID column on the Position table) |
Used to specify, for each combination of Player and Team, the position the player plays in on that team. |
This table stores details of the available positions that a player can assume in a particular team.
|
Column |
Type |
Description |
|---|---|---|
|
PositionID |
Integer / AutoNumber Primary Key, Unique |
Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
PositionName |
String / Text, 50 characters |
Name of a position (left wing, defence, and so on). |
The
Games table stores information about each match, including who is playing the match, where the match will take place, what type of match it is, when the match is scheduled, and the score, when known. Many columns in this table are foreign keys to other tables, reusing data and centralizing information where possible.
|
Column |
Type |
Description |
|---|---|---|
|
GameID |
Integer / AutoNumber Primary Key, Unique |
Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
Location |
Integer / Number Foreign Key (to the LocationID column on the Locations table) |
Used to choose a location for the game, from the list defined in the Locations table (for example, home or away.) |
|
WroxTeam |
Integer / Number Foreign Key (to the TeamID column on the Teams table) |
Link to the Teams table to select which Wrox team is participating in the game. |
|
OpposingTeam |
Integer / Number Foreign Key (to the OpponentID column on the Opponents table) |
Link to the Opponents table to select which opposing team is participating in the game. |
|
GameType |
Integer / Number Foreign Key (to the GameTypeID column on GameTypes table) |
Link to the GameTypes table to select the type of game (for example, a friendly or league match.) |
|
Date |
DateTime |
The date of the match. |
|
WroxGoals |
Integer / Number |
The number of goals scored by the Wrox team. |
This table is used to store the 'type' of games – for example, friendly or league.
|
Column |
Type |
Description |
|---|---|---|
|
GameTypeID |
Integer / AutoNumber Primary Key, Unique |
Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
GameType |
String / Text, 50 characters |
The type of a match, for example, friendly or league. |
This table stores types of location for a game – for example, home or away.
|
Column |
Type |
Description |
|---|---|---|
|
LocationID |
Integer / AutoNumber Primary Key, Unique |
Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
LocationType |
String / Text, 50 characters |
The type of location (for example, home or away.) |
Opponents table stores details of the other teams in the league, and details of where they are based.
|
Column |
Type |
Description |
|---|---|---|
|
OpponentID |
Integer / AutoNumber Primary Key, Unique |
Unique identifier for each row in the database, generated automatically by the database whenever a new row is inserted. |
|
OpponentName |
String / Text, 50 characters |
The name of the opponent. |
|
OpponentDescription |
String / Memo |
A description of the opponent that could be used, for example, to describe their strengths and weaknesses. |
|
OpponentLocation |
String / Text, 50 characters |
The name of the home location of the opponent. |
This table is completely standalone compared to the other tables. It has only one column and is used for storing email addresses of fans. You could expand this table to store more details about each of the fans registered in the database. Since email addresses are unique to an individual (in most cases), we can make this column the primary key for the table without the need for an additional key column.
|
Column |
Type |
Description |
|---|---|---|
|
FanEmail |
String / Text, 255 characters Primary Key, Unique |
The email address of a fan. Since email addresses are individual, this field can be given a unique constraint and assigned as a primary key for the table. |