Difference between revisions of "Opasnet Base"

From Testiwiki
Jump to: navigation, search
(Page created)
 
m (→‎All tables)
Line 35: Line 35:
 
|VALIGN="top"|
 
|VALIGN="top"|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''act'''
+
|colspan=5|'''acts'''
 
|-
 
|-
 
|colspan=5|'''Uploads, updates, and other actions'''
 
|colspan=5|'''Uploads, updates, and other actions'''
Line 43: Line 43:
 
|id||int(10) unsigned||NO||auto_increment||PRI
 
|id||int(10) unsigned||NO||auto_increment||PRI
 
|-
 
|-
−
|acttype_id||tinyint(3) unsigned||NO||||MUL
+
|type||ENUM('initial','replace','append')||NO||||
 
|-
 
|-
 
|who||varchar(50)||NO||||
 
|who||varchar(50)||NO||||
Line 56: Line 56:
 
|VALIGN="top"|
 
|VALIGN="top"|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''actloc'''
+
|colspan=5|'''acts_locs'''
 
|-
 
|-
 
|colspan=5|'''Locations of an act'''
 
|colspan=5|'''Locations of an act'''
Line 69: Line 69:
 
|VALIGN="top"|
 
|VALIGN="top"|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''actobj'''
+
|colspan=5|'''acts_objs'''
 
|-
 
|-
−
|colspan=5|'''Acts of an object'''
+
|colspan=5|'''Acts of an objects'''
 
|-
 
|-
 
|Field||Type||Null||Extra||Key
 
|Field||Type||Null||Extra||Key
Line 81: Line 81:
 
|obj_id||int(10) unsigned||NO||||MUL
 
|obj_id||int(10) unsigned||NO||||MUL
 
|-
 
|-
−
|series_id||int(10) unsigned||NO||||MUL
+
|dat_id||int(10) unsigned||NO||||MUL
 
|-
 
|-
 
|unit||varchar(64)||YES||||
 
|unit||varchar(64)||YES||||
Line 89: Line 89:
 
|
 
|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''acttype'''
+
|colspan=5|'''objs'''
 
|-
 
|-
−
|colspan=5|'''List of action types'''
+
|colspan=5|'''Object information (all objects)'''
 
|-
 
|-
 
|Field||Type||Null||Extra||Key
 
|Field||Type||Null||Extra||Key
Line 97: Line 97:
 
|id||int(10) unsigned||NO||auto_increment||PRI
 
|id||int(10) unsigned||NO||auto_increment||PRI
 
|-
 
|-
−
|acttype||varchar(250)||NO||||UNI
+
|ident||varchar(20)||NO||||UNI
 +
|-
 +
|name||varchar(200)||NO||||
 +
|-
 +
|type||ENUM('variable','study','method','assessment','class','nugget','encyclopedia')||NO||||
 +
|-
 +
|page||int(10) unsigned||NO||||
 +
|-
 +
|wiki_id||tinyint(3) unsigned||NO||||
 +
|-
 +
|lang||char(2)||NO||||
 
|}
 
|}
  
 
|VALIGN="top"|
 
|VALIGN="top"|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''cell'''
+
 
 +
|colspan=5|'''inds'''
 
|-
 
|-
−
|colspan=5|'''Cells of an object'''
+
|colspan=5|'''Indices'''
 
|-
 
|-
 
|Field||Type||Null||Extra||Key
 
|Field||Type||Null||Extra||Key
 
|-
 
|-
−
|id||int(12) unsigned||NO||auto_increment||PRI
+
|id||int(10) unsigned||NO||auto_increment||PRI
 
|-
 
|-
−
|actobj_id||int(10) unsigned||NO||||MUL
+
|ident||varchar(20)||NO||||UNI
 
|-
 
|-
−
|mean||float||YES||||
+
|name||varchar(200)||NO||||
 
|-
 
|-
−
|sd||float||NO||||
+
|page||int(10) unsigned||NO||||
 
|-
 
|-
−
|n||int(10)||NO||||
+
|wiki_id||tinyint(3) unsigned||NO||||
−
|-
 
−
|sip||varchar(2000)||YES||||
 
 
|}
 
|}
  
 
|VALIGN="top"|
 
|VALIGN="top"|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''loc'''
+
|colspan=5|'''locs'''
 
|-
 
|-
 
|colspan=5|'''Location information'''
 
|colspan=5|'''Location information'''
Line 144: Line 153:
 
|-
 
|-
 
|
 
|
−
{| {{prettytable}}
+
 
−
|colspan=5|'''loccell'''
 
−
|-
 
−
|colspan=5|'''Locations of a cell'''
 
−
|-
 
−
|Field||Type||Null||Extra||Key
 
−
|-
 
−
|cell_id||int(10) unsigned||NO||||PRI
 
−
|-
 
−
|loc_id||int(10) unsigned||NO||||PRI
 
−
|}
 
  
 
|VALIGN="top"|
 
|VALIGN="top"|
−
{| {{prettytable}}
+
 
−
|colspan=5|'''obj'''
 
−
|-
 
−
|colspan=5|'''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||||
 
−
|}
 
  
 
|VALIGN="top"|
 
|VALIGN="top"|
−
{| {{prettytable}}
+
 
−
|colspan=5|'''objtype'''
 
−
|-
 
−
|colspan=5|'''Types of objects'''
 
−
|-
 
−
|Field||Type||Null||Extra||Key
 
−
|-
 
−
|id||tinyint(3)||NO||||PRI
 
−
|-
 
−
|objtype||varchar(30)||NO||||
 
−
|}
 
  
 
|-
 
|-
 
|
 
|
−
{| {{prettytable}}
+
 
−
|colspan=5|'''res'''
 
−
|-
 
−
|colspan=5|'''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||||
 
−
|}
 
  
 
|VALIGN="top"|
 
|VALIGN="top"|
 
{| {{prettytable}}
 
{| {{prettytable}}
−
|colspan=5|'''wiki'''
+
|colspan=5|'''wikis'''
 
|-
 
|-
 
|colspan=5|'''Wiki information'''
 
|colspan=5|'''Wiki information'''

Revision as of 11:55, 24 January 2012



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

acts
Uploads, updates, and other actions
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
type ENUM('initial','replace','append') NO
who varchar(50) NO
comments varchar(250) YES
time timestamp NO
temp_id int(10) unsigned NO MUL
acts_locs
Locations of an act
Field Type Null Extra Key
actobj_id int(10) unsigned NO PRI
loc_id int(10) unsigned NO PRI
acts_objs
Acts of an objects
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
dat_id int(10) unsigned NO MUL
unit varchar(64) YES
objs
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
type ENUM('variable','study','method','assessment','class','nugget','encyclopedia') NO
page int(10) unsigned NO
wiki_id tinyint(3) unsigned NO
lang char(2) NO
inds
Indices
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
ident varchar(20) NO UNI
name varchar(200) NO
page int(10) unsigned NO
wiki_id tinyint(3) unsigned NO
locs
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





wikis
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

Retrieved from "http://www.opasnet.org/testiwiki/index.php?title=Opasnet_Base&oldid=23512"