1 table or multiple tables

[brown2005]

say for instance i have

 

sports_players 1345353 entries

 

football_players 334255 entries

rugby_players 145345 entries

tennis_players 345243 entires

and so on....

 

obviously i know the separate entries in each red bits would be quicker to read but if i wanted a website which can read the same as sports players, is this possible or would putting them all in the same table be the best way?

[aschk]

If you wish to normalise your data and make it easier to work with from a queries point of view then I suggest you have a "players" table and a "sports" table, where you link the sport to the player. This way you don't have to alter you schema to have a new sport added, you just insert a new row into the "sports" table.

e.g.

CREATE TABLE sports (
sports_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sports_name VARCHAR(64) NOT NULL
) Engine = InnoDB;

CREATE TABLE players (
player_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
player_name VARCHAR(64) NOT NULL,
sports_id INT UNSIGNED,
FOREIGN KEY fk_sports_id (sports_id) REFERENCES sports(sports_id) ON UPDATE CASCADE ON DELETE SET NULL
) Engine=InnoDB;

 

Unfortunately you're incorrect in your assumption that reading would be quicker from the separate tables. Firstly there will be a code overhead to decide which table you're supposed to be working with, and Secondly because the tables are indexed the seek time will be identical.

 

To find all players that play "rugby" you will execute the following statement:

SELECT p.player_name 
FROM players p 
JOIN sports s ON p.sports_id = s.sports_id
WHERE s.sports_name = 'rugby';

 

Easy as pie, and nice and fast to boot.

 

If you want the sport that a particular player plays then you can do the following:

SELECT p.players_name
      ,s.sports_name
FROM players p
JOIN sports s ON p.sports_id = s.sports_id
WHERE p.player_name = 'Henrik Larson'

[brown2005]

sorry what i meant was wouldnt it be quicker looking, say if i was on website

 

www.myfootballwebsite.com

 

then i would only be coding to look at the football tables anyway wouldnt that be faster than using one table for all sports and looking up just the football records in that table...

 

but i will go the way of using like you above, which is what i was thinking of doing, if using the one table, is fast enough.

[aschk]

In answer to your question: "no" , using one table per sport is not going to be any quicker realistically with indexing, and more to the point it makes maintenance appalling.

 

As I mentioned in my previous post, inserting a new sport is going to require building a whole new table, whereas using the structure I supplied you merely have to insert 1 record into the sports table.

 

It will probably also make cross-sports statistical analysis easier too.