Database table

A table is a collection of related data held in a structured format within a database. It consists of fields (columns), and rows.

If you have ready-to-use database with data, you can easily import the database in your AnyLogic project.

Alternatively, you can create database table(s) in your model and enter data manually. 

Let's explain how to create a new table in AnyLogic built-in model's database.

 To create an empty database table

  1. In the Projects view, right-click the model's Database item, and choose New >  Database table from the popup menu.
  2. You will see the New Database Table wizard. On the first page of the wizard, choose how you want to create a table. To create an empty table, choose the first option, Create an empty table.
  1. New database table will be created. You will see the database table editor opened in the centre of the workspace (see the figure below). In the Projects view, new table db_table will appear in the Database branch.
  1. By default the database table is named db_table. In the Properties view, modify the table name in the Name field.
Having created the table, you should create table columns, and then enter data.

 To add a new column in the database table

  1. In the Projects view, expand the Database branch of the model tree, and click the table item to open its properties.
  2. In the Properties view, you will see the properties of the database: edit box to define the table's Name, and a set of property sections below. Each section defines properties for an individual table column. By default a new table contains one column named db_column. Modify the properties of the table column (the column properties are described below). 
  1. If required, add more column(s) by clicking   button in the properties, and then modify the column properties in a similar way. 
  2. Having added and configured all columns, you can enter data in the table using the database table editor.

Properties

General

Name – The name of the database table.

Below you can see the set of sections. Each section defines properties of an individual column of the database table, and contains the following controls: 

Column name – The name of the column.

Type – The data type for the column, please refer here for information on data types.

Default value – Here you can define the default value for this column that will be used whenever no value is specified for this column.

Indexed – If selected, this column is indexed. In the table editor, indexed column is marked with  icon.

Unique – If selected, this column can contain only unique values. In the table editor, unique column is marked with  icon.

Foreign key – Here you specify, whether this column acts as a foreign key, or not. Foreign key is a field in one table that uniquely identifies a row of another table by referring to the primary key defined there. For example, you have a table named FLIGHT that has a primary key named FLIGHT_ID. Another table called FLIGHT_DETAILS has a foreign key which references FLIGHT_ID to uniquely identify the relationship between both the tables. In the table editor, foreign key column is marked with  icon.

At the bottom, you will see a row of buttons enabling the user to control the table column, which property section is currently selected:


Add – Adds a new column.

 Remove – Deletes the currently selected column.

 Up – Changes the order of the currently selected column (move it closer to the start of the column list). 

 Down – Changes the order of the currently selected column (move it closer to the end of the column list).

Advanced

Optimize performance for – Here you choose one of two alternative ways of storing table data in the computer memory:

faster access time (better if less than 50000 rows) - Default choice. If this option is selected, table data is entirely held in memory while you are working with this model in AnyLogic (the table is stored in RAM). Choose this option if the data set is relatively small (less than 50 000 entries).

handling large amount of data - If this option is selected, only part of table data or indexes is held in memory, allowing to work with large tables which would otherwise take up to several hundred megabytes of memory (the table is cached). Another advantage of cached tables is that the database engine takes less time to start up when a cached table is used for large amounts of data. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small.

Every table of AnyLogic database has a column storing unique IDs of the table rows. This column is named al_id. By default it is invisible, but you can turn its visibility on in the Preferences dialog, on the Database page.