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

• An Item is a single entry in the items table.  It is a "container" for a linear chain (thread) of responses.  Each item is independent of any conferences, but typically appears in one (or more conferences). 
• Items are uniquely (internally) identified by items.id, but when an item appears in a conference, it has a "number" (a label), such as "5", "17.2", "3.2.6", etc.  "Dotted" item numbers refer to "break-out" items in a hierarchy, i.e. 3.2.6 is the sixth breakout from the second breakout from item number 3.
• Conf_has_items is a many-to-many relationship that links items to conferences.  Each row declares that a particular item appears in a particular conference, and specifies the item number used to label that item in that conference.
• Resps contains the actual responses (aka comments or postings).  Each response belongs to one, and only one, item.  The primary key for a response is the combination of its response number (label), and the id of its containing item.  Thus the first response in each item is always number 0, the second is number 1, and so forth.  If responses are deleted, the response numbers do not change (shift).
• Resps_seen tracks which users have seen which (how many) responses on which items.  This acts like a high-water mark, i.e. if resps_seen.seen is 17, then response numbers 0 through 16 are "seen", and any higher-numbered responses are "new".

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