Hopefully the purpose of these tables will be apparent, but just in case here's a simple explanation: I basically wish to store the results of matches (8 ball pool etc.) but also log the results on a frame by frame basis. Where I am getting a problem is that I intend to allow for different game types as my application grows, and as such, the scoring system used within each frame would vary. I think the solution below could work, but it doesn't quite seem right to me, all advice is appreciated.

Is there a more sensible approach than using the following tables:

# Players

• id
• name
• nickname

# NormalMatches

• id
• playerA
• playerB

# NormalFrames

• id
• match_id
• playerAWon (BOOL)

# ScoringPoolMatches

• id
• playerA
• playerB

# ScoringPoolFrames

• id
• match_id
• playerA_cue_ball_potted
• playerB_cue_ball_potted
• playerA_balls_p开发者_StackOverflowotted
• playerB_balls_potted
• playerA_balls_remaining
• playerB_balls_remaining

(edit: altered ScoringPoolFrame table to give better idea of what the problem is.)

Many thanks.

---

i'm not sure what the different is between your normal matches table and your scoring pool matches.... i would have things set up a little differently, with one table to score final win/loss results for a match, and one table to score all games/frames. (* indicates a primary key field)

Players

• id *
• name
• nickname

Matches

• id *
• playerA
• playerB
• winning_player_id

Games

• match_id *
• game_id *
• player_id
• score

the game_id would be a frame number in bowling, or a set number in tennis, or an inning in baseball

you might even want to flatten the matches table into something like this:

Matches

• match_id *
• player_id *
• winner (bool)

so that you can select all matches for a certain player without searching on two columns. you could also have more than two players in a game that way.

---

Guiding principle: Less is more. In this case, less tables = good.

A match is a match and a frame is a frame, so...

• Merge "NormalMatches" and "ScoringPoolMatches" tables together and introduce a column is_scoring boolean to differentiate
• Merge the "NormalFrames" and "ScoringPoolFrames" tables into simply "Frames" (id, playerA_score, playerB_score), but simply allocate a score of "1" for a win and "0" for a loss for normal games. Then your calculation of win/lose is the same for all frames.

Create view(s) to capture the logic of calculations, eg

CREATE VIEW FRAME_RESULT AS
SELECT
    id,
    playerA_score > playerB_score as playerAWon,
    playerB_score > playerA_score as playerBWon,
    playerA_score = playerB_score as is_draw
FROM Frames