Logical Design

Your team hasn't chosen between PostgreSQL and SQL Server yet, but it needs to agree on the data now: what the shop stores and how the parts connect, without arguing about integer against bigint. A logical design in DbSchema holds the entities, their attributes and the relations between them, with general data types and names written in plain words. When the design is ready, DbSchema generates the physical model for the database you choose. Logical design needs DbSchema Architect.

A logical design uses its own words for the parts of a schema:

Physical model	Logical design
Schema	Subject Area
Table	Entity
Column	Attribute
Foreign key	Relation

A logical model connects to no database. Everything on this page changes the model file only, until you generate the physical model and create it in a database.

Start a logical model

1. Choose File → New Model.
2. Choose Design without Database.
3. Choose Logical Design and press Start Design.
4. In the Model Properties dialog, type a name in Project/SubjectArea Name and press OK.

The empty logical model opens in a window of its own. The Database menu of a logical model holds no connection, and the Query menu no SQL Editor.

Turn a physical model into a logical one

A logical design can also start from a model that you reverse-engineered from a database. The conversion makes a new model, and the original model stays as it is.

1. Open the physical model.
2. Choose Model → Convert Model.
3. Set Target DBMS to LogicalDesign and press OK.
4. DbSchema shows the Model Conversion Rules. Press Convert.

The logical model opens in a new window. Attribute names are written with spaces instead of underscores, so customer_id becomes customer id. Each relation comes in as Non-Identifying and Optional, with One or More where the column was NOT NULL and Zero or More where it allowed empty values, such as categories.parent_id.

Convert stops with "Not all LogicalDesign data types are specified. Please edit." when a data type of the model has no logical counterpart. In the shop model that is smallint, the type of reviews.rating: its row in Model Conversion Rules shows ??? on the right.

1. Double-click the ??? cell. The Convert Data Type dialog opens.
2. Select the LogicalDesign row and press Edit.
3. Choose a Data Type, such as INT, and press OK twice.
4. Press Convert again.

File → Properties offers the same conversion, in the menu of the DBMS button: Convert to...

Create an entity

1. Click an empty spot on the diagram, where the entity should go.
2. Choose Table → Create Entity in the toolbar, or press Ctrl+T. Right-click the diagram and choose Create Entity to do the same.
3. Type the name in Table Name.
4. In the Attributes tab, press Add for each attribute. Give it a name and a data type, and tick Primary Key for the key.
5. Press OK.

The logical data types are general: BIGINT, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, DOUBLE, ENUM, INT, MONEY, NUMERIC, NVARCHAR, TEXT, UUID and VARCHAR. Each one becomes a data type of the target database when you generate the physical model.

Super Table makes the entity a sub-entity of another one, so that employee and customer can both inherit the attributes of person. The Inheritance Model of Generate the physical model decides how the hierarchy becomes tables. In the super-entity, the Discriminator box in the attribute dialog marks the attribute that tells the sub-entities apart.

Create a relation

A relation links two entities, the way a foreign key links two tables. The child entity refers to the parent entity.

1. Drag the attribute of the child onto the key attribute of the parent, such as order id of shipments onto order id of orders.
2. The New Relationship dialog opens, filled in. Choose the type and the cardinality, as Set the cardinality of a relation shows.
3. Press OK.

Table → Create Relation in the toolbar opens the same dialog without the drag. Double-click a relation line to edit the relation.

If the attributes of the parent have no primary key or unique key, DbSchema adds a unique index on them when you press OK, named like unq_name, because a relation must point at unique values.

Set the cardinality of a relation

Cardinality says how many children a parent can have and whether a child needs a parent. In a logical design you set it in the relation dialog yourself, whatever the attributes are:

Option	What it sets
Identifying	The child's primary key includes the referring attributes
Non-Identifying	The child keeps a primary key of its own
Many to Many	Many rows on each side, such as products and categories
Mandatory	A child always has a parent, and its attributes become Not Null
Optional	A child may have no parent, and the line is dashed
Zero or More, One or More, Zero or One, One	How many children a parent has
Range	A from and a to count, each between 0 and 255

