This page is about the Opasnet Base 2 -database.
Question
How to improve the existing Opasnet Base? Following issues of Opasnet Base 1 must be resolved:
- Opasnet Base 1 structure makes filtering by location very slow on big data
- Opasnet Base 1 structure is perhaps unnecessarily complex = queries are hard to adapt and read
- MySQL is not ideal for storing huge amounts of data, or at least this is how we assume?
- MySQL tables have fixed column types = difficult to store data objects with varying types of indices into one data table
- Multiple languages (localization) not supported
Answer
MySQL is good on relations but weak on dynamic big data. Let's keep the basic "scaffold" in MySQL and store the big data (locations and results) into noSQL-base. After few days of research the best candidate for noSQL-base seems to be MongoDB. Combining relational MySQL and non-relational MongoDB will be the foundation for the new Opasnet Base 2.
Table structure in the database
All tables
| act
|
| Uploads, updates, and other actions
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
int(10) unsigned |
NO |
auto_increment |
PRI
|
| acttype_id |
tinyint(3) unsigned |
NO |
|
MUL
|
| who |
varchar(50) |
NO |
|
|
| comments |
varchar(250) |
YES |
|
|
| time |
timestamp |
NO |
|
|
| temp_id |
int(10) unsigned |
NO |
|
MUL
|
|
| actloc
|
| Locations of an act
|
| Field |
Type |
Null |
Extra |
Key
|
| actobj_id |
int(10) unsigned |
NO |
|
PRI
|
| loc_id |
int(10) unsigned |
NO |
|
PRI
|
|
| actobj
|
| Acts of an object
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
int(10) unsigned |
NO |
auto_increment |
PRI
|
| act_id |
int(10) unsigned |
NO |
|
MUL
|
| obj_id |
int(10) unsigned |
NO |
|
MUL
|
| series_id |
int(10) unsigned |
NO |
|
MUL
|
| unit |
varchar(64) |
YES |
|
|
|
| acttype
|
| List of action types
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
int(10) unsigned |
NO |
auto_increment |
PRI
|
| acttype |
varchar(250) |
NO |
|
UNI
|
|
| cell
|
| Cells of an object
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
int(12) unsigned |
NO |
auto_increment |
PRI
|
| actobj_id |
int(10) unsigned |
NO |
|
MUL
|
| mean |
float |
YES |
|
|
| sd |
float |
NO |
|
|
| n |
int(10) |
NO |
|
|
| sip |
varchar(2000) |
YES |
|
|
|
| loc
|
| Location information
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
int(10) unsigned |
NO |
auto_increment |
PRI
|
| std_id |
int(10) unsigned |
NO |
|
MUL
|
| obj_id_i |
int(10) unsigned |
NO |
|
MUL
|
| location |
varchar(100) |
NO |
|
|
| roww |
mediumint(8) unsigned |
NO |
|
|
| description |
varchar(150) |
NO |
|
|
|
| loccell
|
| Locations of a cell
|
| Field |
Type |
Null |
Extra |
Key
|
| cell_id |
int(10) unsigned |
NO |
|
PRI
|
| loc_id |
int(10) unsigned |
NO |
|
PRI
|
|
| obj
|
| Object information (all objects)
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
int(10) unsigned |
NO |
auto_increment |
PRI
|
| ident |
varchar(20) |
NO |
|
UNI
|
| name |
varchar(200) |
NO |
|
|
| objtype_id |
tinyint(3) unsigned |
NO |
|
MUL
|
| page |
int(10) unsigned |
NO |
|
|
| wiki_id |
tinyint(3) unsigned |
NO |
|
|
|
| objtype
|
| Types of objects
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
tinyint(3) |
NO |
|
PRI
|
| objtype |
varchar(30) |
NO |
|
|
|
| res
|
| Result distribution (actual values)
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
bigint(20) unsigned |
NO |
auto_increment |
PRI
|
| cell_id |
int(12) unsigned |
NO |
|
MUL
|
| obs |
int(10) unsigned |
NO |
|
|
| result |
float |
NO |
|
|
| restext |
varchar(250) |
YES |
|
|
| implausible |
binary(1) |
YES |
|
|
|
| wiki
|
| Wiki information
|
| Field |
Type |
Null |
Extra |
Key
|
| id |
tinyint(3) |
NO |
|
PRI
|
| url |
varchar(255) |
NO |
|
|
| wname |
varchar(20) |
NO |
|
|
|
Contents of selected tables
Table objtype
| id |
objtype
|
| 1 |
Variable
|
| 2 |
Study
|
| 3 |
Method
|
| 4 |
Assessment
|
| 5 |
Class
|
| 6 |
Index
|
| 7 |
Nugget
|
| 8 |
Encyclopedia article
|
| 9 |
Run
|
|
Table acttype
| id |
acttype
|
| 1 |
Start object
|
| 2 |
Finish assessment
|
| 3 |
Update formula
|
| 4 |
Upload data (replace)
|
| 5 |
Upload data (append)
|
| 6 |
Review scope
|
| 7 |
Review definition
|
| 8 |
Add object info
|
|
See also