CREATE TABLE descriptorFulfillment (
  nodeID int(10) unsigned NOT NULL,
  descriptorID int(10) unsigned NOT NULL references descriptors (descriptorID),
  PRIMARY KEY  (nodeID,descriptorID)
);


CREATE TABLE descriptors (
  descriptorID int(10) unsigned NOT NULL auto_increment,
  `text` varchar(250) NOT NULL,
  PRIMARY KEY  (descriptorID)
);


CREATE TABLE edges (
  edgeID int(10) unsigned NOT NULL auto_increment,
  fromID int(10) unsigned NOT NULL references nodes (nodeID),
  toID int(10) unsigned NOT NULL references nodes (nodeID),
  formEdgeID int(10) default NULL references formEdges (formEdgeID),
  PRIMARY KEY  (edgeID)
);


CREATE TABLE externalLinks (
  `table` varchar(32) NOT NULL,
  rowID int(10) NOT NULL,
  field varchar(32) NOT NULL,
  URI int(10) NOT NULL,
  PRIMARY KEY  (`table`,rowID,field)
);


CREATE TABLE formEdges (
  formEdgeID int(10) unsigned NOT NULL auto_increment,
  schemeID int(10) unsigned NOT NULL references schemes (schemeID),
  descriptorID int(10) unsigned default NULL references descriptors (descriptorID),
  schemeTarget int(10) unsigned default NULL references schemes (schemeID),
  formEdgeTypeID int(10) NOT NULL references formEdgeTypes (formEdgeTypeID),
  `name` varchar(128) NOT NULL,
  PRIMARY KEY  (formEdgeID)
);


CREATE TABLE formEdgeTypes (
  formEdgeTypeID int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  direction enum('IN','OUT') NOT NULL,
  PRIMARY KEY  (formEdgeTypeID)
);


CREATE TABLE locutions (
  nodeID int(10) unsigned NOT NULL references nodes (nodeID),
  personID int(10) unsigned NOT NULL references people (personID),
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `source` varchar(256) NOT NULL
);


CREATE TABLE nodes (
  nodeID int(10) unsigned NOT NULL auto_increment,
  `text` longtext NOT NULL,
  `type` varchar(2) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (nodeID)
);


CREATE TABLE nodeSetMappings (
  nodeID int(10) unsigned NOT NULL references nodes (nodeID),
  nodeSetID int(10) unsigned NOT NULL references nodeSets (nodeSetID),
  PRIMARY KEY  (nodeID,nodeSetID)
);


CREATE TABLE nodeSets (
  nodeSetID int(10) unsigned NOT NULL auto_increment,
  title varchar(64) default NULL,
  PRIMARY KEY  (nodeSetID)
);


CREATE TABLE people (
  personID int(10) unsigned NOT NULL auto_increment,
  firstName varchar(32) NOT NULL,
  surname varchar(32) NOT NULL,
  description text NOT NULL,
  PRIMARY KEY  (personID)
);


CREATE TABLE schemeFulfillment (
  nodeID int(10) unsigned NOT NULL references nodes (nodeID),
  schemeID int(10) unsigned NOT NULL references schemes (schemeID),
  PRIMARY KEY  (nodeID,schemeID)
);


CREATE TABLE schemes (
  schemeID int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL,
  schemeTypeID int(10) NOT NULL references schemeTypes (schemeTypeID),
  PRIMARY KEY  (schemeID)
);


CREATE TABLE schemeTypes (
  schemeTypeID int(10) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY  (schemeTypeID)
);


CREATE TABLE `user` (
  userID int(11) NOT NULL auto_increment,
  username varchar(64) default NULL,
  `password` varchar(255) default NULL,
  PRIMARY KEY  (userID),
  UNIQUE KEY username (username)
);

