DB design help

[Dazzy]

Hi Folks

I have been asked to design a new DB for our training department but I believe it may beyond my ability, i just cant see a way to do it.

I designed the old but but it had alot of mistakes as I was only beginning to use Access then, ie personal details had to be entered for each course, and other stupid things like that.

This time my idea would be to:

Only enter personal details once
Students may attend for multiple years (Sept 1st - Aug 31st)
Students may take many course per year
Import data from Awarding Bodies online interchanges
Export data to meet new requirements online of some courses
Track course prerequisites and course credits
Implement login process and Audit trail.
Produce a series of reports based on all this etc.

Problem I am having after this is each course has it's own set of requirements in regards to what information we have to record for audit purposes, different units, different exam codes etc.

So I am thinking each course will need it's separate tables, and this is where I am stumped. I just ain't sure how to link all these together, are there any good student sample databases I could look at?

Any advice/help would be greatly appreciated.

Many Thanks all

Gary

 

[MarkK]

- You don't sound happy about the job and that dissuades me from taking a lot of time to answer your questions. Have you been 'asked' or 'told' to do this job?
- The thing is, I like databases, and programming, and data structures. Coded solutions are like little engines, and I feel cool when I build one that runs silently and still produces tons of horsepower.
- And c'mon, somebody obviously thinks you're the best person for the job!

- Narrow your focus. Start with the tables.
- - A table answers "What is it?"
- - A field answers "What does it have, or what is it like, or what shape is it in?"
- Maybe it 'IS A' student who 'HAS A' name and 'HAS A' birthdate. All students go in tStudent. Ex-students, new students, green students, dead students, and for each new piece of info you need to descibe a student you add a field to tStudent.

tStudent
StudentID
FName
LName
DOB
IsTall
HasFeet

- To restate: you don't create a selection of Student tables, like "tStudentsNamedEd" and "tStudents2009" and "tExStudents". One table is the authoritative and comprehensive definition of one kind of thing.

tCourse
CourseID
CourseName
CourseNumber
TeacherName? <-no, that goes in tTeacher.

- and I could go on. But post back here if this is interesting or useful to you.
Cheers,
Mark

 

[Dazzy]

Hi

Thank you for your reply I was basically told I had to do it. They assume I know everything about databases and that could not be further from the truth, as put me next to someone like you and I am a complete beginner.

I understand the concept your saying there, and I could set that up and have done to a degree already, but the problem I will face very shortly is how to filter form fields based on the course the individual is doing, this is the sort of stuff that totally throws me out.

I know anything can be done basically when you get down to the code level but I don't really understand VBA at all.

I am also having to design this in office 2000 format using office 2003, while some pc's have Office 2007 others done and in that sense our workplace is a joke.

Lets say I have something like

tClients
personal details
next of kin etc

tcourses
course info, ie name, start etc

tunits
contains fields for all units/courses, ie exam code, date, times, result etc

This is where I see the problem coming for me, (I know that's probably not the best table structure too).

If a student is doing, for argument sake Course 1 and Course 2, the fields I need to show might not need be the same for both Courses. So basically at this stage what I am asking is there an easy way to filter the fields in tunits depending on the course selected in tcourses.

I hope that makes sense.

Thank you for taking time to reply and sorry if I sounded abit off in my original post.

Regards

Gary

 

[Scooterbug]

Step back, take a deep breath and grab some paper and a pencil. Let's not put the cart before the horse.

The first step in good database design is to have good table structure. Lagbolt started you on that path...now you need to keep walking.

To go to your example, on the Course table. First you need to determine what is going to be a constant across all the Courses. For example,

tblCourse
CourseID
CourseName
StartDate
EndDate
TeacherID (Foreign Key)

Now, if each course has different requirements, then you need a separate table to record those. You could then use a junction table to record the requirements that go with each Course.

tblRequirement
RequirementID
Requirement info

The Junction table would look like this:
JunctionID (Pk)
CourseID (FK)
RequirementID (Fk)

 

[Dazzy]

Ok I think I understand that and sorry for what probably is a stupid question but do I then need separate requirement tables for each course?

For instance one course will require a Virtual Registration number which other courses dont need. I still need to go through the new guidelines for two courses as Audit requirements are changing, but I'd like to get an idea of how to do this before hand.

I have never really understood junction tables and I think that's where my problem comes to thinking around these things.

 

[Scooterbug]

The requirement table isn't linked to any particular course per say. It's a table to store anything that can be constituted as a Requirement for ANY course. As some courses may share a requirement, this will insure that you dont store duplicate Requirements in other tables.

Having a separate table of requirements for each course violates the rules of Normalization. The first thing that should pop into your head is: What happens when a new course is added? If you have a separate table of requirements for each course, then you would have to create a new table. Where as if all the requirements were in one table, and you needed to add a requirement that wasn't already in there, all you would need to do is add a new record to the requirement table.

Are you familiar with Normalization? If not, that would be a good place to start. It may help you see things from a different perspective and clarify things are you are designing the database.

 

[Dazzy]

Hi

Yes I understand Normalisation, by trade I am a maths teacher and always look for the shortest route lol as the saying goes mathematicians tend to be lazy.

I actually realise what a dumb question that was as I was making the assumption (wrongly) of creating the form from the table where in face it should be created from a query.

I have to get out of the mode of teaching as the courses I teach the syllabus only require you to create forms from tables.

 

[Scooterbug]

You should be on the path now...feel free to come back with any questions you might have. Plenty of experienced individuals here who have no problem helping out others.

 

[Dazzy]

Hi Guys

I havent been able to sleep and been battling with this for the last few hours, it's now 5am, but just not sure I am doing it right.

please see attached screenshot, Unitname I know is wrong and will change to UnitID.

Any advice would be appreciated.

 

[Scooterbug]

I know I learn better by seeing things...so I did up a quick little db that might help you out.

I'm not sure what the difference is between Course and Unit. The attached database is setup as follows.

There is a table for the following:

Students
Courses
Requirements

Junction table to store the requirements for each Course.

Table for Creating a schedule for students. Contains the StudentID and information for that particular schedule (Start and End Date, etc)

Table for ScheduleDetails. This stores the ScheduleID, CourseID. You can add other things likes TeacherID, ClassAverage, etc.

Any questions, let me know.

 

[Dazzy]

Hi

Thanks for the example I will take a look at it whenever I get a chance.

The difference between Course and Unit is, each course is modular and split into units, some course have 8 units which have to be achieved to gain the certificate.

Others 2 units will achieve an Award, and then based on the credit score of units more need to be achieved to receive the certificate and diplomas.

 

[Scooterbug]

So each unit contains multiple Courses. Got it.

So you should treat each unit like a schedule. There should be one table that holds the info for the Unit (UnitName, ID, Other suff) and then have a UnitDetail table that will have the following:

UnitDetailID (Primary Key)
UnitID (Foreign Key)
CourseID (Foreign Key)

 

[MarkK]

I know what a student is, and a course, but what is a unit? What is a requirement?
Consider that a schedule might not need it's own table. In respect to the student, a schedule might be generated from the intersection of the student and the courses the student is registered for.
A course might need three tables to be entirely defined.
There is ...
1) the course as it exists in a catalogue, say Psychology 101.
2) the class (is this a unit?) as it is offered in any semester, which has a specific prof, students, and class size
3) the days of the week, times of the day, and room number--schedule.

