1

I have a vendor that is starting to give me some rather large reports on course completions and usage.

They have two formats for the reports:

  1. If a student is enrolled in a course, they will have an row entry with basic student and course information for each course. Fields would be student name, student id, student email, course name, course status, course completion date.

  2. Each student would have one row. Each course would be represented by a different column. So we would have student name, student id, student email, course name A, course status A, course completion date A, course name B, course status B, course completion date B, course name C, course status C, course completion date C, course name D, course status D, course completion date D...

The good point of report #2 is that it is much smaller than the report #1. However I am having a hard time getting a handle on executing views and jobs based on a variable column table (as courses get added I don't really know).

I need to make some advanced usage reports and wrap up student info with certain courses and completions. Is there a reason why one of the raw formats will either make my job easier or make the database perform queries easier?

Improve this question
1
  • I would say that option #2 is designed for people whom want to use Excel Pivot Tables, etc to examine the data. While option #1, as @Rick James said in his answer below, is for importing into a relational database. There will be no reliable way to use option #2 relationally unless you have some guarantees about the column order for given courses (i.e. they're sorted). Use option #1!
    – Tersosauros
    Mar 27, 2016 at 4:18

1 Answer 1

Reset to default
2

From a database design point of view, #1 is better. But... In either case (#1 or #2) you should not populate your database with only one table.

You should have these tables (at least):

  • Students - one row per student: Student id, name, email
  • Courses - one row per course: Course id (created by you), course name
  • StudentCourses - one row per student-course pair that exists: student id (but no other student info), course id, status, completion date.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.