Assess and determine a database technology

[gonuke]

For the specific database design we are considering, what is a good DB technology?

Considerations:

• total data size
• API for slicing and selecting data
  • usability
  • efficiency

[gonuke]

Start with Pandas and some of their standard file options and see how it performs.

[gonuke]

Expected database table design:

* parent nuclide
* normalized spectrum type  -----
* total irradiation time.       |--- DB index
* average flux magnitude.   -----
* child nuclide
* number density

[anu1217]

Here's a high-level comparison/overview of what I've found out about hdf5 and SQLite specifically.

Attribute	hdf5	SQLite
Ease of setup	Requires dedicated software/packages to read and interpret data (e.g. h5py) due to binary format	Serverless & stores data in a single file (can be used in Python through sqlite3)
Ease of usage	Data stored in filesystem-like format	Relational database that can hold data in multiple 2D tables
Ease of usage	Verbose syntax due to large number of variables created	Only takes one user connection at a time to a database
Data storage	Consistently performs well with large datasets and quickly saves and retrieves data	Slower to query large datasets and create an overall representation of data
Data slicing	Can be accessed like NumPy arrays, manipulated using numpy attributes	Data queried using verbose SELECT & filter statements (e.g. WHERE)
Data stability	Potential data corruption issues over time	Backwards compatible, cross-platform stable
Data stability	Lack of fluidity & large performance overhead for frequently updated files	Datatypes are advisory, not mandatory - can lead to cross-platform consistency issues

I think the relevance of some of these considerations depends on whether we're adding data or querying existing data more frequently. In the latter case I can see hdf5 being the more suitable format.

[gonuke]

Let's go with SQLite under Pandas and try it, with the option translate everything to HDF5 if we find a need in the future.