# # Table layout for ETA tables in mysql # # Authors: Henrik Frystyk Nielsen, Eric Prud'hommeaux # # $Id: mysql-tables.batch,v 1.11 1999/06/08 21:03:32 frystyk Exp $ # TABLE: uris # ----------- # The uris table is a mapping from any URI into an internal ID which we # use as reference in other tables. There is exactly one entry for each # uri referenced in the database. # # If the URI is relative then this means that it is a reference to a # report (see the reports table). If the URI is absolute then it is an # external reference. # # We optimize for the case that they are shorter than 255 chars but # allow for longer strings if need be. create table uris ( id int unsigned not null auto_increment, # unique ID for all documents we know uri varchar(255) binary not null, # For all URIs luri blob, # Extra for URIs of more than 255 chars primary key (id), unique (uri), index uri_idx (uri(32)) ); # TABLE: schemas # -------------- # Entries which refer to %, for example %categories, refer # to a configurable enumeration specified by the type of forum we are # describing. In order to make the database selfdescribing, we create # a table consiting of the names of the definitions used to configure # the database. This is essentially the name space of the issues! create table schemas ( id int unsigned not null auto_increment, # unique ID for all schemas we know categories int unsigned not null, # URI of definition of categories priorities int unsigned not null, # URI of definition of priorities states int unsigned not null, # URI of definition of states event_status int unsigned not null, # URI of definition of states contact int unsigned not null, # Contact creating schema, see contacts table description varchar(255), # Description of fora using these schemas primary key (id) ); # TABLE: forums # ------------- # Issues are organized in fora - each forum has has a specific schema which # describes the categories, priorities, etc. for that forum. Fora can be # organized in hierarchies but need not inherit anything from their parent # forum. create table forums ( id int unsigned not null auto_increment, # unique ID for all fora we know path varchar(255) binary, # path of this forum schema int unsigned not null, # schema used in this forum, see schemas table contact int unsigned not null, # Contact creating forum, see contacts table description varchar(255), # Description of this forum primary key (id) ); # TABLE: issues # ------------- # An issue is a document, bug, request, annotation, discussion item, or anything # else that we are dealing with in any particular instance of the # database. # # We maintain one entry for each document. A document is uniquely # identified by the id which in fact is a relative uri registered in the # uris table. External documents are characterized by having absolute URIs # in the uri table. create table issues ( id int unsigned not null auto_increment, # unique key, see uris table schema int unsigned not null, # unique schema id, see schemas table forum int unsigned not null, # forum this issue is added to, see forums table category int unsigned not null, # Category number, see %categories priority int unsigned not null, # Priority level, see %priorities created datetime, # Date created state int unsigned not null, # The current state of the report, see %states creator int unsigned not null, # Person or group, see contacts table summary varchar(255), # Creator's problem summary description text, # Creator's problem description owner int unsigned not null, # Person or pool, see contacts table o_result text, # Owner's description of the fix for custumer consumption o_notes text, # Technical notes about the fix for internal consumption o_source int unsigned, # Fixers guess as to how problem was introduced, see %sources o_effort int unsigned, # Guestimate of time required to fix in minutes primary key (id) ); # TABLE: links # ------------ # Here we maintain relationships between resources identified by # uris taken from the URI table. This means that it includes reports # which are identifed by a relative URI. # # Link relationships can be of any type (string) including "subsumed # by", "originating discussion", "follow-up", "solution", etc. create table links ( id int unsigned not null auto_increment, # unique key for all links source int unsigned not null, # Source document from uris table destination int unsigned not null, # Destination document from uris table rel char(64) not null, # link relationship rel_type int unsigned not null, # URI (from uris table) defining the relationship schema, or forum int unsigned not null, # forum (if any) this issue is added to, see forums table contact int unsigned not null, # Contact creating link, see contacts table comment varchar(255), # Comments primary key (id), unique (source,destination,rel,rel_type,forum) ); # TABLE: events # ------------- # We maintain a list of events of past and future events. Events can # trigger emails to be sent and various other notifications. We add both # where we come from and where we want to go in order to check that the # expected state hasn't changed underneath us. If so then we fail gracefully. create table events ( id int unsigned not null auto_increment, # unique key for all events issue int unsigned not null, # Document id from uris table from_state int unsigned not null, # current state to jump from (check against cur state in doc) to_state int unsigned not null, # next state to jump to when datetime, # When state changes or changed status int unsigned not null, # status of event, see %event_status contact int unsigned not null, # Contact creating event, see contacts table comment varchar(255), # Comments primary key (id) ); # TABLE: contacts # --------------- # Contacts is a table of all the people or groups having created a document # or fixes problems. Contacts can create issues as well as fix them. create table contacts ( id int unsigned not null auto_increment, # unique key for all people and groups we know username char(64) binary not null, # Username for access to editing the database password char(32) binary, # Password for access to editing the database realname char(64) not null, # Their full real name location char(64), # Physical location, office etc email int unsigned not null, # email from uris table used for notifications homepage int unsigned not null, # Homepage address from uris table comment varchar(255), # detailed description - not indexed primary key (id), unique (username) ); # TABLE: skills # ------------- # Here we associate contacts with the skills needed to "own" a document. Owning a # document means tbat that group or person knows (and is responsible for) how to carry it # forward to a/the final state. A group or person can have multiple skills - each # skill has its own entry. create table skills ( id int unsigned not null auto_increment, # unique key to allow editing by stupid clients (access) schema int unsigned not null, # unique schema id, see schemas table forum int unsigned not null, # forum this issue is added to, see forums table contact int unsigned not null, # Contact id, see contacts table issue int unsigned not null, # Issue number, see issues table. '0' means all issues in that forum category int unsigned not null, # Category number, see %categories. '0' means all priority int unsigned not null, # Severity level, see %priority. '0' means all state int unsigned not null, # The current state of the report, see %states. '0' means all primary key (id), unique (contact,forum,issue,category,priority,state) );