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!!!

 

[RickXL]

Hi,

I can't do the whole thing for you but I can give you some pointers.

Rick's first rule of keys. If you have a unique object assign it a key off the clock from your database. A possible exception might be SSN. The reason you assign your own keys is that you can't rely on anyone else to keep them constant - even names change. I don't know about the US but I suspect that SSNs can be changed in exceptional circumstances - for instance, if they manage to use the same one twice. So the safe way is to assign each person a number yourself. There is nothing more annoying than having to re-write your reports and re-design a perfectly good database because someone else has changed their key for you!

You will need to assess carefully how many things you need to keep track of. Each object, or entity as they tend to be called in database circles, needs to have its own table. The rules for the table include the fact that each key needs to uniquely identify each row. The key can be compound i.e. comprise more than one column. Personnel tables are usually keyed on personnel number (the key mentioned above) and a date. The reason you need a date is because things can change over time. For instance, someone might move halfway through a course. If logging that is important then you might need to record both addresses. So, person 1 lives at Atown between these dates and in Bcity thereafter. You will need to decide if keeping all addresses is important. If it is then that is how to do it.

One of the problems is that there really is no 100% solution for a database. You end up with a lot of two or three column tables with a key and a single attribute. In practice you need to keep things with a fixed relationship together in one table.

From your first field list: Name, DOB, SSN, Rank, Commissioning Source, School, Degree, phone number, address, next of kin.

I am going to, arbitrarily, make the decision that you are mainly interested on the current courses and are less worried about tracking people's history. So, add a personnel number to that and you are good to go. In any subsequent tables, never embed any of the above data apart from personnel number. If you need Name, for instance, link back to the name table via personnel number.

I am slightly baffled by the Height Weight Test. Will each person ever do more than one? If they come back for a later course would you want to compare the old test with the new? If one person just does one Height Weight Test, and history is not required, then you could add that to the Name table keyed on personnel number.

If they do a test per Class then you need a new table keyed on personnel number and class.

Some things missing. You should have a Class table that links Class Number to Class Name. You might need another table with instructors and their numbers and what they can instruct. You are a bit short of dates as well. Think about the: How are we improving over time, type questions.

Questions: What is a Class? Is it a generic thing like Class 1 is Plumbing and Needlework or should it reflect the particular instance of that class e.g. the 2014 December Plumbing and Needlework class.

Other tips:
Google "Third Normal Form" (3NF).
Start from the end, know what you need to get out of the database first then work out what you need to put in.
Think how you separate last week's course from this weeks.
Think about the time-based reports to show changes over time (more/less courses, more/less attendees/instructors/resources etc)

I could go on but I had better stop!

 

[tjsly4]

Hey Rick! Thanks for weighing in on this task, which for me has been monumental....for some of you it might be a weekend hobby project. I was interested in the points you made, especially the idea of numbering each Soldier with a unique number. I think the SSN will do that, but you might be right that there is a possibility for duplicates.

For anyone else that wants to weigh in, each student will only go through the "course" one time unless they fail to achieve an 80% or better overall. In my original post I referred to each of the "classes" as courses.....in fact, the course consists of multiple classes, each with homework and tests. IF they fail, then that student might have to "recycle" back into the course and retake all of the classess and tests...not just the ones they failed the first time. This is important to note in my design concept because it shows the possiblity for "repeat customers" as it were.

Also, by way of correction to my first posting, the word class also applies to a body of students attending the course at the same time.....approximately 125 in each "Class". (example: Fiscal Year Class 1-15, FY CL 2-15, FY CL 3-15, FY CL4-15) as many as 4 classes at a time might be at the school at any given time. We typically do 8 iterations per year.

The physical fitness test and height and weight measurments are conducted at least 3 times during the course.

I am extremly interested in looking back at historical data. That is the main purpose of the data base I am currently attempting to build.

Thanks again everyone!!!

 

[RickXL]

I have used the SAP system at work. They have personnel numbers and a start and end date as a key in most tables. They have SSN as an attribute in one of those tables. In Europe we have to be wary of Data Protection Issues. One criterion is that no-one can find out more than they need to know about someone. If you use SSN as key then nearly every table will have that number in it so it will be difficult to restrict.

HJetght Weight: Clearly a 1:n ratio to the person then. That means a separate Height Weight table keyed on personnel number (whichever one you choose). You also need to think how you can separate the statistics of people who repeat the course. So date needs to be part of the key.

