Creating a simple student db in Access (for now)

DublinDude

DublinDude

DublinDude

Associate
Joined
1 Oct 2007
Posts
325
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.
 
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:
Code: 
classID     |    className
--------------------------------
    1       |        A
    2       |        B
    3       |        C
    4       |        D
    5       |        E

Same with Level table:

Code: 
levelID     |    levelNumber
--------------------------------
    1       |        5
    2       |        6

I may be completely wrong though, i'm struggling to work out what the fields relate to based on their names.
 
Agree with the above, it's difficult to work out what you mean with the fields in the class/subject/level tables.

As a rough idea, I'd suggest you would need some foreign keys and/or junction tables to connect your relationships.

For example, if you assume that a teacher can teach many classes and a class can be taught by multiple teachers, you would want a teacher_class table containing teacher id and a class id - each row in that table would link a teacher to a class.
If a class can only be taught by one teacher then you might instead consider adding the teacher id as a foreign key in the class table.

The same applies to other relationships - it's difficult to provide any more help without knowing what pieces of information you need to store about each entity.
 
touch said:
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:
Code: 
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 :)
Click to expand...
 
Well, making a load of assumptions about how it might link together, here is my rough structure:
eeamnP9.png


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.
 
touch said:
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.
Click to expand...

Thanks a million for putting that effort in. It looks good. Although do I need a separate subject and subject module table?
 
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.
 
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.
 
touch said:
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.
Click to expand...

Thats a good point. Should each separate module have its own field though?
 
No, the 'fields' are the columns of the tables.
Each separate module will have it's own row (record) in the table.
 
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.
 
davetherave2 said:
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.
Click to expand...

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

Once I have the structure and content, I will need a launch form.
 
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

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.

Each Subject has different Modules which contains specific pieces of information such as Lesson Plans, Maximum number of students etc

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.


My current system has gone through three revision so far and I am still not happy with how some of the data is being stored and so I suspect version 4 is not far away now.

If you are familiar with Object Orientated principles then this will help you try to model your problem better. Think of things as objects rather than tables and then how these relate to each other. I find using stick people brilliant for trying to figure out how things interact with each other.
 
davetherave2 said:
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.
Click to expand...

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.
 
Last edited:
Glad what I did helped.

As you can probably tell I like to work at the high level first. Then we can get down to specific fields and properties later.

That way I don't get too bogged down in the detail until I need to.

Again glad to see you are making some headway.
 
You must log in or register to reply here.
Back
Top Bottom