A5:SQL Mk-2 (a5m2) is known as a SQL client, but an ER diagram editor ships with it. Without leaving the window you run queries in, you can pull the structure of an existing database into a diagram, design a new schema, and produce the image that goes into your documentation.
For an existing database, start with reverse generation. For a new design, start with a blank ER diagram. Image export, DDL generation and table specifications are available from the same program. The menu names below use the English labels.
What the ER editor in A5:SQL Mk-2 does
The ER editor covers three kinds of work:
- Pull an ERD out of an existing database. Pick tables from a connection and the diagram builds itself.
- Design a new schema. Draw entities and relationships on a blank canvas, then turn that design into CREATE TABLE statements.
- Produce documentation. Export the finished diagram as an image, and the schema as an Excel or CSV table specification.
Because all three live in the same application, you move from connection to diagram to DDL to specification without switching tools.
Getting started: install and connect
A5:SQL Mk-2 is a free Windows program distributed from its official site. Unpack it and run it; there's no installer to fight with.
It has dedicated connection support for Oracle, PostgreSQL, MySQL and SQLite. SQL Server connects through an OLE DB provider, while other compatible databases can use OLE DB or ODBC drivers. Some features vary by DBMS. If you plan to diagram an existing database, set up the connection first. If you're designing from scratch, you can open a blank ER diagram without connecting to anything.
Once a connection exists, its schemas and tables appear in the database tree on the left. That tree is where reverse generation starts.
Reverse engineering an existing database into an ERD
When the database is already running, you read the diagram in rather than drawing it.
- Choose Database → Reverse Generate ER Diagram. The same command sits on the right-click menu of a connection in the database tree.
- A dialog lists the tables on that connection. Select all of them, or only the domain you care about right now.
- Click the reverse generate button and the ER diagram appears, with tables, columns and foreign key relationship lines read straight from the schema.
This is the fastest way into a database you've just inherited. On a schema with hundreds of tables, running it several times by domain (members, orders, billing) produces a few readable diagrams instead of one unreadable sheet.
Three things to check right after reverse generation
Before you trust the picture, look at three things: whether the relationship lines match the actual foreign keys, whether indexes came across, and whether the data types match the originals. The official help notes that these can come through differently depending on the database, so it's worth a quick check the first time you point a5m2 at an unfamiliar DBMS.
If the symbols on the line ends are unfamiliar, keep the ERD notation guide open beside you. Four crow's foot combinations cover everything you'll see.
Drawing a new ER diagram
To design from scratch, open File → New → New ER Diagram. The example below builds a small forum: members, posts and comments.
Adding an entity. Click the add-entity mode button on the toolbar, then click where you want it on the canvas. Double-click the new entity to open its properties, where you set its logical and physical names. For a members table, the logical name is Member and the physical name is members.
Filling in fields. The same properties dialog holds each field's logical name, physical name, data type, required flag, key information and default expression, plus the index definitions. Physical names become real column names later, so settling your naming convention here saves edits after the DDL is generated.
Connecting relationships. Click the add-relation mode button, click the parent entity, then click the child. In the forum example, members is the parent and posts is the child. The parent and child can't be swapped after the relationship is placed, so decide the direction before you click. When you're unsure which side is the parent, the side whose cardinality is one is almost always it.
Setting cardinality. Double-click a relationship to choose the counts at both ends: zero or one, one, zero or more, one or more. For members and posts, that's one on the member side and zero or more on the post side. Both IE notation and IDEF1X are supported, so match whatever your team already uses.
Other objects. Clicking the same entity twice creates a self-referencing relationship, which is how you draw a comment that points at its parent comment. Views and subtypes are separate objects too, and a subtype child automatically inherits the parent entity's primary key.
Sort out indexes and data types while you're here
The entity properties dialog has room for index information alongside the field list. Writing indexes down at design time means the generated DDL includes the CREATE INDEX statements. In the forum example, that's the author and creation date on posts, and the post id on comments.
Instead of typing data types by hand every time, you can define type domains: email as VARCHAR(255), money as DECIMAL(15,2), and so on. Fields then pick from that list, which keeps the same concept from drifting into three different lengths as the schema grows. Deciding naming and typing rules up front also pays off when you generate the specification later.
Tidying the diagram
Entities move by dragging, and relationship lines follow the positions of their parent and child. If a line crosses another entity awkwardly, drag the line itself to nudge it.
Comment objects let you annotate the diagram. They never reach the database, so a note like "batch jobs only" travels with the picture when you export it. Line segments, shapes and images work the same way, which is enough to draw domain dividers or add a title without leaving the editor.
When wide tables take over the canvas, open ER Diagram → ER Diagram Properties and lower the maximum number of rows shown per entity. The default is 1000, so every column is drawn; setting it to 20 shows the first twenty and marks the rest as truncated. On a schema with fifty-column tables, that one setting is the difference between a readable overview and a wall of text. The same dialog holds the project name, target RDBMS, font and size, and page headers and footers.
IE notation or IDEF1X?
a5m2 draws relationship symbols in either IE notation or IDEF1X. The information in the diagram is identical; only the symbols change.
IE notation puts crow's foot marks at the line ends and is what you'll see in most web-oriented design work and online tools. If the team is new to ERDs, start there. IDEF1X shows up as a required notation on government and large SI deliverables, so follow the client's document standard when one exists.
Within a project, pick one and stay with it. When both appear across a document set, the same line shape ends up meaning different things in different files. What each symbol stands for is broken down in the ERD notation guide.
Exporting the ER diagram as an image
To get the diagram out, use Edit → Create Bitmap. Choose the size and color depth, click OK, and the bitmap goes to your clipboard, ready to paste into Excel, Word or a design document.
To keep a standalone image file, paste it into a document and save the picture from there. The same route works for a wiki page or an issue tracker.
The diagram itself is stored as an ER diagram file that holds the layout and every property, so keep it in the project folder alongside the source if you'll be revisiting the design.
When the diagram is going to be printed
For a printed handout, set the page header and footer in ER Diagram Properties first. With the project name, author and page number on every page, a diagram split across sheets stays in order. The default font size is 6pt, which reads fine on screen but comes out small on paper, so bump it up for anything you hand around.
From diagram to DDL
A finished diagram converts straight into executable SQL. Choose ER Diagram → Create DDL, pick the target RDBMS and options, and click generate; the statements open in a SQL editor. Point that editor at a connection, run it in execute-all mode, and the tables are created.
The forum example above comes out like this.
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE COMMENT 'Email address',
nickname VARCHAR(50) NOT NULL COMMENT 'Display name',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT='Members';
CREATE TABLE posts (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
member_id BIGINT NOT NULL COMMENT 'Author',
title VARCHAR(200) NOT NULL COMMENT 'Title',
content TEXT NULL COMMENT 'Body',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_posts_member FOREIGN KEY (member_id) REFERENCES members (id)
) COMMENT='Posts';
CREATE TABLE comments (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
post_id BIGINT NOT NULL COMMENT 'Post',
member_id BIGINT NOT NULL COMMENT 'Author',
parent_id BIGINT NULL COMMENT 'Parent comment',
content TEXT NOT NULL,
CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts (id),
CONSTRAINT fk_comments_member FOREIGN KEY (member_id) REFERENCES members (id),
CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) REFERENCES comments (id)
) COMMENT='Comments';You can also switch the target RDBMS and generate again. When the same design has to ship as MySQL for development and Oracle for delivery, that's an options change rather than a redraw.
Table specifications in Excel and CSV
The specification lives under the table menu rather than in the ER editor. Table → Create Table Definition writes an Excel file and needs Microsoft Excel 2000 or later installed. If you'd rather have the raw data, Table → Export Table Definition as CSV gives you a CSV to drop into whatever template your team uses.
If you're still deciding which columns the specification needs, How to Write a Table Specification lays out the fields that hold up in review.
Worth knowing
Assign a diagram to a connection. Assigning an ER diagram to a database makes the logical names appear next to each object in the database tree, and they take priority over table comments. On a schema where physical names alone tell you nothing, that turns the tree into something you can read while writing queries.
Compare development against production. Reverse generate both connections into two diagrams and the structural differences are visible at a glance. It's a quick pre-release check for schemas that have drifted apart.
Split by domain. A whole schema on one sheet is hard to read on screen and worse on paper. Keep separate diagrams for members, orders and billing, and use comment objects to explain the relationships that cross between them.
Three ways this fits into real work
Taking on an inherited database: create the connection, reverse generate a few diagrams by domain, annotate each one with the feature it supports, and lower the max rows per entity so the shape of the schema comes through. By the end you can see which tables are central and where the data accumulates.
Designing a new feature: draw the entities and relationships on a blank diagram, use type domains to keep formats consistent, generate the DDL, and apply it to the development database. Bring the bitmap into the review and the conversation moves faster. If the design sequence itself is still new to you, How to Draw an ERD walks through choosing entities and relationships with an example.
Preparing delivery documents: reverse generate the current structure, export the table specification to Excel, and paste the diagram in as an image. Both artifacts need to come from the same point in time, so regenerate them together whenever the schema changes.
FAQ
Is A5:SQL Mk-2 free?
Yes. It's a free Windows program from the official site, and the ER diagram editor is part of the standard download. Reverse generation, DDL generation and image export all work without paying for anything extra.
Which databases can it connect to?
A5:SQL Mk-2 has dedicated connection support for Oracle, PostgreSQL, MySQL and SQLite. SQL Server uses an OLE DB provider, while other compatible databases can use OLE DB or ODBC drivers. Some features vary by DBMS.
Can it generate an ER diagram from an existing database?
Yes. Open Database → Reverse Generate ER Diagram, pick the tables you want from the list, and click the reverse generate button. The same command is on the right-click menu of a connection in the database tree.
How do I save the finished diagram as an image?
Use Edit → Create Bitmap, choose a size and color depth, and the diagram goes to your clipboard. Paste it into Excel or Word, or into any document, and save it as a picture from there if you need a standalone image file.
For an existing database, connect, reverse generate, arrange the result and export it. For a new design, draw the entities and relationships on a blank diagram, generate the DDL, and use the table menu when a specification is needed.
If you already have the DDL in hand, you can also paste it into WorksCove ERD to get a diagram and a table specification from it. The paste-to-result path is covered with screenshots in SQL to ERD, and there is a matching guide for MySQL Workbench.