update squash league

i am creating a league database for my club squash leagues. the leagues go
from premier 1-10, intermediate 1-10 and club-1-3. so far i have tables for
membership information, league type and level, and matches, all linked by
their primary keys to the secondary keys. The main idea of the database is to
update the league positions and matches at the end of the league month (the
leagues change every month). I have no idea how to do this-(do i need other
tables/what queries do i need?). the tables fields are as follows:

membership info: ID, name, address, phone, email, league id.

League: ID, title, level.

matches: ID, league id, member1 name, member 2 name, member 1 score, member
2 score.

the member table is linked to league by league id, and matches is linked to
league in the same way.

at the end of the month, players are ranked in their leagues by the amount
of games won (games are first to 3). they are ranked by the amount of 3's
then 2's then 1's. according to the rankings, the 1st player moves up 2
leagues, the second moves 1, the 3rd stays, the 4th goes down 1 and the 5th
goes down 2.

Please help!!!

I don't really want to give away the model I'm using, but what I've
done for www.leaguesapart.com is normalize the tables more than you
have. Also we're using PostgreSQL - but you should be able to do
similar things access.

(There are a couple of examples available from the LeaguesApart home
page and you can also create squash and other leagues and play around
with it yourself - it's free to use.)

I suggest you keep your league member details in a different table to
the matches, and the scores in another table again. Why? Because each
member will play in more than one match and a match has several
'scores', depending on what you want to record.

eg do you want to record the score of each game in a match? And (more
importantly) do you want to be able to *do* anything with those scores?

Let's say matches are best of three and player A beats player B 9,6 6,9
9,6. If you hold the scores in the match record then you need 6 columns
for them. Two more if you also hold the calculated score (logically you
wouldn't do that, but there could be good reasons for doing it in the
implementation).

If you want to work out who won, you have to compare the figures in
pairs. If you want to include the total number of points won/lost on
the table then then it all gets very complicated if you keep all the
scores for a match together in the same row as you're doing. (It's
complicated enough if you separate them out!)

Basically what I do is this (this is a simplified version of the story,
but LeaguesApart needs to cater for more situations than you have):

Members table - Each member of a league. If the same person plays in
more than one league (at the same time or from month to month) then
consider separating out further.

Matches: ID, league id, any details to do with the match (date, time,
etc).
My model allows for more than two members playing each other (eg
doubles) so I have another table for the match participants, rather
than putting them here. Some things further down the line are easier if
you do this even if there are ever only two players.

Match participants: keys into leagues and matches, its own id, member
id, home or away. (Actually this simplification doesn't explain how I
handle doubles but, as I said, I'm not going to describe the whole
model!) I keep scores here for football (say) but not squash.

Scores: Key into match participants (and maybe more for implementation
reasons), game number, score - one row per game /match / member
combination.

It is complicated initially to go down this route but if you don't
you'll find some reports you want to generate (and a league table is
simply a nicely formatted report) become almost impossibly complicated
to do, especially if you decide to add a column to the table or
something that you hadn't thought about in the beginning.

Something else I do for performance and to reduce the load on the
machine is to have some halfway-house database tables so league tables
don't have to be generated from the raw data every time they are looked
at. If you do that you have to be careful that the tables are kept in
line with the raw data whatever happens to it. That was quite messy to
sort out but it makes a big difference on LeaguesApart.

Or maybe you could just use LeaguesApart?? At the moment it doesn't do
all you'd want to do. But if it's close to being useful to you let me
know what you'd need (there's a contact page on the site) and I'll see
what I can do - if you want it others probably do as well. Also you can
use it to create tables which you can then embed in your own site.
They'll automatically stay up to date (on your own site as well as
Leagues Apart) when you enter new scores. There's a 'Questions and
answers' section that tells you how to do that on the site.

Some of LeaguesApart isn't very pretty at the moment. That's because my
background is in databases and to me data integrity is the most
important, then performance, and only after those come the aesthetics
and functionality. But I know that if it doesn't look good on the
surface it gives a poor impression of what's underneath, so I'm
starting to work more on that aspect now.

Hope that helps in some way,
Nick. (www.leaguesapart.com)

" wrote:

