Best table design for record of game played

D

David McKnight

I have table that I copy and paste append from the internet the fields are:

Date played/Season/Home/home score/visitor/visitor score/margin/nuetral
field/location

My question is "is this a reasonable good normalization" it maybe
complicated because data is suppied in this format and is worth reconfiguring
into mulitiply tables? I have some pretty complex(time consuming) queries
that rely on this data - one using 2000 loops through two queries that takes
24 hours to complete on a dual core processor. Suggestions?
 
J

John W. Vinson

I have table that I copy and paste append from the internet the fields are:

Date played/Season/Home/home score/visitor/visitor score/margin/nuetral
field/location

My question is "is this a reasonable good normalization" it maybe
complicated because data is suppied in this format and is worth reconfiguring
into mulitiply tables? I have some pretty complex(time consuming) queries
that rely on this data - one using 2000 loops through two queries that takes
24 hours to complete on a dual core processor. Suggestions?
Click to expand...

This table contains a lot of repeating data: surely each team plays many
games, and you should at the least have a table of teams; I suspect you'll
benefit from having a table of locations as well. You would be able to run
Append queries to populate a normalized scores table from this downloaded
data, joined (by team name) to the teams table; the margin, if it's the
difference between scores, should not be stored at all but instead calculated
on demand.

Not knowing anything about your queries all I can say is that it should be
possible to vastly improve their performance with proper indexing and query
design, unless you have tens of millions of scores to process each time. It
would be very unusual to have to "loop" queries at all, much less loop them
2000 times!

John W. Vinson [MVP]
 
D

David McKnight

Thanks for the suggestions I will try to redesign table based on these.

As far as Query Loops I wish there was a better way to find same answer -
but this works best (data makes most sense) compare to other I've tried.
There is probably thousands of approaches you can find on the web. The Loops
come from comparing the average margin of team A vs all the competition they
have played and how they did against other opp's and on and on and on. My
method is fairly simply in concept. Say team A has a value (adjusted
Margin)of 7 after loop one (each team starts at zero) they win and by average
10 points against teams that average a value of 6. Team A "average
Performance" is thus 13. Each of it's opp is also recalc'd the same way to
have a new "average performance" so these vales go into loop two. Each
team's value does not stop changing until after about 2000 loops. If there is
a query method that can do this without loops let me know!!!
 
J

James A. Fortune

David said:
Thanks for the suggestions I will try to redesign table based on these.

As far as Query Loops I wish there was a better way to find same answer -
but this works best (data makes most sense) compare to other I've tried.
There is probably thousands of approaches you can find on the web. The Loops
come from comparing the average margin of team A vs all the competition they
have played and how they did against other opp's and on and on and on. My
method is fairly simply in concept. Say team A has a value (adjusted
Margin)of 7 after loop one (each team starts at zero) they win and by average
10 points against teams that average a value of 6. Team A "average
Performance" is thus 13. Each of it's opp is also recalc'd the same way to
have a new "average performance" so these vales go into loop two. Each
team's value does not stop changing until after about 2000 loops. If there is
a query method that can do this without loops let me know!!!
Click to expand...

From the brief description of your calculation I am reminded of
calculations in a matrix class involving the vector you get in the limit
when you keep premultiplying the vector by a matrix. Typically, instead
of continuing to premultiply a matrix until the values stop changing, it
is possible to do it in one fell swoop by calculating the eigenvector
associated with the matrix. See:

http://mathworld.wolfram.com/Eigenvalue.html

James A. Fortune
[email protected]
 
D

David McKnight

WOW! I think I see how this might be related to what I'm trying to do but it
is allot to digest and then translate this to what I want to do. I had
thought there was probably a mathematical theory on something like this - my
trepidation is that the movement of the "team performance" rating value going
up or down with each loop appears random (at least early on). Not sure if
your link works with "non linear" process. Food for thought - thanks again
for the insight.
 
Top