When you decide to create a new schema or view/edit an existing schema, you get to the Schema-Details view. Here you can see all the important information about the schema.
On the left side, there is an Entity-Relation Diagram (ER-Diagram) representing the schema. Each database table in the schema is represented as a box. In the box, you can see the name of the database table and a list of all columns of the table. If the database table has a foreign key to another table, it is displayed as an arrow pointing to the target table. When creating or editing a database schema, you can add a new entity to the diagram by hitting the “Add Entity” button in the top right corner of the ER-Diagram.
In the ER-Diagram, you can select a table to see or edit its properties like name or description. These are displayed on the right side of the screen in the “Properties“-section. You can also select a foreign key to view its properties. These can however only be changed in the Table-Details-View (see below). If no table or foreign key is selected, you can see the general properties of the database schema.
Properties can only be edited if the table or foreign key is not deployed to a data source.
When a database entity is selected, you can edit the columns, indexes and foreign keys of that table by clicking on the “Edit table details“-button in the Properties section. This will take you to the Table-Details-View.
In the DB Designer View , we provide the following Versions of the Schema:
|Draft||Working Draft that has not been released yet – you cannot work with the Data Service Shape on this schema before you will release it.|
|Release||The released Version of the Database Schema – this Version is published via OData V4 Route and can be used with the Data Service Shape within Process Designer|
|Live||The Live Version collects the Schema Information directly over the SQL Connector from the Database. You can use the Live View to recognize differences between Draft and Release for e.g. if someone edit the schema outside the Simplifier DB Designer (MySQL Workbench, PHPMyAdmin etc).|
The Table-Details-View consists of three sub-views: Columns, Indexes, and Foreign Keys.
In this view, you can add new columns to the database table or edit the properties of existing columns. It is also possible to remove a column from the table.
The following properties can be edited for a column:
|Name||The name of the column (cannot be edited if the column is already deployed to a data source).|
|Description||A description for the column.|
|Type||The datatype of the Column (e.g. String, Integer, …).|
|Character limit||With a checkbox whether or not the column should be limited. Only String-Type columns can have a character limit.|
|Default Value||With a checkbox whether or not a default value should be used.|
|NotNull||Indicating if the column is able to store null values or not.|
|Auto-Increment||Whether or not the column should have automatically incrementing values. It is only possible to have one Auto-Increment column per table and it has to be an Integer-Type column.|
In this view, you can add new indexes to the database table or edit the properties of existing indexes. It is also possible to remove an index from the table.
The following properties can be edited for an index:
|Name||A unique name for the Index.|
|Description||A description for the index.|
|Type||The type for the index (Index, Primary Key, Unique).|
|Columns||The columns to which the index should be mapped. This input provides an auto-complete for the existing column names in the table.|
In this view, you can add new foreign keys to the database table or edit the properties of existing foreign keys. It is also possible to remove a foreign key from the table.
The following properties can be edited for a foreign key:
|Name||A unique name for the foreign key.|
|Description||A description of the foreign key.|
|Target table||The target table for the foreign key. The dropdown contains the names of existing tables of the schema.|
|Target columns||The Column mapping to the target table. When a target table is selected, you can see a list of all primary key columns of the target table. You can then choose a column of the current table that should be mapped for each of these primary key columns. You can only choose columns that match the datatype of the target column (e.g. if the target column is an Integer-Type column, only the Integer-Type columns of the current table are listed as possible mapping columns).|
|On Update||The “ON UPDATE” property of the foreign key. Can be set to “RESTRICT”, “CASCADE”, “SET_NULL”, or “SET_DEFAULT”. The default value is “RESTRICTED”, which is also the default value for most databases.|
|On Delete||The “ON DELETE” property of the foreign key. Can be set to “RESTRICT”, “CASCADE”, “SET_NULL”, or “SET_DEFAULT”. The default value is “RESTRICTED”, which is also the default value for most databases.|