Caucus 5.1 Item and Response Schema

Charles Roth, 18 April 2006

I. Introduction
Caucus version 5.1 moves all of the item and response data out of the old (stable, fast, but difficult to extend) flat-file database, into MySQL tables.  (Eventually other RDBMS's may be used, so we use as little MySQL-specific functionality as possible.)  This document is an overview of the schema for the items and responses.  It is assumed that the reader is familiar with both MySQL and the general use and design of Caucus.

II. Assumptions

III. Table Details
Fields shown without a type are usually varchar(240).  "Medium" means mediumtext, i.e. text up to 16MB.  "Time" means a full date-and-time stamp.

items One row per item; contains responses
id int autoincrement (PK)
userid item creator (FK: user_info)
title item title text
lastresp int response number of highest undeleted response
frozen tinyint 1=frozen, 0=default
qkey int (FK: quiz)  for items of type 'quiz', else NULL

conf_has_items Many-to-many, items to conferences
cnum int (FK: confs)
items_id int (FK: items)
item0 int item number, level 0 (n)
item1 int ... level 1 (x.n)
item2 int ... level 2 (x.x.n)
item3 int
item4 int
item5 int
hidden tinyint 1=hidden, default=0
retired tinyint 1=retired, default=0
items_parent int parent item, for breakouts
(FK: resps)
resps_parent int parent response, for breakouts
Note that an item that is shared across multiple conferences may be "hidden" or "retired" in one conference, but not in another.

resps One row per response; text and properties of each
rnum int response number (PK)
items_id int owning item (PK)  (FK: items)
userid author (FK: user_info)
text medium text of response
time time date/time written
prop int old-style text "property"
bits int old-style "bits"
copy_rnum int original response copied from
(FK: resps)
copy_item int original item copied from
copy_cnum int original conference copied from
copier userid of copier (FK: user_info)
copy_time time date/time copied
copy_show tinyint 1=show, 0=don't show "copied" notice
deleted tinyint 1=deleted, 0=normal

resps_seen How much has each user seen of each item?
userid (PK)  (FK: user_info)
items_id int (PK)  (FK: items)
seen int how many responses in the item has the user seen?
forgot tinyint 1=forgot

user_in_conf When were users in what conferences?
userid (PK)  (FK: user_info)
cnum int (PK)  (FK: confs)
lastin time date/time user was "last in" this conference