Structure of wh data
From ICISWiki
Data Warehouse Tables for single study dataset retrieval
Schema of wh_data
wh_data
Fields
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| study | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | |||
| dataset | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| datarow | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| ounitid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| datacol | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| coltype | char(1) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| valtype | char(1) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| dataval | varchar(255) | utf8_general_ci | YES | | (NULL) | select,insert,update,references | ||
| datavaldesc | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| traitid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| scaleid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| tmethid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references |
| Indexes |
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed | Null | Index type | Comment |
| wh_data | 1 | wh_data_idx1 | 1 | dataset | A | 1482 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx2 | 1 | ounitid | A | 1275564 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx3 | 1 | datacol | A | 141 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx5 | 1 | datarow | A | 163272 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx6 | 1 | traitid | A | 240 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx7 | 1 | scaleid | A | 378 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx8 | 1 | tmethid | A | 427 | (NULL) | (NULL) | YES | BTREE |
MySQL Data Definition script:
CREATE TABLE `wh_data` ( `study` int(11) default NULL, -- link to study ID in wh_columns table `dataset` int(11) default NULL, -- link to dataset # in wh_columns table `datarow` int(11) default NULL, -- row number (1 to n) `ounitid` int(11) default NULL, -- the observation unit ID (link to OINDEX table) `datacol` int(11) default NULL, -- column number (1 to n) `coltype` char(1) default NULL, -- "F" (factor) or "V" (variate) (from wh_columns table) `valtype` char(1) default NULL, -- "C" if text, "N" if numeric (from wh_columns table) `dataval` varchar(255) default NULL, -- the data value `datavaldesc` varchar(255) default NULL, -- the description of the data value (if available in SCALEDIS) `traitid` int(11) default NULL, `scaleid` int(11) default NULL, `tmethid` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Sample data (for rice):
