Designing a database - I need some advice

[MikeB]

Perhaps some of the more experienced members here can advise me on my
thinking about my database design.

I manage a school chess club. Every year the kids sign up. Each year
we gaterh some information - how they go home after chess, who their
teacher is, parents' contact info, whether the parent can volunteer,
whether the kid may play in chess tournaments, whether the parent can
transport the kid(s) to tournaments, etc., etc.

The questions, and of course, the answers are not the same each year.
So I'm thinking of splitting my Players database into two or more
tables. Currently I'm thinking of a main Players table that contains
the "static" information about each player - their name, rating,
level, etc. and then a (for instance) Player2008 table that contains
the variable data for school year 2008. THen next year I can create a
Player2009 table for the variable data for that year. On the Player
form, I can then build a tabbed control and label each tab for a
school year and display the relevant data for that year.

Is this how others would do this? It still seems cumbersome, but I
can't think of an alternative.

Thank you

 

[Dirk Goldgar]

Perhaps some of the more experienced members here can advise me on my
thinking about my database design.

I manage a school chess club. Every year the kids sign up. Each year
we gaterh some information - how they go home after chess, who their
teacher is, parents' contact info, whether the parent can volunteer,
whether the kid may play in chess tournaments, whether the parent can
transport the kid(s) to tournaments, etc., etc.

The questions, and of course, the answers are not the same each year.
So I'm thinking of splitting my Players database into two or more
tables. Currently I'm thinking of a main Players table that contains
the "static" information about each player - their name, rating,
level, etc. and then a (for instance) Player2008 table that contains
the variable data for school year 2008. THen next year I can create a
Player2009 table for the variable data for that year. On the Player
form, I can then build a tabbed control and label each tab for a
school year and display the relevant data for that year.

Is this how others would do this? It still seems cumbersome, but I
can't think of an alternative.

How often do you need to know both current-year and past-year data at the
same time? If you don't really need to know that, a simple Players table
that you update annually ought to be good enough. If you do need both
current-year and past-year information, then it makes sense to have a
"PlayerYearData" table of some sort, but I would have the SchoolYear as an
indexed field in the table, rather than have a separate table for each year.
Then any display of the PlayerYearData would be filtered, one way or
another, to show the year you're interested in.

If you want to have a form that displays, say, the current year and n
previous years on separate tabs, you could use the same subform on multiple
tabs, but use the desired year (along with the PlayerID) in the link master
field for each separate subform control. This could all be done
automatically by having hidden controls on your main form to calculate the
current school year, school year minus 1, school year minus 2, etc.

 

[Al Campagna]

MikeB,
Actually the items you mentioned...

how they go home after chess, who their
teacher is, parents' contact info, whether the parent can volunteer,
whether the kid may play in chess tournaments, whether the parent can
transport the kid(s) to tournaments, etc., etc.

are "static" also. You would not need to know that a player took the #17
bus home 5 years ago... just what the current method is.

So, those items should/could be stored in the same table as the basic
player info (the ONE side of a realtionship)

There's no law that says you can't break those "ancillary" items out
into a separate table (with a One to One realationship to the PlayerID), but
I wouldn't think it necessary.

Now... that's not to say that you couldn't "display/edit" those
ancillary items on a subform of it's own... to keep your main form from
getting over crowded.
For example a Tab called "Other Player Info." But... those fields being
in their own subform, would not "necessitate" your breaking them out to
another table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Ken Sheridan]

Your first step is right, a table with one row per player containing their
static data is the right way to go.

Don't create separate tables per year for the other data, however. That's
encoding data (the years) as table names. A fundamental principle of the
database relational model is the 'information principle' which requires that
data is stored as explicit values at column positions in rows in tables.

What you should have is one new table, PlayerDetails say, with a foreign key
column, e.g. PlayerID, which references the primary key of the Players table.
This can include a SchoolYear column (or whatever you want to call it, but
don't call it Year, that's the name of a built in function) in which to enter
the year along with columns for the other data which varies from year to
year. Set PlayerID and SchoolYear as the composite primary key of the
PlayerDetails table.

For data entry you can use a form based on Players and a subform based on
PlayerDetails. In the parent form add an unbound combo box, cboYear say,
which lists the years over a period (20 years should be enough I'd guess).
Include this combo box in the linking mechanism with the subform by setting
its LinkMasterFields property to:

PlayerID;cboYear

and its LinkChildFields property to:

PlayerID;SchoolYear

The subform will show the data for the current player for whatever year you
select in the combo box on the parent form.

You could of course show the details for all players for one year in a
subform if you wished by basing it on a query which joins the two tables, e.g.

SELECT FirstName, LastName, TeacherID, etc
FROM Players INNER JOIN PlayerDetails
ON Players.PlayerID = PlayerDetails.PlayerID
ORDER BY LastName, Firstname;

Then include the subform in an unbound parent form with a cboYear combo box
as above and this time set the LinkMasterFields property to:

cboYear

and its LinkChildFields property to:

SchoolYear

Ken Sheridan
Stafford, England

 

[MikeB]

I want to thank all of you who answered (and always takes the time out
to answer me). Perhaps I need to add more information.

I keep all players (even if they have graduated from the school and
there is no chance for them returning to play again) in the database.
This is because I need a record of their games to calculate active
players' ratings. (I guess eventually, if a player has no active
opponents in the database, I can archive them out, but I've not yet
gotten this far).

Now some of the "ancillary data I asked my question about, is common
to all players. Some new questions were added later in the life of the
club, some questions on the registration form gets dropped. For
instance, this year we were "rewarding" good behavior in chess club
with a cookie at the end of each class. To do this, we had to ask each
child's parent if they have food allergies and whether it was OK for
their kid to get a cookie (If some parents said "no", we would not
have been able to do this). Next year, we intend asking for
information about the kids' extracurricular chess activities.

So my tables and forms need to cater for these changes. This is what
makes it (to my limited understanding of relational tables) impossible
to have all the data in one table and to display all the data on the
same form.

This is why I am planning to have the tabbed form. That way I can
programatically (I have not yet completely figured out how) display
only tabls of relevant data. So if a kid was not in the club on the
year that cookies were handed out, they wont have errors on their form
if you look at their player data.

I hope that somehow clears up exactly what I'm asking.

I will spend some more time rereading all the responses I have
received - often I find that if I study and think really hard about
what you guys say it does in the end make sense, even if it doesn't at
first read.

Thanks again.

 

[Clif McIrvin]

I want to thank all of you who answered (and always takes the time out
to answer me). Perhaps I need to add more information.

I will spend some more time rereading all the responses I have
received - often I find that if I study and think really hard about
what you guys say it does in the end make sense, even if it doesn't at
first read.

Thanks again.

Mike, that's exactly the way I have learned most of what I now know
about Access, Jet and RDBMS in general. Carry on! There is a learning
curve; and the help in these forums is really awesome!