Database model advice

[suarve]

Hi,

I need to design part of a data model that allows me to store user selection on courses and I have become slightly stuck. Basically, I need to do a list of all available courses (can do this bit). Each course is made up of several modules (that make up a course - like a university degree with many modules studied) - that can either be compulsory or an option.

My initial thought was to ditch a table having a list of courses and have a table that lists modules. A table would link to this showing the make up of a course E.g.

Modules
*Module ID
Name
Start Date
Etc.

Courses
*Course IDCourse title
Core Module 1
Core Module 2
Option 1
Option 2

(all the options would be FKs to the Modules table)

I'm sure there must be a better way as that database currently isn't in 1NF

I'm really struggling how to design the tables for the latter, can anyone help?

Many Thanks

 

[Andric]

course
-------
course_id
course_name
course-teacher
etc...

module
-------
module_id
course_id
module_is_core
module_name
etc...

select course.course_name, module.module_name, module.module_is_core
from course inner join module on course.course_id = module.course_id

courseA, moduleA, true
courseA, moduleB, true
courseA. moduleC ....


basically module 'coreness' belongs to module not course. you coudl also break out module types into a further table but arguably could over normalise

 

[Bakedgoods]

Andric has got it here, he's normalised the database as it should be

 

[suarve]

Excellent stuff! Many thanks for the reply.

EDIT: this is probably me being stupid, how would i list the Course with its modules? E.g

Course ID
- Module ID 1
- Module ID 2
- Module ID 3
- opt 1
-opt 2
etc

 

[Stretch]

Tbh the tables are still not fully normalised due to the fact a module could be applicable to many courses. You really need a join table between the two.

Something like this would be much better

course
-------
course_id
course_name
course-teacher
etc...

course_module
-------
course_id
module_id
module_is_core

module
-------
module_id
module_name
etc...

As for listing a course with it's modules it really depend on how you want to present the data.

If you want it like below you will need to use two separate select statements

Course Name
- Module 1, Core
- Module 2, Not Core
- Module 3, Core
etc...

SELECT * FROM course WHERE course_id = 1000;

SELECT * FROM course_module
JOIN module
ON course_module.module_id = module.module_id
WHERE course_module.course_id = 1000;

Or you could just do one big select statement, which would give you something like this

Course Name, Module 1, Core
Course Name, Module 2, Not Core
Course Name, Module 3, Core
etc...

SELECT * FROM course
JOIN course_module
ON course.course_id = course_module.course_id
JOIN module
ON course_module.module_id = module.module_id
WHERE course.course_id = 1000;

 

[daz]

Tbh the tables are still not fully normalised due to the fact a module could be applicable to many courses. You really need a join table between the two.

Something like this would be much better

course
-------
course_id
course_name
course-teacher
etc...

course_module
-------
course_id
module_id
module_is_core

module
-------
module_id
module_name
etc...

"module_is_core" is an attribute of module so should probably be in the module table.

 

[Confused]

"module_is_core" is an attribute of module so should probably be in the module table.

But then could a module be a core module in one course, but not in another course?

 

[Stretch]

But then could a module be a core module in one course, but not in another course?

Got it in one

 

[daz]

But then could a module be a core module in one course, but not in another course?

Ah ha!