Caucus 5.1 Item and Response SchemaCharles Roth, 18 April 2006
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.
- 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
Note that an item that is shared across multiple conferences may be "hidden" or "retired" in one conference, but not in another.
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 resps_parent int parent response, for breakouts
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 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