-1

I'm pretty new to sql, so forgive me if there's a simple answer. I'm setting up a scheduling database for lessons in a school. Each class has between 3 and 9 students. I was originally planning to use two tables with a JOIN as follows:

Table 1: Class Day-of-week Start-time End-time Student1 ... Student9

JOIN

Table 2: Date Class Teacher

This would give a list of all the classes by date. The problem is that if a new student joins the class, updating Table 1 would add that student to all the previous rows although the student didn't attend those classes. Is there a better (and simple) way to set up the schedule which preserves the data in past rows?

*edit It's not so much a problem with the code, but more of a data organization problem. (I've included the code I'm using below).

Table 1: Class Day-of-week Start-time End-time S1 S2 S3... S9

       English  Mon        8:00       9:00      Jon Sue  Null....Null

Table 2: Date Class Teacher

      2019-01-07  English   ProfX   
      2019-01-14  English   ProfY
      2019-01-21  English   ProfZ

Then the JOIN looks like

Schedule 

2019-01-07  English   ProfX   Mon        8:00       9:00      Jon Sue  Null....Null
2019-01-14  English   ProfY   Mon        8:00       9:00      Jon Sue  Null....Null
2019-01-21  English   ProfZ   Mon        8:00       9:00      Jon Sue  Null....Null

Now assume a new student (Tim) joins for the next class. If I update Table 1 with the new student, all of the previous rows would include Tim, but he wasn't in the class for 2019-01-07 or 2019-01-14, so my table would look like this.

2019-01-07  English   ProfX   Mon        8:00       9:00      Jon Sue Tim....Null
2019-01-14  English   ProfY   Mon        8:00       9:00      Jon Sue Tim....Null
2019-01-21  English   ProfZ   Mon        8:00       9:00      Jon Sue Tim....Null

When I only want this:

2019-01-07  English   ProfX   Mon        8:00       9:00      Jon Sue  Null....Null
2019-01-14  English   ProfY   Mon        8:00       9:00      Jon Sue  Null....Null
2019-01-21  English   ProfZ   Mon        8:00       9:00      Jon Sue Tim....Null

////code////

Create VIEW 'schedule' SELECT Table1.*, Table2.*
FROM Table1
INNER JOIN Table2
ON (Table1.class = Table2.class);
sticky bit
  • 36,626
  • 12
  • 31
  • 42
germlab
  • 1
  • 1
  • If you could share the sample data, that would be helpful to solve it. – Avi Aug 17 '19 at 02:08
  • It's a simple VIEW and JOIN. I added the code and a little more clarification to the problem. – germlab Aug 17 '19 at 02:35
  • Are you adding Tim to the same column with Jon Sue as a comma separated value? Or adding Tim to a new column? anyway non of this is a good practice. – Isuri Subasinghe Aug 17 '19 at 02:58
  • @IMS Student1 through Student9 are 9 different columns. This approach seems simple and appropriate for my application. Please recommend a better approach if you know one. – germlab Aug 17 '19 at 03:19
  • You can have a separate Student table to keep student details (Student id, Student Name). And in Table 1 you can have ID, Day-of-week, Start-time, End-time. And you can have a mapping table to map student id to Table 1 ID. – Isuri Subasinghe Aug 17 '19 at 05:29

1 Answers1

0

It seems like the easiest approach is to schedule a daily copy of the final table without rewriting existing rows.

mySQL daily backup from one table to another

MySQL Event Scheduler on a specific time everyday

germlab
  • 1
  • 1