I don't really want to give away the model I'm using, but what I've
done for www.leaguesapart.com is normalize the tables more than you
have. Also we're using PostgreSQL - but you should be able to do
similar things access.

(There are a couple of examples available from the LeaguesApart home
page and you can also create squash and other leagues and play around
with it yourself - it's free to use.)

I suggest you keep your league member details in a different table to
the matches, and the scores in another table again. Why? Because each
member will play in more than one match and a match has several
'scores', depending on what you want to record.

eg do you want to record the score of each game in a match? And (more
importantly) do you want to be able to *do* anything with those scores?

Let's say matches are best of three and player A beats player B 9,6 6,9
9,6. If you hold the scores in the match record then you need 6 columns
for them. Two more if you also hold the calculated score (logically you
wouldn't do that, but there could be good reasons for doing it in the
implementation).

If you want to work out who won, you have to compare the figures in
pairs. If you want to include the total number of points won/lost on
the table then then it all gets very complicated if you keep all the
scores for a match together in the same row as you're doing. (It's
complicated enough if you separate them out!)

Basically what I do is this (this is a simplified version of the story,
but LeaguesApart needs to cater for more situations than you have):

Members table - Each member of a league. If the same person plays in
more than one league (at the same time or from month to month) then
consider separating out further.

Matches: ID, league id, any details to do with the match (date, time,
etc).
My model allows for more than two members playing each other (eg
doubles) so I have another table for the match participants, rather
than putting them here. Some things further down the line are easier if
you do this even if there are ever only two players.

Match participants: keys into leagues and matches, its own id, member
id, home or away. (Actually this simplification doesn't explain how I
handle doubles but, as I said, I'm not going to describe the whole
model!) I keep scores here for football (say) but not squash.

Scores: Key into match participants (and maybe more for implementation
reasons), game number, score - one row per game /match / member
combination.

It is complicated initially to go down this route but if you don't
you'll find some reports you want to generate (and a league table is
simply a nicely formatted report) become almost impossibly complicated
to do, especially if you decide to add a column to the table or
something that you hadn't thought about in the beginning.

Something else I do for performance and to reduce the load on the
machine is to have some halfway-house database tables so league tables
don't have to be generated from the raw data every time they are looked
at. If you do that you have to be careful that the tables are kept in
line with the raw data whatever happens to it. That was quite messy to
sort out but it makes a big difference on LeaguesApart.

Or maybe you could just use LeaguesApart?? At the moment it doesn't do
all you'd want to do. But if it's close to being useful to you let me
know what you'd need (there's a contact page on the site) and I'll see
what I can do - if you want it others probably do as well. Also you can
use it to create tables which you can then embed in your own site.
They'll automatically stay up to date (on your own site as well as
Leagues Apart) when you enter new scores. There's a 'Questions and
answers' section that tells you how to do that on the site.

Some of LeaguesApart isn't very pretty at the moment. That's because my
background is in databases and to me data integrity is the most
important, then performance, and only after those come the aesthetics
and functionality. But I know that if it doesn't look good on the
surface it gives a poor impression of what's underneath, so I'm
starting to work more on that aspect now.

Hope that helps in some way,
Nick. (www.leaguesapart.com)



Thanks for the quick reply. I am only a beginner at these databases and this
is for my a levels so unfortunately i can't use the leagues at your website!!
Is there any way you could show me how the tables you described above appear
in access and how they are linked together- sorry but im not too good at
this!!

It's ages since I used Access - did some early prototyping with it. But
I never really got into it (my background is Oracle).

If you're doing this as an academic (but working as far as it goes)
exercise the tables the way you are doing them should be OK, but that
still takes you back to your original question. You need someone
familiar with Access for that.

I use generated unique ids on most of the tables but make the primary
key a combination of the league id and that unique id (and maybe other
columns as well, but mostly not). That means I can separate out
recordes in most tables by league without having to join a load of them
together to get back to the leagues table. (For course work though you
should probably give them something that's properly normalised, but
I've no idea whether they even cover that, of course.)

But I don't know in Access even how to have multi-column primary keys
and don't remember anything about the Access procedural stuff, which
you'll need for at least for the moving up and down functionality, so
you need someone else for that.

Good luck with it,
Nick.
www.leaguesapart.com - Your own free online sports and games tables