Database Schema

Databases store data based on the schema definition, so understanding it is a key part of designing databases. In this chapter, we will cover some of the key aspects related to the schema definition.

• Schemas
  • Tables
    • Columns
    • Indexes
    • Foreign Keys
    • Constraints
  • Views
  • Sequences
  • Procedures, Functions and Triggers
• Sample Schema

Schema

In the 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 more tables related through foreign keys. Each table is made up of columns and rows.

• Columns are defined to hold a specific type of data such as numeric, dates, or text. Column names can’t be duplicated in a table. Every table has a primary key column that uniquely identifies the data in the table.
• Rows in a table can be zero or more. If a table holds zero rows, then that table is said to be empty. Each row is uniquely identified by a primary key, which can be one or more sets of column values.

You can create a table in DbSchema by right-clicking on the empty layout and selecting "Create New Table". Find out more about how to create tables here.

Editing tables

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

• Edit Columns - edit, add, and remove certain columns in the table. You can change the position of the column in the table and change its visibility.
• Edit Primary Keys - add or edit primary keys or unique indexes in a table
• Edit Foreign Keys - add or edit foreign keys in your table. Edit basic details, change the definition of the foreign keys by adding or removing columns, and edit the “On Delete” actions.
• Edit Constraints - add or edit check constraints in your table. We will develop more about constraints further on.
• Edit Options - Edit the table options visually, without having to write any line of code.

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 other properties:

• Mandatory Not null - the column should always have a value. Null means nothing has been specified.
• Default Value - if no value is specified for the column by insert, this value will be used. Samples: a boolean may have as default value true, a number can be 0, and a date can be current_date()
• Unsigned - for numeric columns
• Identity - by each 'insert' operation, the column value is autogenerated from an auto-increment number.
• Extra property for type - used only by some databases, may specify some extra text for the column data type. This is used in the Schema creation scripts.
• Extra property for a column - similar to the extra property for type, but at the column level.

Indexes

As an index of contents in a book, indexes provide a quick way to find the exact data item you want. Indexes must have one or more columns.
Indexes can be:

• Normal - it can store any value
• Unique - they don’t allow duplicate values. If a value is inserted and that value already exists in another record, an error will be displayed.
• Primary Key - it’s the same as unique indexes, plus the condition that the column is mandatory (it doesn’t allow empty values).

Foreign Keys

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

The foreign key columns are marked with a small arrow on the right side of the column. Click it to add the table on the other end of the foreign key to the layout.

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

Only in the logical design you can manually set the foreign key type.

In the physical design, the relation cardinality is a consequence of the colum mandatory and uniqueness indexes. Having different combinations of NOT NULL and UNIQUE indexes will lead to one of the referencing types below.

Referencing columns (from)		Foreign Key Type	Representation (See Layout Menu - Fk Notation)
Not null	Unique
no	no	1:n (one to many)	dashed line, 3-lines foot
no	yes	1:0 or 1 (one to zero or one)	dashed line
yes	no	1:many (one to many)	3-lines foot
yes	yes	1:1 (one to one)

Here in DbSchema under the default notation:

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

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

• On Delete Cascade - will delete the records in the referencing tables having the same keys as the dropped primary key columns.
• On Delete No Action - if the key is in use in the referencing table, an exception is returned to the user. The user has to drop first the child records.
• On Delete Set Null - the matching keys in the referencing table will be set to null.

If the database lacks a certain foreign key, you can create virtual foreign keys, 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 :

• Column check constraints - used to validate one single column data. For example age > 18. They are set in the Column Dialog
• Table constraints - can combine two or more columns. For example age > 14 OR with_parents=true. They are defined in the Table Editor.

*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 a lot of trouble in the software.

Foreign Keys 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 that simulate 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, you can create views. The view creation statement is saved in the database.

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

You can create virtual foreign keys between views and views or views and tables. They are useful in Data Editor or Query Builder to explore data from multiple tables or create join queries between views.

Sequences

A sequence is an auto-increment number generator, like auto-increment. Sequences are used to generate the values for the Primary Key columns.

MySql does not have sequences, so it uses IDENTITY columns instead. These columns are automatically filled by the database and don't require any value.

Sample: in the table PERSONS( ID integer identity, FIRSTNAME varchar(100)) you can do 'INSERT INTO PERSONS( FIRSTNAME ) VALUES ( 'Lulu')'. The database will fill in the ID column.

For Oracle, you have to create a sequence. DbSchema will execute 'CREATE SEQUENCE MYSEQ' in the database. You will use: 'INSERT INTO PERSONS( ID, FIRSTNAME ) VALUES ( MYSEQ.nextval, 'Lulu')'

Procedures, Functions & 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 double-click one of the Procedures, Functions, or Triggers in the Tree Pane to open them in the SQL Editor.

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 execute commit or rollback. The operation calling them to has to commit or rollback.

The procedures, triggers, 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.