Defining a database schema

Documentation

April 25, 2019

A schema is a collection of database objects (tables) that contains the tables' unique names and its fields (columns) and the fields' data type. You have to define the schema before populating your data into aito. The schema helps

Comparison with relational database schema

aito's schema is almost similar to SQL database schema

Featuresaito schemaSQL Database Schema
Need to be defined before inputting datayesyes
Data type of a field (column) cannot be changedyesno
Supports duplicate rowsyesno
Required primary key in each tablenoyes
Handling null or missing fieldyesno
Relationship: one to manynoyes
Relationship: one to oneyesyes
Relationship: many to oneyesyes
Support multiple usersnoyes

Aito's data type

Int

The Int data type supports integer values in range 2-31 and 231-1 Schema example:

"id": { "type": "Int" }

Decimal

The Decimal data type supports decimal values, that is a number with decimal point in it. Schema example:

"price": { "type": "Decimal" }

Text

The Text data type supports text data. It can be analyzed by using analyzer to be turned into features for smart functions.

Analyzer

There can only be one analyzer for a field Supported analyzer:

  • Whitespace
    • Split the text into features by white space
    • "aito database" -> 2 features: "aito", "database"
  • English
    • Analyzer the text into stems by English
    • "aito database" -> 2 features: "aito", "databas"
  • Finnish
  • Swedish
  • German

Schema example

"description"   : { "type": "Text", "nullable": true, "analyzer": "English" }

String

The string data type is aito's basic text data. String data type cannot use analyzer. It turns the string into one feature. E.g: "jane doe" -> 1 feature: "jane doe"

"name"   : { "type": "String", "nullable": true }

Boolean

The boolean data type is similar to the SQL database boolean type. It supports the storage of two values: TRUE and FALSE. Schema example:

"click": { "type": "Boolean" }

NULLABLE

All data type can support null or empty data by adding nullable condition in the schema Schema example:

"id"   : { "type": "Int", "nullable": true }
"price": { "type": "Decimal", "nullable": true }
"click": { "type": "Boolean", "nullable": true }

Relationship

The relationship between tables in the aito database is defined by link, which is similar to the SQL database, except that aito can handle linking to a null field. Linking is crucial in order to help aito to take other tables into consideration when performing machine learning operations. Aito supports link of type:

One to one

Let's say you have a transactions table containing a "customer id" field and a users table containing the users' information and a "id" field. You can link the "customer id" field to the "id" field. This can be defined in the schema as

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

Using this relationship, you can perform machine learning operations from the transations table using information from the users table. For example, if an user name is John Doe, predict the product that he is most likely to buy.

Many to one

In the transactions table, you can link the "product" field to the "id" field of a products table. Let's say you have another table sales that contains a products id and its discounted price. You can also link to the "id" field in the product table as well.

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

Let's combine the example of linkings above

These tables and its relationships can be define in a schema as:

{
    "schema": {
        "users": {
            "type": "table",
            "columns": {
                "id"   : { "type": "Int" },
                "name" : { "type": "String" },
                "email": { "type": "String" }
            }
        },
        "transactions": {
            "type": "table",
            "columns": {
                "id": { "type": "Int" },
                "customer": { "type": "Int", "link": "users.id" },
                "product": { "type": "Int", "link": "products.id" }
            }
        },
        "products": {
            "type": "table",
            "columns": {
                "id": { "type": "String" },
                "price": { "type": "Decimal" },
                "description": { "type": "Text", "analyzer": "English"}
            }
        },
        "sales": {
            "type": "table",
            "columns": {
                "id": { "type": "Int", "link": "products.id" },
                "discounted price": { "type": "Decimal" }
            }
        }
    }
}
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