data-base struture and development

jlsports

I am developing a site that generates reports based on a data-base of football games. The data-base is right now about 1900 records, with each record representing one game. I am getting the desired results but I have a feeling that I have a very inefficent data-base design and that the pages will take a really long time to load and build once there are multiable accesses to it at the same time. It currently takes 3-5 seconds to load, but that is with only one user at a time.

A sample page can be found here

http://www.sportsbettingwire.com/cfb/gamematchups.php?week=17

I am querying the data-base for each game shown, and some weeks there are 50+ games and the data-base is 1900 records and growing.

I am probably not making myself clear but maybe somebody has an idea how to struckture this data-base properly. Here is a link to my data-base structure.

http://www.sportsbettingwire.com/db.html

Any ideas or suggestions would really help

azrath

You'll want to do some reading about database normalization. The idea is to split a large table into smaller, linked tables. It vastly improves performance.

For example, you have a bunch of homestat and awaystat columns that are better made into rows of homestat and awaystat tables. These tables are linked to your gamedata table through the gameid foreign key (a reference to linked data in another table).

This phpBuilder article http://www.phpbuilder.com/columns/barry20000731.php3 should get you started.

Best of Luck,
P.

jlsports

azrath,

Thanks for the help and I have been reading about normaliztion. How would you setup the homestat and awaystat fields? Thanks

azrath

I'd probably have something along the lines of:

CREATE TABLE gamedata (
  gameid int(11) NOT NULL default '0',
  date date NOT NULL default '0000-00-00',
  # other columns snipped
  PRIMARY KEY  (gameid),
) TYPE=MyISAM;

CREATE TABLE homestats (
  homestatidid int(11) NOT NULL,
  gameid int(11) NOT NULL default '0',
  stat DECIMAL(3, 1) NOT NULL default '0.0', 
  PRIMARY KEY  (homestatid),
) TYPE=MyISAM;

CREATE TABLE awaystats (
  awaystatid int(11) NOT NULL,
  gameid int(11) NOT NULL default '0',
  stat DECIMAL(3, 1) NOT NULL default '0.0', 
  PRIMARY KEY  (awaystatid),
) TYPE=MyISAM;

You wouldn't have any homestat or awaystat columns in gamedata. For each stat, you'd have a row in homestat or awaystat with gameid set to the game in question.

Regards,
P.

jlsports

So for each STAT, which there are 25 for each the home and the away I would have a data-base row? There are nearly 2000 records so that would be 50,000 rows, RIGHT?

This type of setup would make things more efficent? I realize I am probably missing something here, but if you or somebody could explain that would be cool.

Thanks so much for your help and time?

azrath

Well, think of it this way: If you are trying to get the stats for one game, or say games between two dates, would it be easier to search through a small table that contains only the game and date, or the monolithic table that you have?

In the first case, the DB can choose the appropriate games, then combine them with the associated stats records. In order to discard a row during search, the DB has to 'look at' a lot less data than if everything is in one table. A lot fewer records of the monolithic table can live in memory compared to the normalized one. As a result you have to go to disk more often with the monolithic table and hence get a slowdown.

Breaking things out into a more normalized structure with appropriate indicies means the DB system has a much clearer 'road map' of where the records lie and can thus retrieve them quicker than if it has to search through the same glob of data repeatedly.

Regards,
Patrick