Class (as events and as a group of people) and Course. I did notice some ambiguity there. The basic technique is: Course is an entity so it needs its own table and its own key. This may only have the course title in it but it needs to be separate. If you refer to them as course 1/2015 up to course 8/2015 then that needs to be in there.

Presumably Classes are subdivisions of Courses, that is four per Course so the Class table will contain the Course key.

You then need a many:many table that links people to classes. Will you record Instructors? If so, then a new table and key for Instructors then a 1:many link from Instructors to Classes.

Now you will know which Instructors were associated with which participants (Personnel) on which Course. Will there be Subjects within Classes? If so perhaps the Instructors should link to there, instead.

When I am thinking about database design I usually start drawing the tables out on a piece of paper. Each one has a title and column headings then a sample row or two of the contents. I can then draw in the relationships and get a mental picture of the complete database.

The above is only a guide. If you define a Course differently than me then your design will be different, for instance.

 

[tjsly4]

RickXL:

thanks for your continued input. I started today by drawing all of my tables on paper as you suggested. I am now going through trying to make sure there are no duplicate data fields unless it is going to be my Foreign key.

I came up with 47 separate tables. I am finding that 90% of my tables will include my "unique student ID" that you recommended creating as my foreign key. I am trying to decide if I should do this in excel and then import them, or if I should just use an AutoNumber field. The problem is that I can't figure out how to use an AutoNumber field to relate two tables I can't replicate the auto number value in the second table? I think I will just create the Student ID in excel and import it.

I am worried about the number of tables and relating them correctly. Is this an obscene number of tables? I am basing the tables on data I already have stored in a totally unmanageable number of excel spread sheets.

 

[xenou]

