Logical Design Documentation for Database Development
For the architect who models entities and relations before the engine is chosen, and has to hand that model to people who will never open DbSchema.
On this page
A model that lives only on the screen of the person who drew it gets reviewed by nobody. DbSchema turns the same model into two outputs: documentation the reviewers open in a browser or read as a PDF, and a physical schema for whichever engine you pick, with your naming rules and type mappings applied during the conversion. Logical design is in the Architect edition.
Why a logical design needs documentation of its own
Logical design is modelling the data before you commit to a database engine, so a logical model carries no SQL syntax, no engine-specific data types and no physical tuning. What it does carry is the vocabulary of the business, and that vocabulary is the part everybody outside the database team can actually check. A reviewer who cannot read a CREATE TABLE script can still tell you that an invoice belongs to one customer rather than to many.
The terminology moves with the level of the model, which matters as soon as two people describe the same object in different words. DbSchema's logical design documentation maps the two vocabularies onto each other:
| Physical design | Logical design |
|---|---|
| Schema | Subject Area |
| Table | Entity |
| Column | Attribute |
| Foreign Key | Relation |
Read the table in the direction the conversation is going. The reviewer talks about entities and relations, the person writing the migration talks about tables and foreign keys, and both are looking at one model.
Logical design documentation in DbSchema
From the DbSchema welcome screen, choose Design from Scratch and then Logical Design. A blank logical diagram opens, and a right-click on the canvas creates the first entity. Each entity has attributes, and a relation is drawn by dragging from an attribute in one entity to an attribute in another.
What makes the diagram documentation rather than a sketch is the properties you set on each relation, because those are the sentences a reviewer can argue with. An identifying relation says the parent key is part of the child entity key, so the child cannot exist without the parent. A non-identifying relation says the child references the parent but has an identifier of its own. Mandatory says every child must reference a parent, optional says the reference may be absent. Cardinality is 1:1, 1:n, or many-to-many.
Everything you do here changes the DbSchema model file. No database is involved yet, and there does not have to be a connection open at all, which is the point of designing at this level first.
The naming dictionary and the conversion dictionary
Logical names are written for people, and physical names have to satisfy whatever convention your databases follow. The naming dictionary holds that translation: a logical attribute named First Name becomes first_name or FIRST_NAME, depending on the rules you set. Open it from the Convert Model menu.
The conversion dictionary does the same job for data types, per target database. A logical type of Text converts to VARCHAR(255) for MySQL and to NVARCHAR(255) for SQL Server, and you can change any mapping to match your own conventions.
Both dictionaries are worth filling in before the first conversion rather than after it, because they are what stops the physical model from becoming a second design that has to be kept in step with the first by hand. They are settings of the model, so editing them writes nothing to any database.
Converting the logical design into a physical design
Choose Convert Model → Generate Physical Design. DbSchema applies the naming dictionary and the conversion dictionary and produces a physical schema for the database you target, and that schema is fully editable afterwards: you add indexes, constraints and stored procedures on top of what the conversion produced. A many-to-many relation is resolved into a junction entity during the conversion, which is one design decision you do not have to make twice.
The conversion still only touches the model file. The database changes at the next step, when you connect and open Schema → Synchronize Model with Database: DbSchema generates the SQL statements that bring the database in line with the model, shows them to you, and runs them when you click Execute. Everything before that click is reversible by editing the model, which is the reason to get the naming and the types right at the logical level.
HTML5, PDF and Markdown documentation from the same model
Open the export from Diagram → Export HTML5 or PDF Documentation. The dialog asks three questions: the format, which is HTML5, PDF or Markdown; which diagrams to include, either the current one, all open ones, a selection, or every diagram tagged documentation, where the tag value sets the sort order; and which schema elements go into the output, such as columns, foreign keys, indexes and comments.
The HTML5 output opens in a browser with no server behind it. It carries the diagram as a vector image, a searchable list of tables, and the full column details, so a reader clicks a table to jump to its definition and hovers over a column to read its description. That hover is worth writing for. Descriptions entered in the Description field of a table or column become content in every format and mouse-over tooltips in the HTML5 one, so a description written once during design is the sentence a reviewer reads two months later. Comment tags sit beside them as key-value pairs on any table or column, which is where a sensitivity level, an owner or a deprecation status belongs.
PDF is the format for a review that has to be printed or archived, and it needs one setting if your descriptions are not in a Latin alphabet: turn on Embed Unicode Font in the PDF options. Markdown exports each table as a section with its columns, types and descriptions, which is the format to commit next to the code.
Regenerating the output after a change is a job you can hand to a script. DbSchema's Automation Scripts generate the documentation from Groovy without the interface, so a build can rebuild the HTML5 output on every commit and nobody has to remember to export it by hand.
The first subject area is the cheapest one to get right, because nothing has been deployed yet and the names still cost nothing to change. Download DbSchema at https://dbschema.com/download.html, choose Design from Scratch and then Logical Design, and draw that subject area before the engine is picked. Logical design is in the Architect edition, which also covers the HTML5, PDF and Markdown documentation export.
