New Database Design Help

[mattyn]

Hi All

This seems a good place to start. I have a fair bit of historical IT knowledge - HND level but it was about 13 years ago. I am pretty good at spread sheets, and was reasonably ok with Access - but that was Access 2K.
Now I am in a new job (education facility) there is a massive requirement to move from spreadsheets to an all inclusive management database. Eventually I need to pull all sorts of stats but I will come to that later.

Courses: The facility I am in runs a lot of short term courses - from one day to 8 week duration. Each course is run multiple times per year (the minimum of each course occurrence is laid down by the head office). Each course is grouped into pillars (each pillar has about 15 courses). Each course has a number of EOs (enabling objectives) but not all are reached and I need to be able to see what EOs are not reached.

Students: Students may attend multiple courses (rarely the same course twice but not unheard of should they fail). They are awarded qualifications on successful completion of a course, but that is dependant on what EOs were reached. I need to chase students 6 months after a course to carry out a Course Review Questionnaire.

Staff: Each member of staff has a title, staff number, position name, position number, and fits into one pillar for course delivery. Also, each member of staff needs to complete some mandatory training annually - about 15 small courses which I need to track their due date for each staff member.

I also want to be able to manage classrooms - we have a number that differ in maximum capacity - from 6 to 16. Each course has a maximum capacity of students (based on course content not classroom size) and I need to be able to link the two together to enable a slightly easier planning phase of course generation.

First question is - this year we are planning 500 course occurrences with about 3000 students going through. Is Access up to the task?

Second question - I have started to design this, but scrapped it as I think I am trying to solve all the issues at once. What is the best way to attack this problem?

Third - the HQ are mental about stats. How many training days are we actually delivering, what EOs are we missing, why are students attending the course, how many courses were cancelled for non-attendance, how many no-shows did we get per year, to name but a few.

I see this as three databases in one - a Student, Staff and Course Management databases.

I would really appreciate some expert thoughts on this. Even if I don't get HQ to support it, I am quite keen to build it as a personal project - a bit of self learning!

Many thanks in advance

Matt

 

[Rijnsent]

Hi Matt,

the amount of data you're handling should fit in Access nicely (even in one database). Given the description: I have to agree with you it's a bit too much to start with all of that, the main question is generally where the most value can be added in the shortest time. So step 1 for me would be to find out which work is currently done manually and is either very time consuming / boring / high chance of errors (or the highest on the agenda of your manager ).

To start simple, I guess the "Staff" part is the easiest thing to get started. You'd need a table with staff with the fields you mentioned plus probably their start & end date (as you don't want to chase somebody who hasn't been working for you for some time). A second table you would need is something like staff_training_year with a line per staff member (linked to the staff table) per year per training, a due date and a "training done" date. The challenge is: how do you create the records? You probably don't want to type them all in manually? And how do you "check them off", will the staff members do that themselves or is there somebody filling those fields in your database with a form (or even uploading an excel)?

Once you have the data in the database, making reports, dashboards and queries is rather easy. For me the challenge with this kind of databases is to make them fun/okay to use by the end-user, and help them doing their work easier/faster etc.

Hope that helps a bit, but do feel free to post more questions, I'm happy to help with code/samples,

Cheers,

Koen

 

[mattyn]

Hi Koen

Thank you so much for taking the time to reply. I have no real official direction from HQ to achieve this - it is something we all feel is needed, but Access is not too well supported by the firm, and therefore I want to create something, and if nothing else, learn a new skill.

Staff is an ideal place to start, as we were hauled over with not tracking mandatory training at our last quality assurance assessment. I will have a think and a read up this evening, and post some suggestions for my tables and see what you think. I want to also add in when the staff take leave, and what contact time they will have with each course, but I suppose that can wait. Basics first!

All the best

Matt

 

[mattyn]

My first effort tonight at trying to nail the Staff bit - thoughts appreciated?

It all seems to work, though it is measuring 1.3MB at the moment. Do they always balloon in size?

I am working on the principle that I am best served building the tables first before fancy forms. Is this correct?

Matt

 

[GinaWhipp]

No image? In any event, perhaps this will help...

Training with Attendance
Data Model - Training with Attendance

 

[mattyn]

I don't know what happened with the image - I can see it perfectly!

Trying again....

 

[Rijnsent]

Hi Matt,
a couple of things jump out of your scheme:
-a couple of field names are texts with spaces, a general convention is not to use spaces. E.g. "Tel Ext (93781)" -> "PhoneExtension", see the link that Gina provided for some good examples
-plenty of fields are called "ID", I would pick a name related to the table (or a letter) to make them unique throughout the database (rankID, staffID, etc.)
-tblJPAN is twice in your scheme, I guess "position" is the unique feature for a staff member, and JPAN is a property of the position?
-I guess you want to register your trainings in tblStaffMandTrng? I would go for: add a field for "training_completed_date" to be able to check if/when the training was completed. You stated that your staff has to do certain courses every year, so maybe you want to add a field "valid_for_year"?
-I am trying to understand the tblStaffAdqual table, what is the purpose of that one? You probably want to get rid of the "extra" link between the two ID fields (of tblStaff and tblAdqual)

Hope that helps a bit,

Koen

 

[GinaWhipp]

Hmm, well...

1. If those ID fields are Autonumber your database will break. Autonumbers do not update in unison. To relate Tables you need to put a Foreign Key in the sub-tables
2. tblPAN and tblRank is related to two different fields, why? No reason to have the same data in any Table twice
3. tblAdqual cannot be related to the PK in tblStaff it will never work (and what does that stand for?)

If you go to the link I provided there are some links about Database Design, you might want to have a look at those.

 

[mattyn]

Thank you Koen. That does not seem too bad - I was sort of expecting a whole lot more to be wrong with it.

Understand and remember about not using spaces. Will sort that. Also will sort the ID fields - they are simply the auto number for each table.

Each employment position has a name and number. tblJPAN was created once (it details employment position name and position number just as you assumed). I use it for a drop down menu on the Staff table Position name field. The second (tblJPAN_1 created automatically as I use it for the drop down menu for the position number). One person can only fill one position, multiple people can fill one position (normally when new staff come in to take over).

tblStaffAdqual table: All staff have some qualifications to gain to do their job - these qualifications are attached to the position they fill. We need to keep track of what qualifications are attained by staff to calculate our own training burden. A qualification may be against many positions. tblAdqual is the list of qualifications, tblStaffAdqual is the table to register these much in the same way as tblStaffMandTrng. Achieved is a tick box, and competency Check is a date when that check is due.

I appreciate the time you are taken to advise. This thread might become some sort of blog as we develop this - is that ok here (Mods?)?

Matt

 

[mattyn]

Hi Gina

1. Yes those ID fields are Autonumber for each table. I am about to rename them as suggested above, so they are actually unique ID numbers for each table.

2. tblStaff uses Lookup (drop down) for Rank for the staff member (tblRank) and a drop down for the rank of the relief staff member (tblRank_1) for when we have a staff change. The replacement will be nominated three months or so before he actually arrives. JPAN is a position name and number - again I am using drop down menu to select both (the database created tblJPAN_1) automatically.

3. I don't know about the direct link between tblStaff ID and tblAdqualID. (Adqual is for the qualifications each staff member has to attain to do their job and are attached to the staff position).

Does that make it clearer?

Matt