Following up on what has been said (I think - I've not read it all extremely carefully) you can reduce the number of tables by letting one table serve more than one case.

For instance:

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.

This can be simply Grades (ClassID, StudentName, ItemID, ItemGrade);

That allows for any class to add any assignment, quiz, test, or other grade-able item to your Grades table without having to make up a lot of new tables all the time. ClassNumber, StudentName, and even Item (or ItemID) can and should all be foreign keys. You can set up your Items table to be the repository of all the different types of assignments. It would not be the worst thing you could do if you did not have a separate Items table. In that case you need to have the Item entered in this table, which is a little more work and some redundant storage (plus danger of items not being entered properly).

You might also find another approach is to use a linking table approach, with a linking table between Classes and Students (for instance): Classes_Students(ClassNumber, StudentID, ItemID, ItemGrade). A linking tables are slightly more advanced design concept, but worth a look and not as hard as it sounds. I could imagine other approaches, in fact. Again, better to have a separate Item table, but it will work either way.

Some notes:

A lot of pro's don't like using autonumber fields for *primary keys that will be seen by users*. They say that you are only guaranteed to have a unique ID, not necessarily a very pretty one. Supposedly keys can be even negative numbers. Personally I have *never* seen this happen in real life and I have used autonumber keys fairly often, though usually with datasets that are relatively small (under 10000, tops). Well, in point of fact, I've probably generated around 20,000,000 autonumber keys in my lifetime and I have never seen a single negative number show up yet. I assume the pros know what they are talking about but I'm not sure it's as big a risk as is sometimes advertised, at least for smaller projects. So either do as I do and use the autonumber keys, or you can also still use an autonumber key as a "hidden" primary key on all the tables, and have your own ID field where you enters ID's that will be seen by users. Access forms generally are very helpful in getting forms "keyed" up when you have main forms with subforms (ie., at the time of creation, and otherwise when viewing or editing data). Integer numbers are the best IDs, but simple alphabetic keys are fine too. Don't use descriptions as keys (such as peoples names or class names).

Avoid using "Name" or "Date" as a field name (these are reserved words in Access because it's a function operator in VBA - other such field names to avoid would be words like "Number" or "Count" - things that are part of the SQL Language or VBA functions). Also, no spaces in names! That's only for kids, not real DB work. Database fields start with letter and have only letters, numbers, or underscores. That's how every other database works except Access

Also, try to start out small and learn from your first design. Plan to build a better database next year, so don't try to do too much all at once. You'll make mistakes and not get it right the first time, but you'll do better later on. If you can create a design that's somewhat "modular", you can start with one piece, get it working, then add then next and keep going, which will be more satisfying then trying to get a perfect design that takes a year to build and still has flaws.

 

[RickXL]

Good advice from xenou.

47 tables sounds like a lot to me for the problem you have.

Reading xenou and re-looking at your first post, I notice that you suggest different tables for Class 1 Grades and Class 2 Grades. These should almost certainly be combined. The table holds "Grades". One of the attributes will be Class Id. Effectively, if you filter that table by the Class Id then you will have a separate table for that class anyway.

I am not sure that I understand your AutoNumber issue. One thing you might want to do is start the number from, say, 100000 and not 1. That way the numbers will be more easily recognisable as person numbers. (As for autonumbers going negative, it may depend on how the system treats numbers. 8-bit binary numbers can represent decimal numbers from 0 to 255 or they can also represent numbers between -128 and +127. So if something has a change of heart part way through the next person after 127 could be -128. If everything is defined properly then that should not happen. However, I see that Access does have some sort of issue: Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates)

In the personnel system that I use, if you are updating a series of tables where personnel number is the primary key then the input forms remember the number for you and enter it where necessary so the user only enters it once. As for foreign keys, if, say, you were adding people to a class then you would need to feed in the list of personnel numbers. I don't see a way round that. Again, the input form could have multiple personnel number fields on a single screen and might even have a way to paste batches of numbers in.

By the way, I have made my mind up about SSNs. These should not be keys. If receipt of one is delayed then it would stop you entering anything for that person. Also, if one needed changing for any reason then you would need to write a program of some sort to change it in every table. My advice would be to keep it as an attribute.

If you would like to post a list of your proposed tables I could take a look at it for you.

Another check: While it may be correct to allow for the fact that a student may return with a new name, are you bothered? Same with addresses. Do you really need to know the previous address of a returning person? Perhaps, as xenou suggests, you don't build a complex personnel system the first time you just have a single table with personnel number and a list of attributes. Similar checks could apply to other tables as well.

 

[xenou]

I hadn't noticed the question about SSN's as keys (it was only bolded and underlined, so therefore easy to miss ) RickXL makes a good point - it could really hold up data entry if you were waiting for information. Also it's a terrible privacy violation - you don't want SSN's being stored in plain text and seen by ordinary users if in any way avoidable.

 

[tjsly4]

Wow! Xenou and RickXL! Thanks so much for your pointers and discussion. I am going to heed your advice on the SSN's. It seemed like an easy solution at first, but the points you have made about slowing data entry and privacy are exactly right.



Below is what I was planning on doing this morning and my main questions are up front:
Do you like this spreadsheet for import? Should I add or take away fields?
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. (I am using the word course to describe the entire 6 month education process that includes attendance of 100’s of classes taught by 4 distinct academic disciplines.)


DETAILS
I have created an excel sheet with the following columns:

Class_Number //Full_Name // Ssn // Unique_Student_ID

Here is some info about each column’s attributes. I would appreciate it if you could nitpick at these attributes so that when I import to Access I have accurate data.

Class_Number: (i.e. 1-15, 8-10, 4-13) This number is assigned to each class by big army by a big army DB. The number after the dash represents the Fiscal year; the number before the dash represents a sequential order of each class in each fiscal year. For example, 1-15, is the first class of FY 15. In excel I have this as a “text” column.

Full_Name: (i.e. Doe, John, K.) Names are imported from the big army DB in this format. For all the years prior to me arriving, these names have been left in one cell and never separated into the components. I am inclined to leave it this way? In excel I have this as a “text” column

SSN: (123-45-6789) Out of 4000+ records, I am missing about 400. So as you mentioned above, a bad primary key. I have this formatted in excel as “special number” and I chose social security number format. I stripped out any dashes imported by users using "replace" in excel.

Unique_Student_ID: (i.e. StudentID101, StudentID102) I created a calculated column. I made the formula number each row for me

There are more than 4700 rows in this one sheet at this time.

 

[xenou]

No problem there, really. You will have to have something besides row number if you have to add a student later (your id scheme requires 100% of the personnel be in the Excel sheet up front so you get a row for everyone). That will probably come down to "SELECT Max(StudentID) + 1 As NextID FROM Table" - if it comes up. I'd still probably prefer just plain number (easier to type). Which leads me back to just plain autonumber, maybe also (easiest of all). Your scheme would keep a key relationship back to the spreadsheet if you intend to maintain the spreadsheet as the master personnel record (but it sounds like you are rather just using it for a one time load of data, instead, so that shouldn't matter).

However, be careful with your data in Excel - the classes data goes to a classes table, and the students data goes to a students table. That is not one table in that spreadsheet, though it might look like one to the non-database designer.

The more I think of it the more I like Students_Classes (or Personnel_Classes, depending on your naming) as a linking table. Then you can have any number of students and any number of classes. You can also have students in the same class more than once, which solves an earlier problem you mentioned. You can almost have it up and running today, it would seem ...