I'm going to need a lot of help, but believe this should be simple

[PGFstats]

So, I'm basically brand new to database design. I have quite a bit of experience using them, and even mining in SQL, but zero experience in actual design.

I've been tasked with developing a database for a jiu-jitsu league. The rule set in the league has some cool rules, but makes it a challenge for a newbie like me to set up the database.

So, fighters faces off in a whole season, rather than a tournament style bracket. The goal for them is to win enough points to climb the leader board. The top few fighters at the end of the regular season move on to a playoff, and final match. The playoffs are tournament style. 

The way they earn points is only by submitting your opponent. If you submit them with a choke, you get 6 points for the match. If you win with a joint lock, you get 3 points. If time runs out, and it's a draw, neither fighter scores any points (0 points). You can also earn bonus points. If you win your match in less than 1 minute, you get a bonus point. 

Each fighter is a member of a team. At the end of a round of fights, where everyone has fought once. The team with the most points, earns an additional 1 point for each team member.

So, the first thing that I need to do is enter the fighter data. This is pretty simple. (See Fighter_Bio Table attached)

Then I need to assign each fighter to a team. Also seems pretty simple

 

Then, I need to record the fight results. This also seems pretty easy. (See Match_Results Table attached)

 

Ok, so if you see anything above, that I should do differently, please, by all means, point it out to me. 

So, with the above tables, I need them to fill in the data for the next table. This is the Fighter_Record table. In this table, I need to capture their entire career, and be able to parse that data in a datasheet.

This is what I have so far for the Fighter_Record Table. I honestly have no clue how to build this one, Or how to use the relational data chart thingy, to feed it the information that I need.



So, obviously I'll need to be able to use the Count function in a formula to incrementally count Wins, Loses, Draws, Kills (these are chokes), Breaks (joint locks), and the various bonuses. 

Once counted, I'll need to multiply those results by the value of each thing. 

Then add those results together to get the total points. 

Then I'll need a datasheet to be able to show the fighter's career stats, by Season. 

So, I'm guessing I need a lot of guidance on relationships, and formulas. 

Any help anyone can provide me, would be SO appreciated!

 

[Steve1223]

I am new to the forum, but lots of experience in database design.  You are off to a good start.  Look at the nouns that you used in your description (e.g. Fighter, Team, Season, Match, Round). 

Be consistent in terminology - are fights and matches the same?  

I would rename your Fighter Bio table to Fighter.  Yes the table has biographical information.  But the columns describe the fighter, not his biography.  Age changes from season to season.  I would suggest using date of birth instead.  Hometown could be split into separate columns for City and State.

You need a Season table with a primary key of Season_ID.  Attribute columns could be start and end dates of the season name.  Can you have multiple seasons in the same calendar year?  I think it is possible.  Can a season start in one year and end in another?  I think that is possible as well.  All depends on your needs.

You need a Team table.   One of the columns (attributes) will be a Team Name.  Can a team name for team number change by season?  If no, then a unique primary key of Team_ID can be used.  Otherwise it gets more complicated and you would have both a Season_Id and Team_Id forming the unique key.

Primary keys are unique.  A Random ID (text 255) or Autonumber (Integer) are good choices.  Which to use is personal preferance.  

Team Roster table needs some work.  I think this table is a many-to-many relationship between 3 tables of Team, Season, and Fighter.  None of these columns would be Random ID because they relate back to their respective primary tables.  The combination of these columns is unique.  The Text (255) columns for Season, Team, and Fighter Name.

You use the term Round but do not have a corresponding table.  A possible column would be the date of the match.  I think a season can have multiple rounds.  A given round can have multiple matches.  A match within a round can have two fighters as you are showing in your Match Results table.  I think your match results needs a Round Id to be part of the unique key.  The Match Results table should have a column for the points Fighter 1 earned and another column for the points of Fighter 2.  You could have a column identifying the winning fighter and another column for how the fight was one (choke, joint lock).  You could have a column to capture the outcome (e.g. Win/Loss or Draw).

The changes to the Match Results table may eliminate the need for your Fighter_Record table.  I think this would be aggregating the data from the Match Results.

So think of the data relationships.  One-to-one, one-to-many, or many to many.  This helps determine your tables with a single unique key and those tables that are related to others.

A season can have many teams.  A team can be part of many seasons. 

A team for a season will have a certain set of fighters (roster). 

A season can have many rounds.  A round can have many matches.  A match has an outcome of Win/Loss or Draw.  A match can have 2 fighters.  Each fighter in the match earns points.  Each fighter in the match belongs to a team for that season.

