Help ON my DB Design

[tjsly4]

Sorry this is so long, any help will be GREATLY appreciated.

Hello, I am a CPT in the US Army working in a large organization in charge of training new lieutenants (over 1,000) per year. As you can imagine, my superiors have an insatiable desire for data points to analyze performance, effectiveness, etc. My Soldiers time is constantly taken in an effort to answer these questions from on high. The main problem is that 5 or 6 different departments use different excel spread sheets to track each class/platoon/Soldier in different formats. Currently, in order to analyze that data, my Soldiers have the tedious process of opening each spreadsheet and physically assembling data.

I have been learning all I can about Access DB for the past 4 weeks. I have assembled all possible fields, thought about what tables need to be created, looked at how to bring new data into those tables in the future, and tried to design as many reports and queries as I can foresee. I have been studying how to make relationships between my tables.

I have made about 15 attempts so far, and scrapped each one because I realized I had too many issues to keep trying to use any of the attempts. I can't seem to find good primary and foreign keys for each table.

Here is my main question: Should I plan on importing each Students Social Security Number into the tables as a primary or foreign key? Or is there some other field that would work better? Can I get my parent table built, and then use a query to attach SSN's to names after I import new tables?

Here is an idea of the tables and fields I am currently trying to work with:
(my best idea currently, is to put all school years back to 2011 in each table, in other words, tables include multiple years worth of data)

