Extending a old DB - clever experts required for advice

[Gregory.Spencer]

Summary (still get coffee) explanation:

I have added a new "Sessions" table to a DB because the original design
could not handle a scenario where an entity "class" had a number of
sessions. Originally there was just one class which would be attended
by people, as they got more popular they had to split out classes over
different sessions because too many people would attend a class.
However, the database used to store what people attended what class can
only store just that. There was no way of knowing who attended a
particular session that belongs to class.

Hope that makes sense so far.

OK so the first step was to create a new table called sessions which
would also store what sessions people attended. As there was a table
called Classlink which would store what people attended each class, if
we are creating a session table, we also need a SessionLink table so we
can store what people attended what sessions (I assume this is a good
way to do it).

Why do we still need the ClassLink table you may ask? Because in some
scenarios, the class may have been sorted people have been recruited
for it, but the actual sessions have not yet been defined, so you still
need to know who has been recruited for the Class. (Also there is the
legacy aspect of how the PHP and My Sql DB used to work, if I change
things with the old code I will have to do a lot more checking)

So I think I have the option:
a) When a person attends a class, store this fact in both the new
SessionLink and the ClassLink table. Therefore when you want to see who
attended a class, all you have to do is look in the ClassLink table and
you will have the full list, without then having to look into the
Sessionlink table to see who attended sessions associated with that
class.
b) Store a members attendance only in the Classlink table if it is not
determined which session a person is attending. When the person is
moved to a session, the row will be removed from the classlink table
into the session link to show what specific session the person
attended. This means to work what people attended a class, not only do
you have to check the class table for attendees, you then have to find
the session numbers associated with the class and then check the
sessionlink table for sessions associated with those classes and then
get them as well.
I am a bit concerned that SQL involved here might make things a bit
confusing.
This method does also has the benefit of all the current data in DB was
still be compatible with current code.

c) As I was writing this another option occurred to me. I could extend
the the current classlink table to have a new fields, sessionID and
isItaSession (not strictly necessary because a Null value in session ID
could indicate the row does not indicate a session).
This way when a person is moved into a specific session for a class, a
value is just filled into sessionID field. In fact the primary key on
the SessionDetails table could be a combination of the sessionID and
ClassID fields.
This means when I want to see who attended a class, I am only searching
one table (classlink using the original class_id field), if I Want to
see who attended a particular session for a class, I can search the
classlink table for the classID combined with the session ID and I will
get my list.
So which approach is best? This is probably all a bit confusing with
the information I have given so if you have made sense of what I have
written the I appreciate it. If I have managed to make myself clear in
a succinct way then I would welcome any DB designers opinion on this
matter on what is the best approach and some ideas on what sort of SQL
would be needed if I go for option b

Kind regards and thank you if you even read this far.

Dave