Creating a simple student db in Access (for now)

py2006

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.

bpmurray

I think you've made incorrect assumptions about how the data should be organised that make this list confusing: what are the Level, Class and Module tables and what do they contain? It's much easier if you can list the *things* in the system before attempting to create a database. For example (this is probably wrong) but the structure might be:

• A course contains n students.
• A course contains 5 modules.
• A course can be level 5 or level 6.
• A module can be for either level 5 or level 6 or both.
• A class contains 1 teacher and n students and is associated with a module

If this is the case, the database schema would be quite different from what you have proposed.

py2006

bpmurray wrote: »

I think you've made incorrect assumptions about how the data should be organised that make this list confusing: what are the Level, Class and Module tables and what do they contain? It's much easier if you can list the *things* in the system before attempting to create a database. For example (this is probably wrong) but the structure might be:

• A course contains n students.
• A course contains 5 modules.
• A course can be level 5 or level 6.
• A module can be for either level 5 or level 6 or both.
• A class contains 1 teacher and n students and is associated with a module

If this is the case, the database schema would be quite different from what you have proposed.

You are actually not far off the mark there I think. Except the modules are are not shared across both levels.

Tom Dunne

Another thing I always do is ask what if the data needs to change in the future.

What if you wanted to add a Level 7 course? What if you wanted to add another class/module/etc.?

Your schema needs to cater for this. Not that you necessarily will add the above data, but the design should facilitate it.

Aswerty

You should look into the concept of junction tables for assigning students to subjects. Junction tables are essentially extra tables that allow you to link multiple rows in one table to multiple rows in another table. So you could link any number of students to any number of subjects. The limit of 5 subjects per student would be better placed in you application code as opposed to being hard wired into the database.

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

Each subject would have a teacher (or using junction tables; multiple teachers). Because subjects are linked to students, students are linked to classes, and subjects are linked to levels you should be able to query what teach has what class/level/students just using the query lanugage and not explicitly store this data in the teacher table.

py2006

Tom Dunne wrote: »

Another thing I always do is ask what if the data needs to change in the future.

What if you wanted to add a Level 7 course? What if you wanted to add another class/module/etc.?

Your schema needs to cater for this. Not that you necessarily will add the above data, but the design should facilitate it.

Good point. The levels prob won't change but the modules may.

py2006

Aswerty wrote: »

You should look into the concept of junction tables for assigning students to subjects. Junction tables are essentially extra tables that allow you to link multiple rows in one table to multiple rows in another table. So you could link any number of students to any number of subjects. The limit of 5 subjects per student would be better placed in you application code as opposed to being hard wired into the database.



Each subject would have a teacher (or using junction tables; multiple teachers). Because subjects are linked to students, students are linked to classes, and subjects are linked to levels you should be able to query what teach has what class/level/students just using the query lanugage and not explicitly store this data in the teacher table.

Ok, I will have to look into that thanks. Sounds a bit confusing at the moment.

Each student is currently doing one subject but I would like it to say which module/s they have done to date if that makes sense?


Edit:
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

amen

I'm surprised no one else has mentioned this but it appears that you are using the Students PPS as a primary key on the Student Table. This is not a good idea because

1: Its a natural key. If there is a mistake in entered PPS all your relationships/data in the other tables would have to be updated
2: Its a unique personally identifiable piece of information. If you need to ask this it should be stored in an encrypted field in the database.

You would be better off having a StudentID field which is a uniuqe integer.