Hoard/Data

Data Schemae

Things

This table keeps track of all the things -- a taxonomy in the form of a branching hierarchy. One guideline to remember: there's no need to put a thing under another thing unless that's necessary in order to disambiguate between two homonymous things.

CREATE TABLE
  `things` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `WhenNew`    timestamp NOT NULL DEFAULT current_timestamp(),
    `ID_Base`    int(11) DEFAULT NULL COMMENT 'ID of parent record',
    `ID_Extern`  int(11) DEFAULT NULL COMMENT 'external reference, if any',
    `Name`       varchar(255) DEFAULT NULL COMMENT 'label, e.g. for using in lists',
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

Stuff

These tables are for stuff about the things -- a set of semantic values that can be set for any Thing.

This table records the different types of Stuff that we can record -- a common name for each type of attribute, along with its data-type (so the UI knows which table to use for values).

CREATE TABLE `stuff` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `WhenNew`    tmestamp              NOT NULL DEFAULT current_timestamp(),
    `Name`       varchar(255)      DEFAULT NULL COMMENT 'name of attribute',
    `Type`       varchar(255)          NOT NULL COMMENT 'code for data-type',
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

StuffInt

This is Stuff that is integer values.

CREATE TABLE `stuff_int`
  ID_Thing   int(10) unsigned      NOT NULL COMMENT 'the Thing to which this value applies',
  ID_Stuff   int(10) unsigned      NOT NULL COMMENT 'what type of Stuff this value is',
  `Value`    int(10)                        COMMENT 'might eventually want this to be a longint; unsure of syntax',
  ) ENGINE = InnoDB

StuffStr

This is Stuff that is string values.

CREATE TABLE `stuff_str`
  ID_Thing   int(10) unsigned      NOT NULL COMMENT 'the Thing to which this value applies',
  ID_Stuff   int(10) unsigned      NOT NULL COMMENT 'what type of Stuff this value is',
  `Value`    VARCHAR                        COMMENT 'the string value, of course',
  ) ENGINE = InnoDB

StuffTime

This is Stuff that is absolute time values. For relative values, use an integer or float quantity of the number of applicable time-units.

CREATE TABLE  `stuff_time`
  ID_Thing   int(10) unsigned      NOT NULL COMMENT 'the Thing to which this value applies',
  ID_Stuff   int(10) unsigned      NOT NULL COMMENT 'what type of Stuff this value is',
/* TODO: Value */
  ) ENGINE = InnoDB

Extern

This tracks external data repositories. I'm not sure this is the right way to do it, but it seems specialized enough to have earned its own table.

CREATE TABLE
  `extern` (
    `ID`       int(10) unsigned NOT NULL AUTO_INCREMENT,
    `WhenNew`  timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'when this record was added',
    `SID_Type` varchar(255) DEFAULT NULL COMMENT 'slug for thing-type (matches coded external data)',
    `Name`     varchar(255) DEFAULT NULL COMMENT 'single line for lists',
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB