Version: Current

Tables - basics

In your application's tables-dictionary.kts file, you need to define every table that your application needs. Let us look at a very simple example definition. This contains a single table with three fields in it.

Then you can look closer at all the elements that can go into a table definition.

tables {

  table(name = "POSITION", id = 11002) {
      autoIncrement(POSITION_ID)
      INSTRUMENT_ID
      QUANTITY
      NOTIONAL

      primaryKey { 
        POSITION_ID
      }
  }

}

The autoIncrement keyword is used to set this field automatically to the next number in the sequence when a new record is written to the database. We shall discuss this further in the Advanced page.

Table name

When you define a table, you must start by giving it a name that is unique to the application:

  table(name = "POSITION")

Table ID

Tables must also be given a Table ID that is unique to the application. This ensures that you can rename a table without losing the data in it.

  table(name = "POSITION", id = 11002)

Fields

After specifying a name and a Table ID, specify the fields in the table.

You do not need to specify field types and other attributes - just the field name.

If you are using intelliJ IDE, it will auto-suggest the available fields; any references that are not known will be shown in red.

  table(name = "POSITION", id = 11002) {
      autoIncrement(POSITION_ID)
      INSTRUMENT_ID
      QUANTITY
      NOTIONAL

  }

Making a field not null

All field types except ENUM are nullable by default. However, you can set a nullable field so that it is not nullable on a specific table. When you add the field to the table, just add not null. Effectively, this makes the field mandatory for that table. It does not affect usage of the field in other tables in your application.

For example, here we have set two fields to be not null:

table(name = "TRADE", id = 2000) {
      autoIncrement(TRADE_ID)
      COUNTERPARTY_ID
      INSTRUMENT_ID not null
      QUANTITY
      PRICE not null
      NOTIONAL

      primaryKey { 
        TRADE_ID
      }
  }

Primary key

By default, a table is a set of columns (fields), where you can insert and maintain a number of rows (records). You need to provide ways to query the database so that a specific record or records can be found. So you need to define one or more indices.

The most important index (plural: indices) is called the primary key. This is mandatory, and must be defined by one or more fields which have a unique value or combination of values.

So let's add a simple primary key to the example above. It is on the POSITION_ID field, so that we can find a specific record or range of records. It is common to use a single sequenced/autoIncrement field as a primaryKey in this way:

tables {

  table(name = "POSITION", id = 11002) {
      autoIncrement(POSITION_ID)
      INSTRUMENT_ID
      QUANTITY
      NOTIONAL
      
      primaryKey {
          POSITION_ID 
      }
  }

}

Providing other indices

In many cases, you will want to provide other indices so that all the useful ways of looking at the table are made possible.

There are two types of index that you can specify:

A unique index ensures that no two records in the table can have the same value for the specified field or fields. For example, this could be a TRADE_ID in a TRADES table, where the unique value is generated via autoIncrement or sequence. Or it could be a CURRENCY_SYMBOL in a table of CURRENCIES.
A non-unique index is useful when uniqueness is not important or not possible. For example, you could add a non-unique index on the ORDER_ID field in your TRADE table. This enables you to find all the trades that match a specified order. (ORDER_ID would be unique in the ORDERS table, but the order can be filled by multiple trades.)

So how does that work in practice? Imagine a scenario where you have projects and users.

You have a PROJECTS table where each project has a unique PROJ_ID.
You have a USERS table where each project has a unique USER_ID.

There is a many-to-many relationship between the two tables - projects can have more than one user, and users can belong to more than one project. To allocate users to projects, you need a third table: PROJECT_USERS.

In the the PROJECT_USERS table, you only need two fields:

PROJ_ID
USER_ID

Each record in this table states that PROJ_ID = x has USER_ID = y. However, we require that each unique combination of PROJ_ID and USER_ID can only occur once in the table. So in our table, we define the primary key on PROJ_ID and USER_ID. We can rely on the database to ensure uniqueness; an attempt to insert a record with a PROJ_ID and USER_ID combination that already exists will fail.

Here is our table definition for PROJECT_USERS:

  table(name = "PROJECT_USERS", id = 11020) {
      PROJ_ID
      USER_ID
      primaryKey {
          PROJ_ID
          USER_ID
      }
     indices {
         nonUnique {
             USER_ID
         }
     }
  }

In the above example:

We can query on the primary key just on PROJ_ID to find the users associated with that project.
We can also query the primary key on both PROJ_ID and USER_ID to find if a specific user is associated with a specific project.
However, we cannot skip a field and query the primary key on only USER_ID. So, we have provided the non-unique index on USER_ID, which enable us to find all projects associated with a specific user.

Indices with multiple fields

Any index you create - the primary key or other unique or non-unique indices - can have multiple fields.

If you create an index with multiple fields, it is possible to make a search based on all the fields specified or to make a partial search based on some of the fields. However, there are strict limits to this; see our page on operations and indices for information.

Also note:

The order of the fields is important if you want partial searches to be useful and efficient.
You cannot define two identical indices; you cannot supply exactly the same fields in exactly the same order as the primary key or another index on the same table.

How is data retrieved from the database?

See our page on operations and indices for information on the operations that can be used to perform look-ups on an index.

Naming the primary index and other indices

It is best practice for you to give each primary key or index a name when you declare it.

Use the name parameter to specify the name of a primary key or index, for example:

    primaryKey(name = "POSITION_PRIMARY_KEY") {
      POSITION_ID
    }

Accepting default names

It is not mandatory to specify a name for a primary key or index. If you don't provide one, Genesis creates one using the following rule:

[TABLE_NAME]_BY_[FIELD_1](_[FIELD_N])

where FIELD_1 - FIELD_N are all the fields that make up the key/index in the order specified.

There is one exception; this is the common use case where the primary key is made up of a single field with a name in the format [TABLE_NAME]_[ID]. In this case, Genesis creates the name in the format [TABLE_NAME]_BY_ID.

Let's see some examples. Below, the table name is POSITION. The names created by Genesis are shown in the comment before each primary key.

    
    //POSITION_BY_ID
    primaryKey { 
        POSITION_ID
    }

    //POSITION_BY_OTHER_FIELD
    primaryKey { 
        OTHER_FIELD
    }
    
    //POSITION_BY_OTHER_FIELD_ANOTHER_FIELD
    primaryKey { 
        OTHER_FIELD
        ANOTHER_FIELD
    }

Table name​

Table ID​

Fields​

Making a field not null​

Primary key​

Providing other indices​

Indices with multiple fields​

How is data retrieved from the database?​

Naming the primary index and other indices​

Accepting default names​