0

For a personal project I need to design for the first time in my life a (star schema to keep it simple) relational database, the context is the sport of boxing.

Specifically, I need help designing a fact table that is measuring in a fight how many jabs a fighter threw, and how many landed. I need to measure this by fighter, and I need to measure this by round and by fight (think of "rounds" like innings in baseball for those unfamiliar). This fact table is populated for all fights in the history of boxing and updated after any fight. Here is my solution so far:

Fight_Table_Fact:

| fight_date_key_PK_FK | fighter_key_PK_FK | fight_key_PK_FK | round | jabs_attempted | jabs_landed |

As I understand it - there is a rule that the primary key of a fact table is the combination of all the foreign keys it has. However my solution would only satisfy this if I made "round" a FK. Furthermore, if round is not a PK then the combination of the other PK does NOT uniquely identify a row. However creating a "round" dimension table doesn't make sense to me at the moment, I'm not sure what I would meaningfully put in it. My suspicion is I simply don't have a choice but I don't know how to construct the round_dim table.

Thanks in advance! Any input appreciated :)

2
  • 1
    Looks like your chosen fact granularity of "fight" might be wrong.
    – mustaccio
    Apr 20 at 14:10
  • so you think it should be broken down to round? no other way around it? Just a fight granularity seems more intuitive ... Apr 20 at 15:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.