Defining a database schema

Documentation

April 25, 2019

A schema object is a description of the data format for the database. It consists of tables, columns, and type types of the columns. You have to define the schema before uploading your data into Aito.

A schema object looks like this:

{
  "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

FeatureAitoSQL database
Schema needs to be defined before inputting datayesyes
Relationship: one to manyyesyes
Relationship: one to oneyesyes
Relationship: many to manynoyes
Directional (one-way) linksyesyes
Bidirectional linksnoyes
Joining over link depth >1noyes

Supported data types

You can see the full list of supported data types in our API docs.

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.

One to one

Let's say you have a users table containing basic information of the user, and user contacts table containing contact information of the user.

Users and transactions tables

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

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

Many to one

Let's say you have transactions and users tables. You can link from the transactions table to the users table with the id.

Users and user contacts tables

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

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

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 data type
    • "analyzer" if needer
    • "link": if needed
Back to developer docs

Visit us

Annankatu 34 B

00100 Helsinki

Finland

See map

26 Underwood Street

N1 7JQ

London, UK

See map

Contact info

We'd love to hear from you.

hello@aito.ai

Follow us