Note: This article was written in a different manner than all previous articles. All previous articles were written simultaneously to the code/project that they discuss. For this article, I took notes on what I was doing and started the writing after the discussed work was completed. Still finding my voice as it were, I guess.

So far in my NFL Project, I started out (perhaps backwards) by first exploring the available game data on NFL.com. I then experimented with the Nokogiri Gem so that i could suss out game ids from the summary page for a given week of games. After determining that there was enough data (to me) readily available (i.e. free) to download, I set about building my NFL application. The first step, of course, was setting up the application. Then I moved onto the foundational models for both division and team data. All models were built, and tested, to make sure they were set up correctly and validated data as I wished them to.

Now that those basic, and mostly static, models have been completed, it’s time to move on to the slightly more complicated aspect of where to put the individual game information and the teams participating in said game.

As I’ve mentioned earlier, a project like this is something that has been rattling around in my head for a rather long time. Long before I knew how to write any programming languages, in fact, I was thinking about how to structure a database to track the results of a sporting league. In my past I have taken some, let’s call them continuing education courses in the basic principles of database design, as well as courses in specific RDBMS Systems (SQL Server 2003 and MySQL to be precise). As I learned, it was always in the back of my mind how I could structure this idea properly. I even asked one of my professors, but he wasn’t all that helpful (or I explained myself poorly) in providing any guidance. Of course, you might be asking yourself now, what did you need guidance on John? I needed guidance on how to build my database tables correctly.

At first blush you might think that there is no question about how to set up the database tables, and at first I thought so too. I thought just shove it all into the same table, you’d have a column for the home team, away team, the winner, and the loser, and you could probably work out how to have the total score in there. Even though I wasn’t fully versed on what the concept of normalization was, this just seemed wrong to me. I would write the queries in my head and they just wouldn’t make sense. For instance, if you wanted to find all the games that one team played in you’d have to look through not only the home team column, but the away team column as well. Similarly, to find a teams record you’d have to look at not just the win column, but the losing column as well.

This was a conundrum in my brain that I thought about off and on for a while. It probably took me longer than it should have but one night, right before I went to sleep, I realized that what I needed to do was to break out the information that was purely about the game from the teams participating in the game.

Now this solution is probably obvious to the mature seasoned database developers but at the time, I was not one of those (I’m more seasoned than I was but I could probably use a lot more seasoning if you wanna know the truth), and it was one of those cool moments where you solve a problem you’ve been working on for a while and get that happy feeling us aspiring developers get (aspiring developers know the one, right?). Anyway, I had now solved (I think) the way I would present data in any sports league databases I would build for the future. Two tables, related to each other, with validations and restrictions, would do the trick. (It does the trick for SQL pretty easily, so far solving the ActiveRecord queries has proven a harder task). The two tables are:

  • Games - This is information that is specific to the game only. The game date, the attendance, perhaps even the weather. This stuff is purely about the game, and has no connection to either team playing in the game. (You might ask, what about the officials John? If you want to track officials, I suggest using another table related to the game so you can look at specific officials easily, as most team sporting events have multiple officials.)
  • Participants - this is the information specific to each team playing in the game. There is a data attribute that relates to the Game itself, but instead of having one column for home team and one column for away team, you just have one column that indicates whether the team is home or away and another column for winning and losing. This also provides a simple solution for associating scoring with teams without complicated querying later on.

So that’s what this article is about, building the Game and Participant models, testing them, along with a little seed data preparation, which is needed for some of the more complicated tests.

The Game Model

The Game model itself is pretty simple. Since I’m aiming for the MVP (minimal viable product) here and the goal is the statistics from the game, the only information needed at the start is the date of the game and the unique identifier provided by the information downloaded from NFL.com.

Creating this model via migration leads to this code

  def change
    create_table :games do |t|
      t.date :gamedate
      t.integer :nflcomid

      t.timestamps
    end
  end

After running the migration, a few simple tests using shoulda-matchers makes sure that both the game date and NFL.com id are present, unique, and in the case of the NFL.com id, stored as an integer. (The NFL.com, as described in those early articles, is a long integer that represents the date of the game and ‘number’ of the game on that date). It’s actually a pretty simple build and test, but it has to be done before I can build the Participant model.

The Participant Model

The Participant model is slightly more involved, complex, and dependent on not just the Game model described above but the Team model built earlier (and as we’ll see later, shoulda-matchers just doesn’t want to deal with that easily at this time)

In most sporting events, there are two participants (either teams or players) who complete against each other, so in the Participant Model, you have two rows per game, one for each participant.

