Creating a simple student db in Access (for now)

[DublinDude]

Hi all,

I'm creating a simple relational database in Access. (hoping to create a more elaborate one later in year using a more versatile db).

For now, I just need advice on the structure of tables and how to relate them.

I will have:

Student Table
PPS
Firstname
Lastname
Start Date
Finish Date
D.O.B
Phone
Address

Level
LevelID
Level 5
Level 6

Subjects (Each level has 5 modules)
L5 Module1
L5 Module2
L5 Module3
L5 Module4
L5 Module5

L6 Module1
L6 Module2
L6 Module3
L6 Module4
L6 Module5

Class
ClassID
ClassA
ClassB
ClassC
ClassD
ClassE

Teacher
TeacherID
Name
(what class/level/students they have?)


So basically once it is structured correctly and the relationships are created, I want to create a user interface (launch form) so that people can view all sorts of reports and queries etc based on the info.

I saw a nice similar one where on the launch form there was a series of tabs for each class, when you clicked on one tab you saw the names of all the students in that particular class. If you wanted to view more details on that trainee, you clicked on him/her and pressed a button to view more details.

Any ideas, hints, help etc would be greatly appreciated. I have made a couple attempts but it has fallen flat.

 

[DublinDude]

Where did you get the table structures and fields from?

It doesnt seem quite right to me, particularly the 'Class' and 'Level' tables.
The classID field of the class table doesnt seem like it should be on the same level as the other fields. I think the columns of the class table could be something like "classID" and "className".
Then the table, with data, would look like this:

I may be completely wrong though, i'm struggling to work out what the fields relate to based on their names.[/QUOTE]

Apologies, I should have been clearer.  The structure and fields I just typed up off the top of my head based on what I [I]thought [/I]the structure may be.

Basically, each student is doing either a Level 5 or a Level 6 course.  Both of which contain about 5 different modules/subjects in order to complete the level. 

In the school there are 5 different classrooms each with a different name. I just called them 'class a' etc.  

Thanks for your help :)

 

[DublinDude]

Well, making a load of assumptions about how it might link together, here is my rough structure:

i dont know where teacher links in. I guess it's probably module but it could also be subject, class or even level.

Let me know how many marks I get

edit: just noticed that the module table has a "subjectID" foreign key in it, that obviously shouldnt be there.

Thanks a million for putting that effort in. It looks good. Although do I need a separate subject and subject module table?

 

[DublinDude]

Student Table (all their details here)
|
Level Table (There are only two levels - each level has many trainees)
|
Subjects (This will list all subjects, 5 in one level and 5 in the other)
|
Classroom (Obviously trainees will be in separate classrooms)
|
Teacher


I could be slightly out with my structure but it is kinda what I am thinking. The teacher one isn't entirely necessary.

 

[DublinDude]

You should always try to have any repeating fields in a separate table. Rather than having fields called 'module1', 'module2', etc you should have a module table to hold this.
This way you won't have to change the database structure if you ever add more than 5 modules to a subject and also if you need to rename a module, you only need to do this in one place rather than every subject which includes this module.

Thats a good point. Should each separate module have its own field though?

 

[DublinDude]

No, the 'fields' are the columns of the tables.
Each separate module will have it's own row (record) in the table.

Oh I get you now.

 

[DublinDude]

Just one thing to consider.

Are your students only going to be there for 1 year?

If not how do you plan on retaining details about subjects in previous years.

(Having built a fairly comprehensive system for managing students and their needs e.g. not just subject information, this can get a bit overwhelming quickly)

Access is a wonderful tool for getting something up and running as a proof of concept but really is a pain in the rear with more complex systems.


I am more than happy to share any knowledge that I have.

Thanks a million. Yea I see it as a temporary solution so people can see how things work. I do plan in the future to do something a tad sexier with maybe php and mysql?

But for now, if I can get this up and running in Access. ASAP to keep certain people happy!

Once I have the structure and content, I will need a launch form.

 

[DublinDude]

The easiest way that I find to model a new system is take the key aspect (in your case student) and then map the things around it eg:

We have a Student that is assigned to a Level.

Our Student attends a number of classes which include other Students yes, but one class until that module is complete then they move as a group to next class.

These classes are Level specific and have different Subjects

Each Class is assigned to a Teacher and a Teacher may have Many Classes that they look after. Teacher has one class at a time

Each Subject has different Modules which contains specific pieces of information such as Lesson Plans, Maximum number of students etc. In this case subject and module are the same. My fault for confusion.

This is kind of doing a Use Case Diagram but keeping it really simple so that you can focus on getting pieces working e.g. setting up the student screens and then add in the more complex things later.

Thanks for your help, it's slowly starting to take shape.


You see we have 100 students separated into four groups/classes. Apples, oranges, pears, bananas. So apples will be assigned to a teacher for 5 weeks to complete a module (e.g Maths) in that teachers classroom. Once that is complete they move to next teachers room to do another module. Once all modules at that level are complete they achieve their Cert at that level.