index.mdx

---
sidebar_label: Data Model
slug: /model
title: Data Model
---

The data model employed by TDengine is similar to relational, users need to create database and tables. For a specific use case, the design of databases, stables (abbreviated for super table), and tables need to be considered. This chapter will explain the concept without syntax details.

There is an [video training course](https://www.taosdata.com/blog/2020/11/11/1945.html) that can be referred to for learning purpose.

## Create Database

The characteristics of data from different data collecting points may be different, such as collection frequency, days to keep, number of replicas, data block size, whether it's allowed to update data, etc. For TDengine to operate with best performance, it's strongly suggested to put the data with different characteristics into different databases because different storage policy can be set for each database. When creating a database, there are a lot of parameters that can be configured, such as the days to keep data, the number of replicas, the number of memory blocks, time precision, the minimum and maximum number of rows in each data block, compress or not, the time range of the data in single data file, etc. Below is an example of the SQL statement for creating a database.

```sql
CREATE DATABASE power KEEP 365 DAYS 10 BLOCKS 6 UPDATE 1;
```

In the above SQL statement, a database named "power" will be created, the data in it will be kept for 365 days, which means the data older than 365 days will be deleted automatically, a new data file will be created every 10 days, the number of memory blocks is 6, data is allowed to be updated. For more details please refer to [Database](/taos-sql/database).

After creating a database, the current database in use can be switched using SQL command `USE`, for example below SQL statement switches the current database to `power`. Without current database specified, table name must be preceded with the corresponding database name.

```sql
USE power;
```

:::note

- Any table or stable must belong to a database. To create a table or stable, the database it belongs to must be ready.
- JOIN operation can't be performed tables from two different databases.
- Timestamp needs to be specified when inserting rows or querying historical rows.

:::

## Create STable

In a typical IoT system, there may be a lot of kinds of devices. For example, in the electrical power system there are meters, transformers, bus bars, switches, etc. For easy aggregate of multiple tables, one STable needs to be created for each kind of devices. For example, for the meters in [table 1](/tdinternal/arch#model_table1), below SQL statement can be used to create the super table.

```sql
CREATE STABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location binary(64), groupId int);
```

:::note
If you are using versions prior to 2.0.15, the `STABLE` keyword needs to be replaced with `TABLE`.

:::

Similar to creating a normal table, when creating a stable, name and schema need to be provided too. In the stable schema, the first column must be timestamp (like ts in the example), and other columns (like current, voltage and phase in the example) are the data collected. The type of a column can be integer, floating point number, string ,etc. Besides, the schema for tags need t obe provided, like location and groupId in the example. The type of a tag can be integer, floating point number, string, etc. The static properties of a data collection point can be defined as tags, like the location, device type, device group ID, manager ID, etc. Tags in the schema can be added, removed or altered. Please refer to [STable](/taos-sql/stable) for more details.

Each kind of data collecting points needs a corresponding stable to be created, so there may be many stables in an IoT system. For electrical power system, we need to create a stable respectively for meters, transformers, bug bars, switches. There may be multiple kinds of data collecting points on a single device, for example there may be one data collecting point for electrical data like current and voltage and another point for environmental data like temperature, humidity and wind direction, multiple stables are required for such kind of device.

At most 4096 (or 1024 prior to version 2.1.7.0) columns are allowed in a stable. If there are more than 4096 of physical variables to bo collected for a single collecting point, multiple stables are required for such kind of data collecting point. There can be multiple databases in system, while one or more stables can exist in a database.

## Create Table

A specific table needs to be created for each data collecting point. Similar to RDBMS, table name and schema are required to create a table. Beside, one or more tags can be created for each table. To create a table, a stable needs to be used as template and the values need to be specified for the tags. For example, for the meters in [Table 1](/tdinternal/arch#model_table1), the table can be created using below SQL statement.

```sql
CREATE TABLE d1001 USING meters TAGS ("Beijing.Chaoyang", 2);
```

In the above SQL statement, "d1001" is the table name, "meters" is the stable name, followed by the value of tag "Location" and the value of tag "groupId", which are "Beijing.Chaoyang" and "2" respectively in the example. The tag values can be altered after the table is created. Please refer to [Tables](/taos-sql/table) for details.

:::warning
It's not recommended to create a table in a database while using a stable from another database as template.

:::tip
It's suggested to use the global unique ID of a data collecting point as the table name, for example the device serial number. If there isn't such a unique ID, multiple IDs that are not global unique can be combined to form a global unique ID. It's not recommended to use a global unique ID as tag value.

### Create Table Automatically

In some circumstances, it's not sure whether the table already exists when inserting rows. The table can be created automatically using the SQL statement below, and nothing will happen if the table already exist.

```sql
INSERT INTO d1001 USING meters TAGS ("Beijng.Chaoyang", 2) VALUES (now, 10.2, 219, 0.32);
```

In the above SQL statement, a row with value `(now, 10.2, 219, 0.32)` will be inserted into table "d1001". If table "d1001" doesn't exist, it will be created automatically using stable "meters" as template with tag value `"Beijing.Chaoyang", 2`.

For more details please refer to [Create Table Automatically](/taos-sql/insert#automatically-create-table-when-inserting).

## Single Column vs Multiple Column

Multiple columns data model is supported in TDengine. As long as multiple physical variables are collected by same data collecting point at same time, i.e. the timestamp are identical, these variables can be put in single stable as columns. However, there is another kind of design, i.e. single column data model, a table is created for each physical variable, which means a stable is required for each kind of physical variables. For example, 3 stables are required for current, voltage and phase.

It's recommended to use multiple column data model as possible because it's better in the speed of inserting or querying rows. In some cases, however, the physical variables to be collected vary frequently and correspondingly the stable schema needs to be changed frequently too. In such case, it's more convenient to use single column data model.