I hope this helps.

 

[Flowers4Algernon]

Hello! Just wanted to share this How-To article regarding relationships: https://howto.caspio.com/tables-and-views/relationships/database-relationships/

[PGFstats]

On 12/5/2021 at 9:59 AM, Steve1223 said:

I am new to the forum, but lots of experience in database design.  You are off to a good start.  Look at the nouns that you used in your description (e.g. Fighter, Team, Season, Match, Round). 

Be consistent in terminology - are fights and matches the same?  

I would rename your Fighter Bio table to Fighter.  Yes the table has biographical information.  But the columns describe the fighter, not his biography.  Age changes from season to season.  I would suggest using date of birth instead.  Hometown could be split into separate columns for City and State.

You need a Season table with a primary key of Season_ID.  Attribute columns could be start and end dates of the season name.  Can you have multiple seasons in the same calendar year?  I think it is possible.  Can a season start in one year and end in another?  I think that is possible as well.  All depends on your needs.

You need a Team table.   One of the columns (attributes) will be a Team Name.  Can a team name for team number change by season?  If no, then a unique primary key of Team_ID can be used.  Otherwise it gets more complicated and you would have both a Season_Id and Team_Id forming the unique key.

Primary keys are unique.  A Random ID (text 255) or Autonumber (Integer) are good choices.  Which to use is personal preferance.  

Team Roster table needs some work.  I think this table is a many-to-many relationship between 3 tables of Team, Season, and Fighter.  None of these columns would be Random ID because they relate back to their respective primary tables.  The combination of these columns is unique.  The Text (255) columns for Season, Team, and Fighter Name.

You use the term Round but do not have a corresponding table.  A possible column would be the date of the match.  I think a season can have multiple rounds.  A given round can have multiple matches.  A match within a round can have two fighters as you are showing in your Match Results table.  I think your match results needs a Round Id to be part of the unique key.  The Match Results table should have a column for the points Fighter 1 earned and another column for the points of Fighter 2.  You could have a column identifying the winning fighter and another column for how the fight was one (choke, joint lock).  You could have a column to capture the outcome (e.g. Win/Loss or Draw).

The changes to the Match Results table may eliminate the need for your Fighter_Record table.  I think this would be aggregating the data from the Match Results.

So think of the data relationships.  One-to-one, one-to-many, or many to many.  This helps determine your tables with a single unique key and those tables that are related to others.

A season can have many teams.  A team can be part of many seasons. 

A team for a season will have a certain set of fighters (roster). 

A season can have many rounds.  A round can have many matches.  A match has an outcome of Win/Loss or Draw.  A match can have 2 fighters.  Each fighter in the match earns points.  Each fighter in the match belongs to a team for that season.

I hope this helps.

 

Steve, this is a TON of help! Thank you very much for taking the time to consider all of this for me!

I love your correction of the Fighter_Bio table. I'm absolutely going to make that change. I also like your suggestion of using the date of birth. We do have fighters return from season to season, so that makes perfect sense. Thank you!

The Season Table will be tricky. Locking down start and end dates are a little tricky right now. We film it all in one week, and then release an episode a week for however many weeks it takes. (It's been different each season). Yeah, it's entirely possible that we could have more than one season in a year. And the Season could start in one year and end in the next. That's foreseeable. But, right now, all we have as far as attributes is Season 1, 2, 3 (we're staring 3 in January)

I really do need a Team table. We don't have traditional team names. Like, there are no mascots or anything. Just basic stuff like, Red Team, Blue Team, Yellow Team, etc. While a color could be used the next season to denote a team, the players are entirely different on the Red Team each season. 

So, where I used the term "Round" I should have stated "Block". For this sport, a Block is a series of matches, where every fighter, gets a match. Each fighter gets a new opponent each block. So, we could have 12 matches in every Block, but each fighter, only fights once per block. The season could have 20 blocks. The amount of matches per block, and the amount of blocks per season, vary each Season. I hope that helps clarify that, although it looks like you pretty much figured it out. 

So, if I understand this correctly, a Primary Key is denoted by the unique identifier, be it a Random ID or an Autonumber. 
When establishing relationships to other tables, I connect the Primary Key, to the attribute that I want the data from the PK to fill. For example, If I want the Fighter's name to populate in a results table. I connect the PK from the Fighter Table, to the Name attribute in the Results table? Does the PK become a FK? I guess I struggle with exactly how Caspio passes data using the Relationships tool.

Anyways, you've been a ton of help, and I am truly grateful for you giving me some pointers! I'll give them a shot and then give you some results, and see how it goes!