Table: Student Admin data ***my main parent table***(fields include: Name, DOB, SSN, Rank, Commissioning Source, School, Degree, phone number, address, next of kin, etc.

Table: Physical Fitness Test Results (fields include: Class Number, Name, push up score, sit up score, run time, overall score, pass/fail)

Table: Height Weight Test (fields include: Class Number, Name, height, weight, waist measurments, neck measurments, body fat percentage, pass/fail)

Table: Course 1 Grades ( fields include: Class Number, Name, Test 1 Grade, Project 1 Grade, Test 2 Grade, Project 2 Grade, Homework Grade, overall Grade, class Ranking, etc.

Table: Course 2 Grades ( fields include: Class Number, Name, Test 1 Grade, Project 1 Grade, Test 2 Grade, Project 2 Grade, Homework Grade, overall Grade, class Ranking, etc.

There are many more course tables, a table for honors and awards, a table for tracking arrival and departure dates, and more.

A MILLION THANKS TO ANYONE WILLING TO WEIGH IN ON THIS MESS!!! I NEED A DATA BASE!!!

 

[xenou]

Hi,
So here's a quick shot (probably full of holes):



Classes (ClassID, OtherField1, OtherField2, OtherField3, LastUpdated);

Students(StudentID, Full_Name, SSN, LastUpdated);

Classes_Students(ID, ClassID, StudentID, ItemID, Grade, PassOrFail, DateCompleted, LastUpdated);

Classes_Items(ClassID, ItemID)

Items(ItemID, ItemDescription)


The last two tables could be structured as one:
Items(ItemID, ClassID, ItemDescription) Foreign Keys: ClassID
This might be easier (not sure). However, if you do have the two tables (Items and Classes_Items), then the Key in Classes_Students would be the ItemID in the Classes_Items table -- i.e., it would need to be an item that exists for a class, not just any old item.

As mentioned by RickXL, it's often helpful to get dates into the database, if nothing else then as a barebones logging mechanism (at least you know when the records were created). I usually have such a field called "LastUpdated", "DateCreated", "DateModified" or some such, with the understanding that this means "in this database" - a personnel file might have been created elsewhere so I would not consider my "DateCreated" fields on a par with "DateHired" or anything like that. So you see in one of my tables DateCompleted means completed the course, whereas LastUpdated means the date the record was added or modified. However, if DateCompleted is entirely driven by the course (all courses have a set date to start and end) then that doesn't belong in that table, but rather is an attribute of the course. Of course then you really have another table of courses - since English101 can be this year and/or next year - you see why the DBA's make the big bucks now. Your course ID's probably don't need to get that level of detail, and in any case have a date identifier embedded in the ID, at least insofar as the course year goes.

I added a PassOrFalse so you could quickly get a boolean yes or no to report if all classes are taken and passed. But if all grades have the same threshold (C or better is pass, for instance), then it's redundant. I wonder how most colleges handle this. They probably have a completion status (Pass, Fail, PassNoGrade, Audit, Incomplete, etc. etc.).

 

[tjsly4]

Amazing feedback! A few questions from your reply above:

1. is SELCT Max(studentID) + 1 a formula I can apply to a field in Access? Or was this an excel formula you were recommending for adding data in my excel sheet? (I don’t know anything about syntax in Access)

2. If I make a linking table for Students_Table and Class_Numbers_Table, will I still be able to build Queries that include data for recycled students 1^st class and exclude data from 2^nd attempts at class? Would it be a messy query.
I think I am in love with this many to many table to solve my students to classes relationship. I am going to start building that right now.
3. Among my 47 proposed tables I mentioned was a table I am calling Class_Index_Table. Proposed fields:
ClassNumbers // StartDate // EndDate // TotalStudents
If I remove Class_Number from my Students_Table, would this proposed Class_Index_Table lend itself to a link table between the Students_Table and Class_Index_Table? Can I add more info to my Student_Table? (i.e. sex, Date of Birth, Married, Spouse name, #Kids, etc)
4. I only have 4 Commissioning Sources (U.S. Military Academy, ROTC, FOCS, SOCS). Should I include that as a field in Students_Table or should I link it to my Students table with a Link table? Seems like a lot of extra data work for me, but maybe better query results.
If I am wearing you guys out, I apologize. If there is anything I can do to repay the favor, I would be more than willing. Already recommending this site to everyone I can.

 

[xenou]

Hi,

1. That would be Access syntax (more precisely, SQL syntax). You could write such a query in Access

2. I think not a messy query. You'd probably just write a criteria in your query to get the latest or most recent attempt. It's standard fare for SQL.

3. If you have a Classes table and a Class_Index_Table then the first is a "generic" course (ID, description) and the second is a particular instance of that course that takes place at a certain time and in a certain location (9:00 AM M-W-F in Building A, Room 1, Fall 2014). You want your Classes_Students table to link up to the latter - the course that exists in time and space (i.e., the one that has students in it). Class_Index_Table might, however, have a foreign key to Class_Table, the latter being the "Master List" of classes that may (or may not) exist, and containing the general information common to instances of that class (be they in building 1, or 2, or at 9:00 AM or at 12:00 noon - they would all still be English101). By the way, you don't strictly need Total Students or student count anywhere - that can be counted on the fly. Just query for the students in the class and the number of rows returned is your count.

4. Commissioning source - not sure exactly. That sounds like a Student attribute. If every student has one and only one commissioning source, it could be in the students table as something that is descriptive of the student (just like Date of Birth or SSN - all of which, BTW, could be there too).

 

[tjsly4]

Beautiful...thanks for the clarification. So much to learn! So little time to get it right. Wish me luck!

 

[RickXL]

Hi,

I have read through the additions and agree with xenou. There are just a few things that leapt out at me:

===

"What are the effects on my DB and future reports/queries if I give “recycle” students a unique student ID each time they go through the course."

The answer is that you lose your history. If you did that how would you know someone was repeating the course? They would look like a completely different person who just happened to have the same name. However, you decide to handle it you need to ripple it through all the affected tables to make sure that you do not build in a design fault. (The same applies to any change as you home in on a solution.)

===

"StudentID101"

I think just 101 will be enough for the key. Each time you display it you could still show StudentID101 but there is not much point storing StudentID a few thousand times.

===

" If I make a linking table for Students_Table and Class_Numbers_Table, will I still be able to build Queries that include data for recycled students 1st class and exclude data from 2nd attempts at class? Would it be a messy query."

This is one of the "ripple through" points I was making. What piece of information will tell you that this is the second attempt?

===

"I only have 4 Commissioning Sources (U.S. Military Academy, ROTC, FOCS, SOCS)."

Two points:
1. If you don't have a separate table for Commissioning Sources how would you verify that the entered string (ROTC etc) was correct?
2. Would you ever need a description or explanation of those names or abbreviations?

My starting point would be ...

Table: CommissioningSources

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CommSrce
[/TD]
[TD]Name
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]USMA
[/TD]
[TD]US Military Academy
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ROTC
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]FOCS
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]SOCS
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

You could abbreviate that to just the Name column but I feel that anything that should be validated against should have its own table.

 

[tjsly4]

Thanks RickXL! I am going to adopt your advice on the Student_ID field.
====
When it comes to the recycle students, you asked the key question that I had not thought through yet. "What piece of information will tell you that this is the second attempt?"
A possible solution is one of my 47 proposed tables. It is called the Recycle_Report. Here is a sample.

Student_ID

<tbody>
[TD="width: 66, bgcolor: transparent"] Full_Name [/TD]
[TD="width: 72, bgcolor: transparent"] Initial_Class_# [/TD]
[TD="width: 66, bgcolor: transparent"] 2nd_Class_# [/TD]
[TD="width: 66, bgcolor: transparent"] 3rd_Class_# [/TD]
[TD="width: 66, bgcolor: transparent"] Recycle_Code_Initial [/TD]
[TD="width: 90, bgcolor: transparent"] Recycle_Code_2nd [/TD]
[TD="width: 90, bgcolor: transparent"] Recycle_Code_3rd [/TD]
[TD="width: 67, bgcolor: transparent"] Remarks [/TD]

[TD="width: 55, bgcolor: transparent"] 4388 [/TD]
[TD="width: 66, bgcolor: transparent"] Schmo, Joe [/TD]
[TD="width: 72, bgcolor: transparent"] 2-13 [/TD]
[TD="width: 66, bgcolor: transparent"] 5-13 [/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"] 1 [/TD]
[TD="width: 90, bgcolor: transparent"] Grad [/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"] Grad 2^nd attempt
[/TD]

[TD="width: 55, bgcolor: transparent"] 472 [/TD]
[TD="width: 66, bgcolor: transparent"] Doe, John [/TD]
[TD="width: 72, bgcolor: transparent"] 1-14 [/TD]
[TD="width: 66, bgcolor: transparent"] 4-14 [/TD]
[TD="width: 66, bgcolor: transparent"] 8-14 [/TD]
[TD="width: 66, bgcolor: transparent"] 1,2 [/TD]
[TD="width: 90, bgcolor: transparent"] 2 [/TD]
[TD="width: 90, bgcolor: transparent"] Grad [/TD]
[TD="width: 67, bgcolor: transparent"] Grad 3^rd Attempt [/TD]

</tbody>

Currently an excel document, I won’t import the Full_Name field. Student_ID could be related to my Student_Table as a one-to-one foreign key?

 

[xenou]

Hi,
You're Excel layout is breaking the rule of "no repeating fields". When you see a 1,2 or 3 ... (or 1st, 2nd, 3rd ...) in field names that's a dead giveaway. In this case, RecycleCode_Initial, RecycleCode_1st, RecycleCode_2nd, .... is very suspicious. The main problem will be that this makes for real headaches writing queries using SQL and what should be easy becomes hard when you get around to creating your reports.

You keep your data structured with one field for each of these things (ClassID, Class_Num, Result):

-----------------------------------
| Student_ID | Class_Num | Status |
-----------------------------------
|       4388 |      2-13 | Repeat |
|       4388 |      2-13 | Grad   |
|       4388 |      5-13 | Grad   |
|        472 |      1-14 | Repeat |
|        472 |      1-14 | Repeat |
|        472 |      1-14 | Grad   |
|        472 |      4-14 | Repeat |
|        472 |      4-14 | Grad   |
|        472 |      8-14 | Grad   |
-----------------------------------

You may think "how will I get my count now?" But that will not be difficult at all when you apply a little SQL:

SELECT StudentID, 
  Class_Num, 
  Count(Class_Num) - 1 As NumberOfRepeats 
FROM MyTable
GROUP BY StudentID
HAVING Count(Class_Num) > 1;

In fact, you could probably get away with class_id and class_num alone - one can assume any repeated class_num's are repeats, and the last one is a grad (actually, maybe at the end of the year the student never successfully finishes the course - could be one of your students that come back the next year ....).

The concept here is called "normalization". Actually the classic case of normalization is indeed going from a flat file structure (i.e., your Excel sheet) to a relational database structure.

In general I would agree with RickXL's initial explanation way up at the beginning - organize tables as objects that have a distinct identity and only include fields that belong to that object (Classes have nothing to do with student's names, and student's don't need to have any attributes related to classes. It is only when a student takes a class that the two tables start to have something in common - and that's when we start to develop tables with foreign keys and the like - such as a Classes_Students table). However, this idea about *no repeating fields* is a very foundational one as well. You will see the difference is dramatic when you start adding data to tables, and writing queries, forms and reports.

Currently an excel document, I won’t import the Full_Name field. Student_ID could be related to my Student_Table as a one-to-one foreign key?

Note, here I would guess that Student_ID is the primary key in your student table. The same table will have the student name. So strictly speaking, it's in *other* tables that you use Student_ID as a foreign key.