0

I run multiple pool leagues and have been keeping the information in excel. I'm working on moving this to the web and using MySQL for the data. The largest amount of data is in the matches table, with information about every match played.

I have multiple leagues based on different games, straight pool, one pocket, 9 Ball & 8 Ball. There are a few fields specific to each game, like high run in straight pool but no others, break & run in 8 Ball and 9 Ball but not the other two, 8 on the break in 8 Ball but no others, etc.

I could put all of these fields in one table and have a field called 'game' which flags which game this record is about. Or, I could have a table for each game, with all and only the fields needed for that game.

The single table, if I go that route, will be small to start, but if I'm able to expand the number of places where I have these leagues the number of records could grow to 20,000 or more in a couple years.

Which way would be best?

P.S. I do not envision running queries that look at records of more than one game in the same query.

  • 1
    20K rows is a "small" table. One table per game is bad; don't do it. – Rick James Apr 23 '17 at 19:10
0

If your queries know beforehand which type of game you're refering to, and you are only having one game per query, the cleanest way is most probably to have different surrogate-tables for different games, and always JOIN the main table with the one specific for your type of game.

For instance, you would have a table for all the different types of games:

 CREATE TABLE games
 (
      game_id integer PRIMARY KEY,
      game_name varchar(100) NOT NULL
 ) ;

 INSERT INTO games 
     (game_id, game_name)
 VALUES 
    (1, 'straight pool'),
    (2, 'one pocket'),
    (3, '9 Ball'),
    (4, '8 Ball') ;

A table for players (and probably for leagues, seaons, etc.)

 CREATE TABLE players
 (
     player_id integer PRIMARY KEY, -- probably autoincrement
     player_name varchar(100)
 ) ;

 INSERT INTO players
    (player_id, player_name)
 VALUES
    (1, 'Player 1'),
    (2, 'Player 2'),
    (3, 'Player 3'),
    (4, 'Player 4') ;

Your matches table:

 CREATE TABLE matches
 (
      match_id integer PRIMARY KEY, -- probably autoincrement

      game_id integer NOT NULL REFERENCES games (game_id),
      player_1_id integer NOT NULL REFERENCES players (player_id),
      player_2_id integer NOT NULL REFERENCES players (player_id),
      match_date date NOT NULL,
      -- probably a league_id
      -- probably a season_id 
      -- some other data common to all matches

      UNIQUE (game_id, player_1_id, player_2_id, match_date)  -- Alternate key
 ) ;

And then, for each specific game type, a table with all the columns that makes sense:

 CREATE TABLE straight_pool_matches
 (
    match_id integer REFERENCES matches(match_id),
    -- Don't know enough to know what to put here
    info_about_straight_pool_matches varchar(255)
 ) ;

So, let's image there is a match of "straight pool" on 1st Jan 2017, between players 1 and 2. You would add data to two tables:

 INSERT INTO matches
     (match_id, game_id, player_1_id, player_2_id, match_date)
 VALUES
     (1, 1, 1, 2, '2017-01-01') ;

 INSERT INTO straight_pool_matches
     (match_id, info_about_straight_pool_matches)
 VALUES
     (1, 'Something that makes sense about a straing pool match') ;

And the way you would perform your queries would most probably be:

 SELECT 
     m.match_id, p1.player_name AS player_1, p2.player_name AS player_2, m.match_date,
     s.info_about_straight_pool_matches
 FROM 
     matches m
     JOIN players p1 ON p1.player_id = m.player_1_id
     JOIN players p2 ON p2.player_id = m.player_2_id
     JOIN straight_pool_matches s ON s.match_id = m.match_id 
 WHERE
     m.game_id = 1 
 ORDER BY
     m.match_date, m.match_id ;

 match_id | player_1 | player_2 | match_date | info_about_straight_pool_matches
-------: | :------- | :------- | :--------- | :---------------------------------------------------- 1 | Player 1 | Player 2 | 2017-01-01 | Something that makes sense about a straing pool match

dbfiddle here

  • Sorry about the delay, stuff happens. This is quite detailed and you've done a lot of work, thanks. I just don't understand the benefit of breaking the matches table up into 2 pieces, then joining them back together. Separating out the games into its own table I get, it's the splitting the remainder into matches and straight_pool_matches, 9_ball_matches, etc. There's no savings in space, just added complexity and possibly performance, though it might be very little. I'm probably missing something, I hope you can explain it to me. – John Biddle Apr 25 '17 at 23:18
  • This is a common pattern when you need to have "specialized versions" (in OOP parlance this would be subclasses) of a common class. In PostgreSQL, it could be done also via inheritance, although the side effects make it not recommendable. An alternative is to have all columns of all possible type of matches in the matches table. This makes sense if there're little. If you want to scale, you can't do it this way (with 10 types of games x 10 specific columns each, you end up having 100 columns, and not making any sense of your data). – joanolo Apr 26 '17 at 6:42
  • Don't be afraid of having several smaller tables (with few or no NULL columns). Databases are good at handling these kind of things; and you (or whoevever needs to maintain the system) will be much better off at understanding (making sense of) what's inside. – joanolo Apr 26 '17 at 6:44
  • So, to answer your original question "more fields or multiple tables", If you want to scale, go for "multiple tables". – joanolo Apr 26 '17 at 6:45

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.