CREATE TABLE Sessions (
    SessId INTEGER -- oops.. cannot be PRIMARY KEY AUTOINCREMENT,
    PersonId INTEGER,
    PersonRole TEXT,
    DoneDate TEXT,
    Topic TEXT
    )
[download]

Further update: since session-id can't be unique in this approach, you might want another (unique) id field, or two tables: one for sessions, one for participants in sessions.

Expanding on graff's idea - to normalize this schema, you should have a the 'session' table contain unique entries, and separate out and create a new 'session_member' table that contains (session_id, member_id, role) where member id can be a student, or teacher - whose role is specified by 'role'. This table can have aidditional info, like enrollment date, and grades.

        "Despite my privileged upbringing, I'm actually quite well-balanced. I have a chip on both shoulders."         - John Nash

Yup, that's what I did. :-)

Perl is the programming world's equivalent of English

Thanks! Adding a table does the trick at the cost of some reworking of adds and fetches. Is this the SQL equivalent of "Add another level of indirection"?

Reworked sample is:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=xr.SQLite');

$dbh->do(<<SQL);
CREATE TABLE Sessions (
    SessId INTEGER PRIMARY KEY AUTOINCREMENT,
    DoneDate TEXT,
    Topic TEXT
    )
SQL
$dbh->do(<<SQL);
CREATE TABLE SessPeople (
    SessId INTEGER,
    PersonId INTEGER,
    IsTeacher INTEGER
    )
SQL


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

$self->addSession(1, 2, 'Perl is fun',                   '2015-06-27')
+;
$self->addSession(3, 4, 'SQL is fun',                    '2015-06-27')
+;
$self->addSession(3, 5, 'SQL for fun and profit',        '2015-06-20')
+;
$self->addSession(4, 1, 'PerlMonks is great',            '2015-06-20')
+;
$self->addSession(2, 6, 'PerlMonks for fun and proffit', '2015-06-20')
+;

my $active = $self->getActiveSessions(1, 2);

print "$_->[1], $_->[2]: '$_->[3]'\n" for @$active;


sub addSession {
    my ($self, $teacher, $learner, $topic) = @_;

    $self->{dbh}->do(<<SQL, {}, $topic);
INSERT INTO Sessions (Topic, DoneDate)
    VALUES (?, '')
SQL

    my $sessId = $self->{dbh}->last_insert_id(undef, undef, undef, und
+ef);

    $self->{dbh}->do(<<SQL, {}, $sessId, $teacher);
INSERT INTO SessPeople (SessId, PersonId, IsTeacher)
    VALUES (?, ?, 1)
SQL
    $self->{dbh}->do(<<SQL, {}, $sessId, $learner);
INSERT INTO SessPeople (SessId, PersonId, IsTeacher)
    VALUES (?, ?, 0)
SQL
}


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

    return $self->{dbh}->selectall_arrayref(<<SQL, {}, @ids);
SELECT DISTINCT s.SessId, t.PersonId, l.PersonId, s.Topic FROM Session
+s s
    JOIN SessPeople t ON t.SessId == s.SessId AND t.IsTeacher
    JOIN SessPeople l ON l.SessId == s.SessId AND NOT l.IsTeacher
    JOIN SessPeople p ON p.SessId == s.SessId
    WHERE p.PersonId IN ($idPlaces)
SQL
}
[download]

Perl is the programming world's equivalent of English

I'm not sure what you're trying to do here, but whatever it is, this is overcomplicating it.

Aside from that, you're using dynamic SQL and a flag to determine if the person is a teacher or not. Use a Role id instead. That's what you want. The flag is a trick, and, it may bite you in the future.

Can more than one person be in the same session? Why does it matter if the person is a teacher or student?

It looks like you're inserting each person twice, once as a teacher and once as a student. If so, it can be done in one query:

INSERT INTO SessPeople (SessId, PersonId, IsTeacher) SELECT ?, ? 0 UNION ALL SELECT ?, ? 1;

The second query is daunting. I have no idea what you're trying to do. If the original query (topic starting post) table was used, you can check via many ways. The easiest is to connect the two columns via UNION ALL:

WITH Session2 AS(SELECT AS (SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions UNION ALL SELECT SessId, LearnerId, DoneDate, Topic FROM Sessions) SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions2 WHERE TeacherId IN ();

The rewritten query here might be more complicated, but it does't look right. Could you explain a little more clearly what you;re trying to achieve? I have a feeling the solution is considerably simpler.

PersonId INTEGER,
PersonRole TEXT,

The problem with that is PersonRole defines what PersonId is. Meaning, PersonId is two different data entities in one column. This breaks normalization.

Well, unless the entity is who the person is, and what role they play is merely an attribute for this table. If that is not the case, it should be two separate columns.

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:

Hope this helps!

In fact the real code uses session and person tables as you suggest. It is exceedingly unlikely that there will ever be more than two roles. People don't have a permanent role, they are either a teacher or a learner in each session. The SessPeople table is what you suggest as a PersonSessions table.

As an aside, additional roles could easily be added if really needed by re-purposing the IsTeacher column as a RoleId column to be a foreign key for a Role table and setting Learner = 0, Teacher = 1 in the new table.

Perl is the programming world's equivalent of English

What duplication of id list?

SessionID as a number? Why not a GUID