Defining a database schema

The Aito database schema is a description of how the database is constructed and internally processed. A schema contains the information of:

• The name of the tables
• The name and the ColumnType of the columns in each table
• The Analyzer of a column if needed
• The relationships (links) between tables

The Aito database requires a defined schema before executing other operations. The schema is defined in the JSON format and populate to Aito using the Schema API Endpoint.

Schema structure

• A schema defines tables with unique name, mapping from table name to table definition.
• A table must define:
  • its name
  • "type": "table" as only "table" is currently supported
  • "columns": defines columns with unique name, mapping from column name to column definition
• A column must define:
  • its name
  • "type": the appropriate column type
  • "analyzer" if needed
  • "link": if needed

An example of the Aito schema:

{
  "schema": {
    "users": {
      "type": "table",
      "columns": {
        "id": { "type": "String" },
        "name": { "type": "String" },
        "age": { "type": "Int", "nullable": true }
      }
    },

    "messages": {
      "type": "table",
      "columns": {
        "id": { "type": "String" },
        "user": { "link": "users.id", "type": "String" },
        "text": { "type": "Text", "analyzer": "English" }
      }
    }
  }
}

Comparison to relational databases

Relationships

The relationship between tables in the database are defined by links. Linking is crucial in order to help Aito to take other tables into consideration when performing machine learning operations. Please refer to the Utilizing Relationships in Aito article for more details.

One to one

Considering a case where you have a "users" table containing the information of the user and a "user contacts" table containing contact information of the user.

This can be defined in the schema of the user contacts table:

"user": { "type": "Int", "link": "users.id" }

Many to one

Considering a case where you have a "transactions" table and the "users" table. You can link from the transactions table to the users table with the id.

This can be defined in the schema of the user contacts table:

"user": { "type": "Int", "link": "users.id" }