Mysql – More Fields or multiple tables

database-designMySQLschema

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.

Best Answer

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

Related Question