SQL Database Student Timetable System

Question

I am making a system that displays a students timetable using SQL Server Management Studio and linking it into a Visual Studio program (which is irrelevant to my issue but just adds context), however I am having a major issue with my database tables at the moment and I am in need of some help.

The basic background of my project is that there are 3 tables, Lesson, Student and Subject. The tables are populated with small test sample data sets.

Lesson table:

LessonID    DayOfWk PeriodValue SubjectID   Room    blockLessonIsIn
-------------------------------------------------------------------
1           Mon     2           1           G8      2
2           Mon     3           1           G8      2
3           Mon     4           1           G8      2
4           Mon     5           2           N5      1
5           Tue     1           3           SF5     4
6           Tue     3           2           N7      1
7           Wed     1           3           SF5     4
8           Wed     2           1           H9B     2
9           Wed     5           1           G8      2
10          Thu     1           3           SF4     4
11          Thu     3           2           N7      1
12          Thu     5           3           SF5     4
13          Fri     1           3           SF5     4
14          Fri     2           1           G8      2
15          Fri     3           1           H9B     2
16          Fri     4           2           SP2     1
17          Mon     1           5           H1      1
18          Tue     5           5           H1      1
19          Thu     3           5           H1      1
20          Fri     4           5           H1      1
21          Wed     4           4           S1      3
22          Fri     5           4           S1      3
23          Tue     1           2           N1      4
24          Tue     2           2           N1      4
25          Wed     1           2           N1      4
26          Thu     1           2           N1      4
27          Thu     4           2           N1      4 

Subject table:

SubjectID   Title
---------------------------------
1           Computing
2           Maths
3           Economics
4           Physics
5           Geography

Student table:

UserID  Forname Surname     SchlYear    InOrOut Block1  Block2  Block3  Block4  Pword
----------------------------------------------------------------------------------

1       Jake    Richardson  13          1       2       1       NULL    3       password
2       Russell Penn        13          1       5       1       NULL    2       russpass
3       Xander  Sheppard    13          1       2       1       4       NULL    xander
4       Dan     Bostock     13          1       2       1       4       NULL    pass

These tables are linked to each other via the following:

• Lesson table -> Subject table via SubjectID as foreign key
• Subject table -> Student table via Block1, Block2, Block3 and Block4 with a SubjectID as a foreign key in each, with each block relating to a subject which gives each student a maximum of 4 possible subjects

My aim is to query the tables so that one singular value can be found when the query is ran for a day and period value with an individuals userID entered, thereby finding the lesson that an individual has on that day and period.

The query I have currently is as follows, if trying to find for example Jake Richardson's lesson on Tuesday period 1:

SELECT 
    lesson.SubjectID
FROM 
    lesson_tbl AS lesson
LEFT OUTER JOIN 
    student_tbl AS subject1 ON lesson.SubjectID = subject1.Block1
LEFT OUTER JOIN 
    student_tbl AS subject2 ON lesson.SubjectID = subject2.Block2
LEFT OUTER JOIN 
    student_tbl AS subject3 ON lesson.SubjectID = subject3.Block3
LEFT OUTER JOIN 
    student_tbl AS subject4 ON lesson.SubjectID = subject4.Block4
WHERE 
    lesson.DayOfWk = 'Mon' 
    AND lesson.PeriodValue = '5'  
    AND ((subject1.UserID = '1' AND lesson.blockLessonIsIn = '1') OR 
         (subject2.UserID = '1' AND lesson.blockLessonIsIn = '2') OR 
         (subject3.UserID = '1' AND lesson.blockLessonIsIn = '3') OR 
         (subject4.UserID = '1' AND lesson.blockLessonIsIn = '4'));

And this will successfully return the value 2, ie, Maths.

However, if for example we were to search for Russell Penn's value on Tuesday period 1:

SELECT 
    lesson.SubjectID
FROM 
    lesson_tbl AS lesson
LEFT OUTER JOIN 
    student_tbl AS subject1 ON lesson.SubjectID = subject1.Block1
LEFT OUTER JOIN 
    student_tbl AS subject2 ON lesson.SubjectID = subject2.Block2
LEFT OUTER JOIN 
    student_tbl AS subject3 ON lesson.SubjectID = subject3.Block3
LEFT OUTER JOIN 
    student_tbl AS subject4 ON lesson.SubjectID = subject4.Block4
WHERE 
    lesson.DayOfWk = 'Tue' 
    AND lesson.PeriodValue = '1'  
    AND ((subject1.UserID = '2' AND lesson.blockLessonIsIn = '1') OR 
         (subject2.UserID = '2' AND lesson.blockLessonIsIn = '2') OR 
         (subject3.UserID = '2' AND lesson.blockLessonIsIn = '3') OR 
         (subject4.UserID = '2' AND lesson.blockLessonIsIn = '4'));

Then the return values are 3 values of 2 ie 3 values of maths, whereas it should only return 1 value of maths as there is only 1 maths value on Tuesday at period 1 within the blocking constrains I have added.

I am aware that maths crosses over both block 1 and block 4, but this is the idea, and the blockLessonIsIn is aimed at remedying the fact that it should only select the lessons that relate to the specific block that maths is in.

Is there something crucially wrong with my database design or is it a glaring error that I have stupidly missed. The database will be moulded into my program that has already been created to pass the values such as DayOfWk and PeriodValue so this is simply the only thing holding me back, but it has me totally stumped as to where 3 values of Maths are being selected.

Please note there are columns that are not needed for the timetable, so if they have not been quoted (ie Room in lesson table) ignore them.

If you have any more questions about my program or anything I may have forgotten to mention, or need me to go more in detail about what I am trying to achieve with certain lines of code, please do ask as this is the major stumbling block in my program.

Thanks for any help any of you may be able to provide!

Answer 1

I think you may have some data corruption in your Lesson table. After reading your question and what you want to be able to do, it seems reasonable to me to think that each (Day, Period) pair in Lesson should be unique. For example, if a student is in Wednesday Period 1, then we should be able to definitely say that he is in Math. This, however, doesn't seem to be the case. If we run the following query,

select dayofwk, periodValue, COUNT(*)
    from lesson_tbl
    group by dayofwk, periodValue
    having COUNT(*) > 1

we get the following results

+ ------- + ----------- + ----- +
| dayofwk | periodValue | count |
+ ------- + ----------- + ----- +
| thu     | 1           | 2     |
| tue     | 1           | 2     |
| wed     | 1           | 2     |
| thu     | 3           | 2     |
| fri     | 4           | 2     |
+ ------- + ----------- + ----- +

which tell us that these (day,period) combinations are not unique. If a student is in Wednesday Period 1, then we can't determine if he is in Math or Econ.

What to do from here

(1) If you do not believe that a (day, period) combination should be unique in the lesson table, then what you want is impossible. You will not be able to find "one singular value" for a (user, day, period).

(2) If you believe that a (day,period) combination must be unique in the Lesson table, then fix the corrupted data. Then build the following query

declare @dayQuery varchar(3) = 'tue'
declare @PeriodQuery int = 1
declare @userQuery int = 1

select *
    from lesson_tbl L
    inner join student_tbl U
        on case L.blockLessonIsIn when 1 then U.Block1
                                  when 2 then U.Block2
                                  when 3 then U.Block3
                                  when 4 then U.Block4
           end = L.subjectID
    where L.dayofwk = @dayQuery
        and L.periodValue = @PeriodQuery
        and U.userID = @userQuery