Schema Structure

Databases stores the data based on the schema definition. Understand the structure by reading about:

Schema

Schemas or catalogs are a grouping of tables. Is common for one application to create at least one schema and put the tables inside. In this way is a clear distinction between the applications tables. Inside one schema cannot exist two tables with the same name. But is possible to create two tables in two different schemas with the same name. It is a common practice to install the same application on the same database more than one time, each time using different schemas. <

Tables

A table is a collection of data about a specific topic. Using a separate table for each topic means that you store that data only once, which makes your database more efficient and reduces data-entry errors. Tables stores the data in records, each record with the same columns ( values ).

In DbSchema you an add a new table using the table menu button or using the New Table option in the Layout Pane pop-up menu. Double-click the table header in diagram to edit. In the Table Editor you can add, edit, order or remove Columns, Indexes, Foreign Keys or Constraints.

The last tab in the Table Editor is the Storage . This is a specification for the database about how the table should be created in the database. For example, MySql uses engine=InnoDb as storage clause. In Database Settings Dialog you can define a default storage clause for all tables in the database.

Columns

Tables organize data into columns (called fields) and rows (called records). Each column contains data of a single data type ( text, number, boolean, etc. ) describing the same information ( like name, address, id, etc. ). For some data types a precision and/or decimal are required. The precision is the maximal number of characters or digits the data may have. The decimal is required for the floating point numbers as the maximal number of decimal digits ( decimal ).

Here can be set only primary keys over one column. To set composite primary keys ( using multiple columns ) please use the second tab 'Pk and Idexes' in the Table Dialog.

To edit a column just double-click it in the layout diagram.

Each column may have few other properties:
  • 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 may be 0, and a date may be current_date()
  • Check - a validation constraint that each value in the column should pass. Sample: check age > 18.
  • Unsigned - for numeric columns
  • Identity - by each insert 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 column - similar with extra property for type, but at column level.

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.
The unique indexes ensures that the values from the indexed columns are unique ( for one column index, there will be no duplicates for that column ).

The Primary Keys are in fact unique indexes over mandatory ( not null ) columns. A primary key is an unique identifer for each table record.

You may ask if is possible to create two Primary Keys for one table. No, this is not supported by databases. But you can get the same if you create an unique index ( the columns should be mandatory ).

Foreign Keys

Foreign Keys are constraints which enforces the data from one column ( referencing ) to exist in another column ( referred ). The referencing columns may contain only key values in the referred table. Any insert or update in the referencing column with invalid data will end with an error.

The referred column has to be a primary key or unique column. The foreign key enforces that the referencing column can store only values existing in the referred column.

The columns which have a foreign key ( in or out ) 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.

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.

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. The columns has to have the same data type. The foreign key can be defined over two or three pair of columns ( composed foreign key ). Press the small Add button to add further columns.

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

The relation type ( one-to-one, ... ) is a consequence of the involved columns properties. Having different combinations of NOT NULL and UNIQUE over the referencing and referred columns lead to one of this types.

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 :

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

The many-to-many relationships cannot be implemented in the physical model unless you use an intermediate table.
Sample: To establish a many-to-many relationship between FLIGHTS and PEOPLES we may use an intermediate table PERSON_FLIGHTS. We may use two foreign keys PERSON_FLIGHTS( flightid ) to FLIGHTS( flightid ) and PERSON_FLIGHTS( personid ) to PERSONS( personid ).

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:

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

A Foreign Key can be virtual and won't be implemented in the database. The foreign key is saved in the project file. It can be used in Queries or Data Editor to simulate a real foreign key.

Composite foreign keys are involving two or more columns on each side. In this case in the Foreign Key Editor there will be more columns listed, and each of the column value will match the value 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. This are set in the Column Dialog
  • Table constraints, can combine two or more columns. For example age > 14 OR with_parents=true. This 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 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. Than they can do some more efficient testing of the database, by running the checks for the database data and for views at the same time.

In the View Editor you have the chance to 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.

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. By insert this columns doesn't require any 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.

Procedures can do 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.