# The database structure

The _mySQL_ database is structured into four main components:

1. A lists of all the filenames and other metadata of time series (the **TimeSeries** table),
2. A list of all the names and other metadata of pieces of time-series analysis operations (the **Operations** table),
3. A list of all the pieces of code that must be evaluated to give each operation a value, which is necessary, for example, when one piece of code produces multiple outputs (the **MasterOperations** table), and
4. A list of the results of applying operations to time series in the database (the **Results** table).

Additional tables are related to indexing and managing efficient keyword labeling, etc.

Time series and operations have their own tables that contain metadata associated with each piece of data, and each operation, and the results of applying each method to each time series is in the **Results** table, that has a row for every combination of time series and operation, where we also record calculation times and the quality of outputs (for cases where there the output of the operation was not a real number, or when some error occurred in the computation). Note that while data for each time series data _is_ stored on the database, the executable time-series analysis code files _**are not**_, such that all code files must be in Matlab’s path (all required paths can be added by running `startup.m`).&#x20;

Another handy (but dangerous) function to know about is `SQL_reset`, which will _**delete all data in the mySQL database**_, create all the new tables, and then fill the database with all the time-series analysis operations. The **TimeSeries**, **Operations**, and **MasterOperations** tables can be generated by running `SQL_CreateAllTables`, with master operations and operations added to the database using `SQL_Add` commands (described [here](populating)).

You now you have the basic table structure set up in the database and have done the first bits of _mySQL_ manipulation through the Matlab interface.

It is very useful to be able to inspect the database directly, using a graphical interface. A very good example for Mac is the excellent, free application, [_Sequel Pro_](http://www.sequelpro.com) (a screenshot is shown below, showing the first 40 rows of the Operations table of our default operations library). Applications similar to _Sequal Pro_ exist for Windows and Linux platforms. Applications that allow the database to be inspected are extremely useful, however they should _**not**_ be used to manipulate the database directly. Instead, Matlab scripts should be used to interact with the database to ensure that the proper relationships between the different tables are maintained (including the indexing of keyword labels).

![SQLPro for Mac](https://254595279-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LEJuzw5fsQv4fwW3E7P%2F-LEJvnlWrtHaZ1olifXB%2F-LEJvoj3pbh7lV9g_Adp%2FSQLProScreenshot.png?generation=1528285843406956\&alt=media)