tCourse
CourseID
CourseName
CourseNumber
Description

tPrerequisite (not essential to fundamental system design)
PrerequisiteID
PrerequisiteCourseID
CourseID

tClass (maybe a unit?, a specific instance of a course)
ClassID
CourseID
ProfID - (unless you can have multiple profs)
Semester
Year
MaxStudents

tRegistration (student's connection to a class)
RegistrationID
ClassID
StudentID
CourseworkGrade
ExamGrade
AttendancePercent

tSchedule
ScheduleID
ClassID
DayOfWeek
Time
Room

So 'schedule' here is strictly a definition of when and where a class will occur. In respect to any student, I expect a 'schedule' will need to be constructed from the various tClass items they're registered for, and you could fairly easily automate a conflict detector.

 

[MarkK]

Here're some ideas

 

[Dazzy]

Hi Guys

Thanks for the examples I will be able to get these to the stage where I can use them, but I still cant see a way past my original issue, I guess I don't understand relational databases enough to see how using a link table will change the form fields based on course selected.

Just to clarify Units make up Courses, so in the example shot I posted each course contained multiple units to make up the overall qualification.

 

[Scooterbug]

As for what your forms will look like, using subforms will be the way to go. For example, You could start out by using a combo box to find the student. Once a student is selected, you would then store the ID, and have a subform that will display the student information.

Getting ready to leave for the day....but in a nutshell, I look at this like a standard Order form. You chose the Customer, then you choose the Courses or units that each student will be taking.

 

[Dazzy]

Hi Guys

Once again thanks for you help it has got me to the stage now where, I have a few sample subforms and using the afterupdate event on the CourseName combobox to change the subform accordingly.

The problem I face now is I cant see a way to make the subform refresh if I view a different course.

Lets say I am viewing a students course 1 which shows subform1, if I go to Course 2 it still shows subform1.

I am using a simple if else statement for testing and will change it for a cleaner case statement later when I get the working model.

If Me.CourseName.Value = "Course 1" Then
Me.frmSubUnits.SourceObject = "qrysubfrom1"

ElseIf Me.CourseName.Value = "Course 2" Then
Me.frmSubUnits.SourceObject = "qrysubfrom2"

ElseIf Me.CourseName.Value = "Course 3" Then
Me.frmSubUnits.SourceObject = "qrysubfrom3"

End If

Is there any event which is triggered when you go to the next/previous record?

Many thanks again

 

[Scooterbug]

You can force a refresh of the data in a subform with a simple requery command.

in this case, try: me.frmsubunits.requery

I am assuming that frmsubunits is the name of the subform container.

As for events that trigger, I believe that the On Current event will fire when going to the next/Previous record. You could also turn off the naviagation buttons and add your own, this way you can put code in to fire when a record changes.

 

[Dazzy]

Hi

You are correct on current works fine, sat looking at it all day and didn't see it till I posted the message, isn't it always the way?

Out of curiosity if the CourseName combo box is empty (ie new record) is it possible to set the SourceObject to null so no subform loads, but instead a caption would appear on it saying please select a course. I tried empty strings but it didn't work.

If Me.CourseName.Value = "Course 1" Then
Me.frmSubUnits.SourceObject = "qrysubfrom1"

ElseIf Me.CourseName.Value = "Course 2" Then
Me.frmSubUnits.SourceObject = "qrysubfrom2"

ElseIf Me.CourseName.Value = "Course 3" Then
Me.frmSubUnits.SourceObject = "qrysubfrom3"

ElseIf Me.CourseName.Value = "" Then
Me.frmSubUnits.SourceObject = ""

End If

 

[Scooterbug]

Out of curiosity if the CourseName combo box is empty (ie new record) is it possible to set the SourceObject to null so no subform loads, but instead a caption would appear on it saying please select a course. I tried empty strings but it didn't work.

Dunno...but you could always set the subforms visible property to False if the combo box is empty. You could also have a label that becomes visible in the subform's place that says whatever you wanted it to. I would set the visible stuff in the Case Else portion of the Select Case that you plan on adding.