Setting up Tables

  • Thread starter Thread starter Gntlhnds
  • Start date Start date
G

Gntlhnds

I'm sure people make posts like this frequently, but here comes one more.

I'm creating a database, and I first started out trying to modify a template
to suit my needs, but after spending umpteen hours on it, I've decided to
start over from scratch so that I can set it up to be the most efficient.
Having said that, I'm at a loss as to setting up my tables properly so that
they will have the proper relationships.

Here is what I need from my database, and maybe someone will be nice enough
to explain what I should have in which tables, how the relationships should
be set up, and then I can take it from there to create the forms, reports,
and queries.

The purpose of the database is to manage student data, but since it is for a
military school, there are some other things that I need to track that
civilian schools will not have.

Student Data:
Last Name
First Name
Middle Initial
Rank
DOB
SSN
Phone #
e-mail address
Home of Record (Address, City, State, ZIP)
Room #
Class # (there will be multiple students per class)
Status
Arrival Date
Graduation Date
Departure Date
Follow-On Orders
Notes
Archive (a checkbox that is used to determine if the student is a current
student or prior student)

PFA Data (Physical Fitness Assessment): (These are conducted every two weeks
and I need to track each students results of the assessment):
Date
Name
Age
Height
Weight
BCA
Sit-Reach
Sit-Ups
Sit-Up Score
Push-Ups
Push-Up Score
Run Time
Run Score
Composite Score

Barracks Data (I'm using a separate table so that I can create a combo box
on the forms that will show which rooms are available when I intake a
student, and then using the Archive field, I can remove a student from a room
and make the room available in the combo box):
Room #
Key #
Student Assigned


Any help in setting up the tables properly would be greatly appreciated.
Thanks in advance.
 
Oh, I should also add that I would also like to track grades for the classes
as well (there are about 13 classes they take). Thanks again!
 
I would look at the PFA table. The biggest problem is that you are going
across, like a spreadsheet, instead of down, like a database table. Ask
yourself this: what happens to your table, queries, forms, and reports if
someone decides that pull-ups would be good for PT? They may all need
changing.

I recommend something like so:

OH! Before we go there, don't use Date or Name for field names. I'd also get
rid of the dashes - in the field names. Why? Those two words are reserved and
can cause problems. I also recommend no special characters, including no
spaces, in field or table names. The underscore _ is the only exception.
Looking at your other fields, definitely dump the # in the names. Push-Ups
could be PushUps or Push_Ups for example.

Back on track: You should have a primary key field in Student_Data. The SSN
could be a candidate as long as every one of your students have a SSN. If you
are US military, that shouldn't be a problem but don't forget the Privacy Act
of 1974! With the primary key in the Student_Data table, you don't need to
repeat things like Name and Age in the PFA table.

PFA_ID STU_ID PFA_Date Weight BCA
1 1 1/1/10 210 1
2 2 1/1/10 134 2

Activities Table

PFA_ID Activity Result
1 Run 12:30
1 SitUp 23
1 PushUps 13
2 Run 11:20
2 SitUp 33
2 PushUps 23

Where are the scores? If you have a formula to compute them, you do that as
needed based on the age, gender, activity, and result as needed in a query,
form, or report. You can even compute the age from the DOB field. You
shouldn't store such derived data. You'll need to join these tables
togethere to get it to work.

If this all sounds like gibberish, maybe I haven't explained it well enough.
In that case I highly recommend getting some relational database training or
reading "Database Design for Mere Mortals" by Hernandez before proceeding any
further on this database.
 
Thank you for the speedy reply. For the time being I think I'm going to
continue to use my old database from the template and continue to modify it,
so changing the PFA table to match the accepted conventions may not happen
just yet, but it is great info for when I create the table to keep track of
the student's grades. I'll make sure I use the set-up you mentioned. I wish
the templates Microsoft supplies would use the accepted conventions for
naming, relationships, and normalizing. From now on I'm going to start from
scratch whenever I need to make a new database.
 
Back
Top