DbSchema Layouts
What is a Layout?
A layout is a database diagram with associated data and query tools. Think of it like a board on which you can make your own representation of the database schema. Inside the layout, you can edit tables and foreign keys by just double-clicking on them. Add tables, foreign keys, groups, callouts, and access data tools like Relational Data Editor or SQL Editor. You can create as many layouts as you need, each focused on a specific part of the schema. A table can be present in multiple layouts.
Why we use Layouts?
- Layouts allow you to separate complex databases into more specific sections. Therefore you can gain a better understanding of the database;
- The layout offers a visual representation of the database;
- You can edit tables, columns, foreign keys, and more just by clicking on them;
- Every layout is saved into the model file so you can later reopen it.
- The other DbSchema tools are open in the layout and will be saved to project file together with the layout.
Symbols in Layouts
Inside a layout, everything is represented graphically. This will help you gain a better understanding of the database and browse the data easily.
1. Table
2. Foreign Keys
The logical design allows defining the cardinality and ordinality for each relation.
In physical design, these are a consequence on how the referring column is indexes ( unique or not ) and if the column is mandatory or not.
In logical design, foreign keys have a different representations based on their cardinality and ordinality.
- Cardinality represents the maximum number of times an entry from a table can relate to entries from another table.
- Ordinality represents the minimum number of times an entry from a table can relate to entries from another table.
Since there are two tables involved in the relationship, the cardinality and ordinality are read from each table, and the symbol is read from the opposite table. Based on the columns involved in the relationship on both tables, there are 8 states in which a foreign key can be. We will try to understand them better by looking at some examples:
- Zero or One to Zero or Many
In the child table, the referencing column is not mandatory, nor unique. In this case, any entry in the child could refer to a parent entry, or be null, and a parent entry may not be referenced at all. Therefore, the parent table (Table 1) can refer to zero or many entries in the child table (Table 2), and the child table refers to zero or one parent entries, since the child referencing column is optional and can have a null value.
- One and only One to Zero or Many
In the child table, the referencing column is mandatory, but not unique. In this case, any entry in the child must refer to a parent entry, and a parent entry may not be referenced at all. Therefore, the parent table can refer to zero or many entries in the child table, but the child table must refer to one and only one parent entry, since the child referencing column cannot have a null value.
- Zero or One to Zero or One
In the child table, the referencing column is not mandatory, but is unique. In this case, any entry in the child could be null, but if it has a value, it must refer to a unique parent entry. A parent entry may not be referenced at all. Therefore, the parent table can refer to zero or one entries in the child table, and the child table refers to zero or one parent entries, since the child referencing column is optional and can have a null value.
- One and only One to Zero or One
In the child table, the referencing column is mandatory and unique. In this case, any entry in the child must refer to a unique instance of a parent entry, and a parent entry may not be referenced at all. Therefore, the parent table can refer to zero or one entries in the child table, but the child table must refer to one and only one parent entry, since the child referencing column cannot have a null value.
- Zero or One to One or Many
In the child table, the referencing column is not mandatory, nor unique. In this case, any entry in the child could refer to a parent entry, or be null, but now every parent entry MUST be referenced by a child. Therefore, the parent table must refer to at least one or many entries in the child table and the child table refers to zero or one parent entries, since the child referencing column is optional and can have a null value. To enforce the requirement that a parent entry must have a referencing child, you will need a trigger or some functionality to force the inserts to both parent and child together within the same transaction.
- One and only One to One or Many
In the child table, the referencing column is mandatory, but not unique. In this case, any entry in the child must refer to a parent entry, but now every parent entry MUST be referenced by a child. Therefore, the parent table must refer to at least one or many entries in the child table and the child table refers to one and only one parent entry, since the child referencing column is mandatory. To enforce the requirement that a parent entry must have a referencing child, you will need a trigger or some functionality to force the inserts to both parent and child together within the same transaction.
- Zero or One to One and only One
In the child table, the referencing column is not mandatory, but is unique. In this case, any entry in the child could be null, but if it has a value, it must refer to a unique parent entry. Every parent entry MUST be referenced by a child. Therefore, the parent table entries must refer to one and only one entry in the child table. A parent entry may not be referenced at all. To enforce the requirement that a parent entry must have a referencing child, you will need a trigger or some functionality to force the inserts to both parent and child together within the same transaction.
- One and only One to One and only One
In the child table, the referencing column is mandatory and unique. In this case, any entry in the child must refer to a unique instance of a parent entry. Furthermore, every parent entry must have a referencing child entry. Therefore, the parent table must refer to one and only child entry, and the child must refer to one and only one parent entry, since the child referencing column cannot have a null value. To enforce the requirement that a parent entry must have a referencing child, you will need a trigger or some functionality to force the inserts to both parent and child together within the same transaction.
Virtual Foreign Keys
There are databases that contains no foreign keys. In these situations, you may create virtual foreign keys. Virtual foreign keys will be created only in DbSchema and will be saved to the model file.
Design Schema Online or Offline
Using DbSchema you can design the schema while connected to a database (online) or without database
connection (offline).
In the online mode, all table and column changes will be applied in the database. The executed statements are visible on the left, in the SQL History pane.
In the offline mode, changes will be applied only to the DbSchema model which will be saved to file. You need to connect to the database and choose one of the Schema options (Refresh/Compare with the database).
Add tables to layout
1. Add tables from the tree panel by drag & drop.
2. Add tables using the foreign key icon.
Auto place
DbSchema integrates an option that will instantly organize the schema for you.
To access it, right-click on the layout and choose the Auto place option.
Column Visibility & Position
DbSchema does not allow resizing tables. Instead, you can choose the visible columns. The reason for this is that we focus on large and very large diagrams, where this feature will conflict with our algorithms for optimal handling diagrams.- Change the column visibility by double-clicking the table header. Then choose whether you want to see it or not from the visible column.
- Change the column position by clicking on the Up or Down buttons.
Foreign Key Routing
The foreign key line is routed automatically.
DbSchema is optimized for managing large diagrams, where this feature is likely to create problems.
You can choose between two modes of routing the foreign key lines: from table to table or from column to column.
In the second case, the line will point to the involved columns. Two foreign keys may merge into one line if they are pointing to the same column.
Show Data Type
Add Callouts
Add callouts by right-clicking on an empty part of the layout. Choose the Create a Callout option.
Fast Browse Table Data
Browse data from tables directly in the layout. Hold SHIFT + CTRL and click on the table header to see its contents.
Data Tools
You can open the data tool menu just by clicking on the table header.
Dropping Layouts
Layouts can be dropped by right-clicking the layout in the structure tree.
When closing a layout, DbSchema will ask if you wish to preserve the layout in the design model ( close the layout but keep a copy in the design model, so it can be reopen at any time ), or close and drop from the design model as well.
The same is done with the other data tools: SQL Editor, Query Builder and Relational Data Editor.
