I have been meaning to be more active with a dev blog, and was recently inspired by Kevin Powell's podcast to start blogging more. Thanks Kevin!
I have been running a College Football 2025 Online Dynasty with a group of a dozen or so friends since the game released back in June. We've been having a blast and are on our 3rd season. One of the major downfalls of the game, though, is that the in-game stats, trophies, and rewards are seriously lacking. We have a discord channel and a Google sheet that we use to keep a record of results and long-term stats. This is fine, but the web-dev in me saw this as a great opportunity to make a really cool website.
Historically, this has always been a downfall for me. I am what some may refer to as a "Front of the Front-End" developer. I specialize in creating accessible HTML mark-up and functional CSS (and client-side JS). I know what's possible with technology, but typically always struggle in making all of the pieces fit. Not this time, though! I am creating this blog post to hold myself accountable and to share my struggles, learnings and knowledge with the world.
Keeping Track of Data
I created a Google Sheet that was accessible to myself and other members of the league with columns for opponents, scores, and other individual game-specific data. A quick google query showed that there is a Google Sheets API available that I could leverage to pull data from the sheet into a website! How much simpler can you get? Simple is something I'm championing throughout this project. Not only because I don't want to overwhelm myself, but because I'm creating a relatively static website with the audience of maybe 20 people tops.
However, after 10-12 hours of wrestling over the course of the past month, I have learned that this Google Sheets API is not as simple as I'd have hoped. It at least has the extra challenge of being less popular than other options, so there isn't a ton of Stack Overflow-esque troubleshooting for me to do. All of the troubles I was running into felt extremely unique and difficult. I also made the mistake of following a YouTube tutorial to get the project set-up initially, which skipped over some Node.js set-up stuff that I'm relatively uncomfortable with. And, on top of that, for some reason I decided that this was a great time to leverage React, which I'm by no means an expert in. Basically, I bit off more than I could chew. I realized I needed to chunk up my battles to make this project more manageable for myself.
SQLite
After deciding that Sheets was not going to be the answer for me, I did some research into what the best options for light-weight databases. I am not knowledgeable in this area at all. I took a SQL class in 2018, but have not had to use it even one day in my day-to-day life. I found lots of articles and Reddit threads of people suggesting SQLite for small projects. This seems perfect for what I am trying to accomplish.
I knew I needed to design my database and determine what kind of tables and columns I'll need to create the components I want. I basically want to make a copy of any league's website you'd see (NHL.com, MLB.com, etc.). I did a quick search and found https://dbdiagram.io/. I played with this tool and was able to design a basic database for my site. I found this tool extremely valuable for myself as it let me get familiar with SQL commands and jargon without actually having a database yet.
With confidence high from setting up this diagram, and believing that I think I actually understand what I'm trying to do (Remember, I am an SQL-newbie), I fired up SQLiteStudio (GUI for SQLite) and got started. I input all of the data for my user, teams and conference tables. With the help of w3schools I was actually able to run an SQL query that returned data from 3 separate tables! Great success!
SELECT users.username, teams.school_name, teams.nickname, conferences.conference_name
FROM users
INNER JOIN teams
ON users.current_team=teams.team_id
INNER JOIN conferences
ON teams.conference=conferences.conference_id
Up Next
This was my success of this past weekend. I was pretty excited to be able to ideate, design and architect this basic database with very little friction. Up next, I am going to try to understand Node.js a little better so that I can work on using JavaScript to actually use this data on my web project. I've used Node.js before but have little to no understanding of what it is actually doing. I plan on doing some tutorials and running through w3schools to help me here before I jump feet-first into this.
Thanks for reading and feel free to share any resources you think might help me along the way!
SOCIAL SHARE CARD GENERATOR