Thus, each participant belongs_to one specific game. And, each participant has to be a team that is part of the league. As we have built a Team model to store all the team information, we don’t replicate it in the Participant model, we just say that each participant belongs_to one specific team as well. As stated earlier, whether a team is home/away or the winner/loser can also be stored as individual attributes of the row. Scoring in some sports (like the NBA or MLB) where there is no guarantee of exactly how long a game may last, can be confined to another model, but in the NFL, there are at most 5 periods of play. There are 4 15 minute quarters, and in case of a tie after those 4 quarters, one 15 minute overtime is played. For now, I’m keeping the scoring in the Participant model structure. If it needs to move later, that’s something that can be accomplished (and could be a good learning project once I get the MVP launched). Thus, there needs to be data attributes available to cover the scoring in these five periods of play. (Not all games go to overtime of course, but you always have to have the possibility of dealing with it already built for when it happens). So with that, the migration is written, and the code to create the participants table is created:

  def change
    create_table :participants do |t|
      t.belongs_to :game, foreign_key: true
      t.belongs_to :team, foreign_key: true
      t.string :homeaway
      t.string :winlosstie
      t.integer :q1
      t.integer :q2
      t.integer :q3
      t.integer :q4
      t.integer :ot

      t.timestamps
    end
  end

(The column is called winlosstie instead of winloss because the NFL does allow for ties…the NBA and MLB do not)

After the migration is run, the shoulda-matchers are again used to test all the basic validations of presence. In addition, the shoulda-matchers are used to determine that homeaway can only be either H (for home) or A (for away), and that winlosstie should only be W, L or T (should be pretty clear what they represent). There was one more validation I wanted to test, which is a standard Rails tool, but it proved harder than I thought to get working.

A team can play in many games, and a game has two participants, so you can’t have the game be a unique attribute all by itself, nor can you have the team be a unique attribute all by itself or your database will never work properly. In the database parlance I was used to using before I starting working with coding languages, I needed a compound primary key. In simple terms, this means that the combination of the game and team must be unique. As this is pretty common in database design (consider a shopping cart application you might use, if you add an item multiple times, does it show up multiple times in the cart? Well, it might, but a well written database will instead increment the item cart total by one instead of creating an entirely separate line), Rails has a way to write the validation within your rails code using scope:

 validates :team, uniqueness: {scope: :game}

and, shoulda-matchers does have a built-in method for checking that kind of validation, but there is a catch. After much research on github and stack overflow, I discovered that the built-in scope matching functionality of shoulda-matchers does not work when the uniqueness scope is not part of the model being tested (in this case the Participant model). Since my uniqueness statement was scoped to another related table, I had to come up with another solution, and that did take some time.

The solution I came up with might not be the most elegant or what most seasoned developers would do, but it does work and the test passes. This is what I came up with

  describe "Verifying scope of team within game" do

    it "should not allow the same team to be entered as a participant for the same game" do
      Participant.create(team: Team.first, game: game, homeaway: "H", winlosstie: "W", q1: 7, q2: 10, q3: 14, q4: 10, ot: 0)
      test = Participant.create(team: Team.first, game: game, homeaway: "H", winlosstie: "W", q1: 7, q2: 10, q3: 14, q4: 10, ot: 0)
      expect(test.errors.full_messages).to include("Team has already been taken")
      expect(test.valid?).to eq false
    end
  end

So, using a game variable created by the FactoryGirl gem, I create one participant manually and then try to create another participant for the same game that uses the same team and tell my testing suite that I expect errors to occur.

I tried at first using FactoryGirl to create teams, but that didn’t work out very well, so instead, I decided to take a small detour and work on creating (and testing) the seed file. As previously discussed, the information for each team is pretty fixed and static, so it makes sense to auto-populate the team information. Since this is an open source project and anyone can copy it if they like, it’s nice to provide them with such pre-packaged information as you need. Rails of course gives you a way to do this via the seed file. Since I needed to access the teams for my test anyway, I took some time out and wrote out the beginnings of the seed file for this project. It contains code to create the divisions and teams if necessary, and can be seen here if you want to take a look. I used hashes and arrays to build up the team information and as such, I wanted to test if it would run properly. I looked around and saw that testing seed data wasn’t common within a Rails application, but I did come up with a way to test it using RSpec that works for now. Using Rails.application.load_seed within the top of my RSpec file,I ran tests to make sure that a variety of basic queries would give the correct results. This verified that my seed file had been built properly (though in the future I might have to alter this due to keeping the test database clean between testing runs, but as of now so far so good).

After I had completed my seed file creation and testing, the test that I wrote for the uniqueness scope passed successfully, and the Participant model was completed to my satisfaction. The testing suite while good for now isn’t comprehensive. For instance, what if someone tries to add a third participant to a game? Right now the application would allow that. However, since the plan is to populate automatically behind the scenes, I don’t feel it necessary to test now. If I were building an application that was more generic regarding league information, and manual entry, stronger testing boundaries would probably be required.

And so, after a quick add, commit, push of the local git repository, the work I wanted to get done for this “Part 6” was done, and I could get ready to move on to the next step, which involves taking the Nokogiri and JSON work done in the early parts and writing code to populate the proper models (Game and Participant) with dynamic information downloaded from NFL.com. That will come next.