Storing Multiple choice answers of a Module Options Form in a Microsoft Access Database

Question

I currently am trying to work out how to create a relational database for a University Module Options form. First you enter your student ID, Name, Surname and select your degree programme e.g. Human Resource Management then choose multiple modules through the form using checkboxes until the total credits for each semester for each programme is chosen.

However in choosing multiple modules and in being a relational database design i am unsure of how to store these multiple answers in the Student Options table as shown below.

I currently have the tables of

Table: Student Field Names: Student ID (primary key) Name Surname

Table: Programme Programme (primary key) Semester 1 Credits (different programmes allow different amount of credits ) Semester 2 Credits (different programmes allow different amount of credits )

Table: Module Module ID (primary key) Module Name Credits Prerequisite

The last table is one i am struggling with as after the modules are chosen from the form they will be stored in this table and currently have this...

Table: Student Options Student ID (primary key) Programme (link to programme table)

However i am unsure what fields to have to store them in without being too cluttered and still having a link to the modules table as shown below which are all stored individually.

Does my modules table need to have a relationship link to the student options table to be a relational database ?

How would i store the multiple modules chosen into the student options form?

Thanks

Answer 1

As for your core problem, I think the database design outlined below should be sufficient:

You should not store both, the modules a student selects and the programme he is enrolled in in the same table. Instead do it like outlined above.

• The programme a student is enrolled in should just be a foreign key in the student table, therefore giving you a one-to-many-relation (This is a crucial point though, because this means any one student can only be enrolled in one programme! If your database has to be able to have one student be enrolled in more than one programme, you need a many-to-many-relation there too.).
• The modules should be related to a student via a middle table (I called it StudentModule in this case), therefore giving you the desired many-to-many-relation. What you now have to do of course is check via code, if the module isn't already selected by the student (as well as all the other small and big details there are...). But this you would have to do with any database design as far as I know.
• As you can see, I also inserted a middle table for the module to programme relation. This is because I assume that one module is eligible for multiple programmes. By relating modules to programmes in this way, you can then check for stuff like "can this student elect this module", ...