Hi Grandfather,

Sorry for the long delay in making this post ... family duties on Saturday :)

As you know, I'm sure, it's important to separate data from the logic in your Perl code. Even more so, you should definitely keep logic out of your database!

Using a column called IsTeacher is a neat trick that solves your immediate problem, but effectively limits you to two roles. As soon as you want to add a role for Teacher's Assistant or anything else, you will have to change your database table structure and probably refactor a bunch of your code. Obviously you want to avoid doing each of those things! You should set up your database so you can add new functionality to your app just by adding records to the DB.

When designing a database schema you should anticipate new requirements being introduced in the future, since they always have been :-) A couple of things to keep in mind are:

• Keep your "data structure" in one set of tables and your "data instances" in another set. For example, in a common setup for user session management you would have one table called Role that defines the roles available and their properties, indexed by a key, and then another table called UserRoles that would hold the information for each user-role combo. All that table needs to contain is the ID for the User and the ID for the Role. Setting up like this avoids problems when suddenly you want a user to have more than one role. You shouldn't have to add a column to the Users table for that.
• Try to "atomize" your data as much as possible. Rather than your SessPeople table, have a Person table with all info on the Person, a Session table with all info on the Session, and then an very small PersonSessions table that should contain just the ID of the Person and the ID of the Session. If you need to know about a "session-person", use a JOIN in your SQL.

I suggest a schema like the following:

  CREATE TABLE Session (
    SessID   INTEGER PRIMARY KEY AUTOINCREMENT,
    DoneDate TEXT,
    Topic    TEXT
  )

  CREATE TABLE Person (
    PersId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )

  CREATE TABLE Role (
    RoleId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )

  CREATE TABLE PersonRoles (
    PersID INTEGER,
    RoleID INTEGER,
    PRIMARY KEY(PersID, RoleID)
  )


  CREATE TABLE PersonSessions (
    SessID INTEGER,
    PersID INTEGER,
    PRIMARY KEY(SessID, PersID)
  )
[download]

And then in your code:

sub getActiveSessions {
  my ($self, @ids) = @_;
  my $idPlaces = (join ', ', ('?') x @ids) || '';

  return $self->{'dbh'}->selectall_arrayref( <<SQL, {}, @ids );

  SELECT p.Name, r.Name, s.Topic, s.DoneDate
  FROM Person p
  JOIN PersonRoles pr ON pr.PersID = p.PersID
  JOIN Role r ON r.RoleID = pr.RoleID
  JOIN PersonSessions ps ON ps.PersID = p.PersID
  JOIN Session s ON s.SessID = ps.SessID
  WHERE p.PersID IN ($idPlaces)
  AND s.DoneDate = '2015-06-27'
SQL
}

say Dumper( $self->getActiveSessions(1..7) );
[download]

This outputs:

$VAR1 = [
          [
            'Doug',
            'Learner',
            'Fun with JSON',
            '2015-06-27'
          ],
          [
            'Bert',
            'Teacher',
            'Fun with Origami',
            '2015-06-27'
          ],
          [
            'Greg',
            'Learner',
            'Fun with Origami',
            '2015-06-27'
          ],
          [
            'Fred',
            'Learner',
            'Fun with Origami',
            '2015-06-27'
          ],
          [
            'Fred',
            'Learner',
            'Fun with JSON',
            '2015-06-27'
          ],
          [
            'Carl',
            'Teacher',
            'Fun with JSON',
            '2015-06-27'
          ]
        ];
[download]

Complete demo script here:

use strict;
use warnings;
use DBI;
use feature qw/ say /;
use Data::Dumper;

my $dbh = DBI->connect('dbi:SQLite:monks.sqlite','','', {RaiseError =>
+ 1});

my $self = bless { dbh => $dbh };

$dbh->do(<<SQL);
  CREATE TABLE Session (
    SessID   INTEGER PRIMARY KEY AUTOINCREMENT,
    DoneDate TEXT,
    Topic    TEXT
  )
SQL

$dbh->do(<<SQL);
  CREATE TABLE Person (
    PersId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )
SQL

$dbh->do(<<SQL);
  CREATE TABLE Role (
    RoleId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )
SQL

$dbh->do(<<SQL); 
  CREATE TABLE PersonRoles (
    PersID INTEGER,
    RoleID INTEGER,
    PRIMARY KEY(PersID, RoleID)
  )
SQL

$dbh->do(<<SQL);
  CREATE TABLE PersonSessions (
    SessID INTEGER,
    PersID INTEGER,
    PRIMARY KEY(SessID, PersID)
  )
SQL

# make some roles
for ( qw/ Teacher Learner /) {
  $dbh->do( <<SQL, {}, $_ );
INSERT INTO Role (Name) VALUES (?) 
SQL
}

# make some sessions
my %sessions = ( 
  'Fun with Perl'    => '2015-06-20',
  'Fun with SQL'     => '2015-06-20',
  'Fun with JSON'    => '2015-06-27',
  'Fun with Origami' => '2015-06-27',
);

while (my ($topic, $done_date) = each %sessions) {
  $dbh->do( <<SQL, {}, $topic, $done_date );
INSERT INTO Session (Topic, DoneDate) VALUES (?,?)
SQL
}

# make some people
my %people = (
  'Adam' => { role => 1, sessions => [1,4] },
  'Bert' => { role => 1, sessions => [2] },
  'Carl' => { role => 1, sessions => [3] },
  'Doug' => { role => 2, sessions => [1,3] },
  'Eric' => { role => 2, sessions => [1,4] },
  'Fred' => { role => 2, sessions => [2,3] },
  'Greg' => { role => 2, sessions => [2,4] },
);

while ( my ($pers_name, $properties) = each %people ) {

  $dbh->do( <<SQL, {}, $pers_name );
    INSERT INTO Person (Name) VALUES (?)
SQL

  my $pers_id = $self->{dbh}->last_insert_id(undef, undef, undef, unde
+f);

  $dbh->do( <<SQL, {}, $pers_id, $properties->{'role'} );
    INSERT INTO PersonRoles (PersID, RoleID) VALUES (?,?)
SQL

  for (@{ $properties->{'sessions'} }) {

    $dbh->do( <<SQL, {}, $_, $pers_id );
      INSERT INTO PersonSessions (SessID, PersID) VALUES (?,?)
SQL
  }
}

sub getActiveSessions {
  my ($self, @ids) = @_;
  my $idPlaces = (join ', ', ('?') x @ids) || '';

  return $self->{'dbh'}->selectall_arrayref( <<SQL, {}, @ids );

  SELECT p.Name, r.Name, s.Topic, s.DoneDate
  FROM Person p
  JOIN PersonRoles pr ON pr.PersID = p.PersID
  JOIN Role r ON r.RoleID = pr.RoleID
  JOIN PersonSessions ps ON ps.PersID = p.PersID
  JOIN Session s ON s.SessID = ps.SessID
  WHERE p.PersID IN ($idPlaces)
  AND s.DoneDate = '2015-06-27'
SQL
}


say Dumper( $self->getActiveSessions(1..7) );
[download]

Hope this helps!