Identifying makes the relation Mandatory, and Optional is then disabled.

The picture shows three relations to orders, set like this:

Relation	Type	Mandatory or Optional	Cardinality
payments to orders	Non-Identifying	Optional	Zero or More
order items to orders	Identifying	Mandatory	One or More
shipments to orders	Non-Identifying	Mandatory	One

The marks at the child end show the cardinality, and the default notation draws an arrow at the parent end. View → Relations → Relation Notation offers the same notations as for foreign keys. In Information Engineering, an Optional relation also shows a circle at the parent end, and a Mandatory one a bar.

Generate Physical Model turns a Many to Many relation into an entity between the two, named after both, such as products_categories, with a relation to each. Right-click the relation and choose Resolve Many-To-Many to do that in the logical design.

Set how names and types convert

Two dictionaries decide what the physical model looks like. Both are under Model → Dictionaries, and in the Convert menu of the toolbar.

The Naming Dictionary turns logical names into physical names. Separators sets the character between words: Space in the logical design and Underscore in the physical model, by default. Convert Cases writes the names in the letter case that the target database prefers. The table below them holds your own pairs, such as a long word and its short form.

To see the names that the physical model will get before you generate it, tick View → Diagram Display → Show Physical Name. The diagram then shows customer_id in place of customer id.

The Conversion Dictionary maps each data type to a data type of the target database. The Model Conversion Rules dialog opens before every conversion and shows the rules that apply, so you can check them there. To change a rule, double-click its right-hand cell.

The rules from PostgreSQL to the logical design map integer to BIGINT, so a model converted to the logical design and back to PostgreSQL has bigint where it had integer. To keep integer, change that rule to INT before you press Convert.

Generate the physical model

1. Choose Model → Generate Physical Model, or Convert → Generate Physical Model in the toolbar.
2. Set Target DBMS, such as PostgreSQL.
3. Choose the Inheritance Model, from the table below.
4. Press OK, check the Model Conversion Rules, and press Convert.

Inheritance Model	Tables for a super-entity and its sub-entities
SingleTable	One table, the super-entity's, which gets the attributes of every sub-entity
ClassTable	One table per entity, each with its own attributes
ConcreteTable	One table per sub-entity, with the super-entity's attributes added

With ConcreteTable, the super-entity gets no table of its own. The physical model opens in a new window, and the logical design stays as it is. The columns keep the Not Null that Mandatory and Identifying set, and Optional leaves the columns as they are. The cardinality stays behind: a relation set to One gets no unique key. In the physical model, DbSchema reads the cardinality from the columns again, as Foreign Keys shows.

Connect the physical model to a database and create the schema there. The same steps convert a physical model to another database, such as MySQL to PostgreSQL. Triggers, functions and procedures are written in the language of one database, so check them after a conversion.

Common problems

I can't find Generate Physical Model

Generate Physical Model is in the Model menu of a logical model. In a physical model the same item reads Model → Convert Model.

How do I go from a physical model back to a logical one?

Convert it with Target DBMS set to LogicalDesign, as Turn a physical model into a logical one shows.

I created the relation the wrong way round

DbSchema can't swap the parent and the child of a relation. Right-click the line, choose Drop, and drag again from the child attribute onto the parent attribute.

Where did this unique index come from?

A new relation to attributes with no primary key or unique key adds one, as Create a relation says. If you created the relation by mistake, drop the index in the Primary Key, Indexes tab of the parent entity.

The cardinality I set is gone in the physical model

A physical model has no setting for cardinality: DbSchema draws it from NOT NULL and the unique keys of the child columns. Change those in the physical model, as Change the cardinality shows.

Next steps

• Link tables with foreign keys
• Create tables, columns and indexes
• Create the schema in the database