# Database Design

By default, the store creates a database and two tables: *tssamples\_metadata* and *tssamples\_data*. Metadata of the time series associated with the store is placed In *tssamples\_metadata*, while the time series values are written in *tssamples\_data*. 

Structure of the time series metadata table (*tssamples\_metadata*):

| <p>tag\_set \[PK]</p><p><strong>jsonb</strong></p> | <p>server\_uid</p><p><strong>text</strong></p> | <p>store\_uid</p><p><strong>text</strong></p> | <p>ts\_uid</p><p><strong>text</strong></p> | <p>ts\_path</p><p><strong>text</strong></p> | <p>ts\_comment</p><p><strong>text</strong></p> | <p>tag\_set\_id</p><p><strong>integer</strong></p> |
| -------------------------------------------------- | ---------------------------------------------- | --------------------------------------------- | ------------------------------------------ | ------------------------------------------- | ---------------------------------------------- | -------------------------------------------------- |

* **tag\_set**: The column is the primary key of the table and is of the JSONB type. The column is a set of attributes or *tags* (key-value pairs) identifying a time series. By default, the set contains only one tag, *tsid*, representing the address of the time series. However, by changing parameters from the *Advanced* category, you can add additional tags. For example, if you set the parameter *Add Time Series Comment to Tag*, a *tsc* tag will be added to the set containing a comment to the series.
* **server\_uid**: The column is of the TEXT type and contains a unique server identifier.
* **store\_uid**: The column is of the TEXT type and contains unique store identifier.
* **ts\_uid**: The column is of the TEXT type and contains unique time series identifier.
* **ts\_path**: The column is of the TEXT type and contains path to time series. The values of this column are indexed.
* **ts\_comment**: The column is of the TEXT type and contains comment for time series.
* **tag\_set\_id**: The column is of the INTEGER type, is auto-incremental and contains unique numeric identifier for time series.

Structure of the time series data table (*tssamples\_data*):

| <p>sample\_time</p><p><strong>timestamp without time zone</strong></p> | <p>tag\_id</p><p><strong>integer</strong></p> | <p>sample\_value</p><p><strong>double precision</strong></p> | <p>sample\_status</p><p><strong>integer</strong></p> |
| ---------------------------------------------------------------------- | --------------------------------------------- | ------------------------------------------------------------ | ---------------------------------------------------- |

* **sample\_time:** The column is of the TIMESTAMP WITHOUT TIME ZONE type and contains measurement time. The values of this column are indexed.
* **tag\_id**: The column is of the INTEGER type and contains the link to the *tag\_set\_id* column from the *tssamples\_metadata* table. The values of this column are indexed.
* **sample\_value**: The column is of the FLOAT8 type and contains the sample value.
* **sample\_status**: The column is of the INTEGER type and contains the sample status.

Below is an example of a database query that will return data for a 5-minute interval for time series with the paths *MySeries.Temperature* and *MySeries.Pressure*:

```sql
SELECT m.ts_path, d.sample_time, d.sample_value, d.sample_status
FROM public.tssamples_data d
JOIN public.tssamples_metadata m ON m.tag_set_id = d.tag_id
WHERE d.sample_time BETWEEN '2020-03-20T09:20:00' AND '2020-03-20T09:25:00'
AND (m.ts_path = 'MySeries.Temperature' OR m.ts_path = 'MySeries.Pressure')
ORDER BY m.tag_set_id, d.sample_time DESC
```

Note that the time of this query is in the UTC format.