/return home Thu Nov 02 2023

Designing UFC database tables with PostgreSQL

One of my favourite past times is to watch MMA, more specifically the biggest MMA promotion in the world; The Ultimate Fighting Championship (The UFC). Ever since learning to code, I’ve had the idea of a UFC type project in the back of my head constantly, I did not know what type of project but I knew that using my skills to create something related to the UFC was my goal.

After doing some research of other peoples projects related to the UFC, I began to realize the lack of (or total absence of) API’s related to UFC data, this confused me, because how could there not be the UFC is a billion dollar company and with millions of viewers tuning into watch each event. Some research later, and again I was shocked to learn their was not even a public database with current and former UFC data online, by then I had decided to set my mind to creating a complete UFC dataset, of every historical UFC event, every fight and every fighter.

This project involves a lot of web scraping ([link to ufc-webscraping-blog]), working with a lot of big data (and I mean ALOT) as well as constant regular future updates to account for the approximately 47 UFC events per year.

The beginning

If you want to read about were and how I got my data from, I have another blog [here]. This blog your currently reading will cover the designing of my database tables. Due to my past, now slightly (very) rusty PostgreSQL experience, I chose to use it for this project.

Tables

At the minimum I needed to split my data into 3 tables:

  • Events; contain name, date, location and PPV
  • Fight_stats; contain fight_id, winner, event id, fight length and win method
  • Fight_matchups; contains fighter1_id, fighter2_id and fight_id (points to fight id in fight_stats table)
  • Fighters; contain name, record, weightclass, age and current rank / retired *

*this database will only contain basic historical fight information, in the future implementing detailed fight data i.e stikes landed, knockdowns, takedowns will be a great addition but for now the goal is just to populate the database with accurate yet not abundant data.

Events Table

The events table is the first to populate, as explained in my scraping blog. The initial table looks like this:

Events Event_id int primary key Event_title varchar Event_date datetime isPPV bool Location varchar Arena varchar Lat?? Long??

  • began by creating an events db, with all historical UFC events

data to be included in this db:

  • event name
  • event date
  • event location

***seperate dev database idenctical to the prod database with an id column? from ufcstats.com

EVENTS table: event_id int event_title string event_date datetime event_location string event_arena string ??

FIGHTS table: fighter1_id int fighter2_id int winner (fighter_id) is_title bool is_title bool is_main_event bool ending_round int ending_time int method string division string