Database Schema

Databases store data based on the schema definition, so understanding it is a key part of learning how to manage databases. In this chapter, we will cover some of the key aspects of the schema structure and some important functionalities of DbSchema.

Schema

A database schema is the structure of the database described in a formal language. In the case of Relational Databases, the schema is composed of a grouping of tables. We can think of the schema as a blueprint of the database. A schema can contain only one table with a certain name.

Tables

A database table is a collection of data focused on a specific topic. A database can have multiple tables, related through foreign keys. Each table is made up of columns and rows. You can create a table in DbSchema by right-clicking on the empty layout, and select “Create New Table”. Find out more about how to create tables here.
Table in Layout

Editing tables

You can edit tables in DbSchema by double-clicking on the table header.

Table Editor

Columns

As we mentioned before, every table is made up of columns and rows. Each column can contain only one type of data (numeric, letters, etc.). For some columns, precision and/or decimal is required. The precision refers to the maximum number of characters allowed in the cells of the columns, while the decimal refers to the maximum number of decimal digits.

Learn more about how to create or edit columns here.

As shown in the first image, each column has a symbol that represents proprieties or foreign keys relations. To edit a column just double-click it in the layout diagram.

Each column may have few other properties:

Indexes

Similar to the index in a book, indexes provide a quick way to find the exact data item you want. Indexes can be defined over one or more columns.
Indexes can be:

Index Editor

Foreign Keys

A foreign key is a column or group of columns in a table that acts as a constraint. The foreign key enforces data from one column (referencing) to exist in another column (referenced). For every foreign key value of one table, there should be a corresponding value in the linked table. The referenced column can only be a primary key or unique column.

The columns which have a foreign key are marked with a small arrow on the right side of the column. Clicking it you can add or view the table on the other end of the foreign key.

Double-clicking any of the foreign key lines in the layout will open the Foreign Key editor. One foreign key must have at least one pair of columns.

If the referencing column is NULL, the check will not validate this value. NULLs are allowed in this column only if the column is not mandatory.

One-to-one, one-to-many and many to many foreign keys

The relation cardinality cannot be changed from the database, nor from DbSchema. This is a consequence of how the column and the indexes are created. Having different combinations of NOT NULL and UNIQUE indexes will lead to one of the referencing types below.

Referencing columns (from)Foreign Key TypeRepresentation (See Layout Menu - Fk Notation)
Not nullUnique
nono1:n (one to many)dashed line, 3-lines foot
noyes1:0 or 1 (one to zero or one)dashed line
yesno1:many (one to many)3-lines foot
yesyes1:1 (one to one)

Here in DbSchema under the default notation:

FK Notation
Or in Barker Notation ( you can set the notation from the Layout Menu ) :
FK Notation Barker

For the case one or more records from the primary key ( referred ) column are deleted or updated, different actions can be set for each foreign key:

If the database lacks a certain foreign key, you can create a virtual foreign key, that will be implemented only in DbSchema. This won’t affect the database in any way. The foreign key is saved in the model file. It can be used in Queries or Data Editor to simulate a real foreign key.

Composite foreign keys include two or more columns on each side. In this case in the Foreign Key Editor, there will be more columns listed. Each of the column values will match in the referred table. The primary key or unique index in the referred table will be defined as well on multiple columns.

Constraints

Constraints are validations of the data on insert or update. Two types of constraints can be defined :

*Hint:
Always use meaning-full names for constraints. For the constraint age > 18 use the name 'CheckAgeOver18'. If a user may try to insert the value 14 in the field age, he will get back in DbSchema or software 'Error: Constraint CheckAgeOver18 failed' which is easy to understand. If you name the constraint like 'Check214' you can imagine what he can understand from 'Error: Constraint Check124 failed'.

Constraints are useful to enforce data integrity, eq. have no incorrect data. Mistakes can occur via human errors when data is entered, computer or software errors, etc. Setting constraints may save lot of troubles in the software.

Foreign Key are also constraints. They are enforced via internal database triggers, so each time a new record is inserted or verified the inserted record is verified against the trigger condition.

Views

Views are SELECT queries which simulates a table. The view columns are the columns listed in the SELECT clause.

Views can be a clean way for the programmers to move their queries inside the database. Instead of keeping complex SELECTS in the application logic, they create views. Their statement will be saved in the database.

In the View Editor you can edit and test the query view statement. The view columns are automatically generated after testing the view query against the database.

Virtual foreign keys can be created between views and views or views and tables. They are useful in Data Editor or Query Builder to simulate real foreign keys.


View Editor

Sequences

A sequence is an auto-increment number generator Like auto-increment, it used during generating of Primary Key columns values.

MySql does not have sequences, he uses IDENTITY columns instead. These columns don't require eny value. They are automatically filled by the database.

Sample: in the table NAMES( ID integer identity, NAME varchar(100)) you can do 'INSERT INTO NAMES( NAME ) VALUES ( 'Lulu')'. The ID will be filled in by the database.

For Oracle you have to create a sequence. DbSchema will execute 'CREATE SEQUENCE MYSEQ' in the database. For the table NAMES you will to than 'INSERT INTO NAMES( ID, NAME ) VALUES ( MYSEQ.nextval, 'Lulu')'

Procedures, Functions and Triggers

Procedures, Functions and Triggers are Procedural Language ( PLSQL ) pieces of code. They are edited and created in DbSchema using the SQL editor . You can simply double-click one of the Procedures, Functions or Triggers in the Tree Pane and they will be opened in the SQL Editor inside DbSchema.
View Editor 2

Procedures can execute some operations in the database without returning any value. Functions will compute something and return a value. Triggers are fired by INSERT, UPDATE or DELETE operations in the database.
Usually you can do COMMIT or ROLLBACK only in procedures. Functions or triggers can't do this, the operation which calls them have to commit or rollback.

Procedure, trigger and functions are written in a database specific language. If you decide to convert the schema from one database to another, you have to re-write them.