ER/Builder - Free Database Modeling & Schema Generation

Franco Martinig, Martinig & Associates, http://www.martinig.ch/

ER/Builder is a free visual tool for database modeling and schema generation using an entity relationship (ER) approach. It offers also a reverse engineering feature for some databases. The currently supported databases are Oracle, SQLServer, PostgreSQL, MySQL,MariaDB, Interbase and Firebird.

Web Site: http://soft-builder.com/en/erbuilder-data-modeler/

Version tested: version 1.0 in December 2018

System requirements: Windows 7, 8 and 10

License & Pricing: Freeware currently, commercial version will follow

Support: through e-mail at http://soft-builder.com/en/contact/

Tutorials: http://soft-builder.com/en/tutorials/

Product Status and Roadmap

ER/Builder is a new data modeling product that has been developed since 2017. In earlier versions, this tool was known as ER/One. According to the development team, a commercial version is scheduled for early 2019 that will provides more features than the free version. Among the differences between the free and commercial versions, you will find: reverse engineering for Oracle and SQL Server databases, model versions management, converting the DBMS of a model, models merge, multi diagram, model documentation generation. SQLite support should be added in both free and commercial version.

Installation

The installation of ER/Builder is as simple as running the downloaded setup file on your computer. The Options panel allows you to choose where you want to store your data, which would be the default database engine managed and some choices on how you want to visualize your diagrams (colors, etc.).

Working with ER/Builder

ER/Builder works around the concept of projects. If you don't want to start your discovery of the tool with a clean sheet, the website provides a sample of existing projects with classical sandboxes of data modeling (airline reservation, product catalog, etc.) that you can use to start being familiar with the tool. There are also some tutorials available on YouTube. I would strongly recommend to watch them before you start using the tool as some features (like creating relationships) were initially not so intuitive to me. In overall, I would say that a little bit more of a formal help will be... helpful, for instance on how to connect to a local database. However, the concepts are simple and with a little bit of trial, I managed to do it.

(click on figure to enlarge)

Data Modeling

ER/Builder modeling features are clearly oriented towards database generation, but leaves some space for true data modeling, allowing to separate for instance the attribute names from the table column name. The layout is build around a set of pages (6x4), so you can see where your items will be if you print your model. When you insert an entity / table, the screen provides all the elements to define it, including the indexes, keys, triggers. etc. The items creation SQL script is then updated in real-time. You can add simple relationships and reflective relationships. Adding many to many relationships will automatically create a new table referencing the primary keys of the two entities involved. Adding a reflexive relationship automatically adds a foreign key to the table. Undo and redo features allow you to quickly modify your diagram. You can also copy entities if you have tables that have similar structures. ER/Builder also offers a Model Validation option, but running it on my toy model didn't provide special information, producing only warning messages about missing descriptions.

(click on figure to enlarge)

Database Schema Generation

The database schema generation is build in real-time when you create your tables, relationships and other items like indexes. You can therefore directly check the SQL translation of your modeling efforts. To generate the full model, you use the Generate DDL option where you can choose the type of items you want to generate (indexes, triggers, etc:) You can save this to a file and you can also have a "preview" option where you can check your complete DDL before saving it or applying it directly to the database. To create directly the tables and other items in the database that you are connected to, you use the Generate Database option. In this case, you can choose also to drop (or not) existing data definitions. An error message will tell you if you are trying to overwrite an existing data definition.

/*============================================================================*/
/* DBMS: MySql 5*/
/* Created on : 13.12.2018 15:30:40                                           */
/*============================================================================*/


/*============================================================================*/
/*                                  TABLES                                    */
/*============================================================================*/
CREATE TABLE `EMPLOYEE` (
  `EMPLOYEE_ID`         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `FIRST_NAME`          VARCHAR(20),
  `FAMILY_NAME`         VARCHAR(40),
  `BIRTH_DATE`          DATE NOT NULL,
  `GENDER`              VARCHAR(1) NOT NULL,
  `HIRING_DATE`         DATE NOT NULL,
CONSTRAINT `PK_EMPLOYEE` PRIMARY KEY (`EMPLOYEE_ID`)
)
;

CREATE TABLE `DEPARTMENTS` (
  `DEPARTMENT_ID`     INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME`              VARCHAR(40),
CONSTRAINT `PK_DEPARTMENTS` PRIMARY KEY (`DEPARTMENT_ID`)
)
;

CREATE TABLE `WORKS_FOR` (
  `EMPLOYEE_ID`       INT(10) UNSIGNED NOT NULL,
  `DEPARTMENT_ID`     INT(10),
  `START_DATE`        DATE NOT NULL,
  `END_DATE`          DATE
)
;

CREATE TABLE `SALARY` (
  `TABLE4_ID`         INT(10) NOT NULL,
  `TABLE4_ID_1`       INT(10),
CONSTRAINT `PK_SALARY` PRIMARY KEY (`TABLE4_ID`)
)
;

/*============================================================================*/
/*                                 INDEXES                                    */
/*============================================================================*/
CREATE UNIQUE INDEX `IDX_PRIMARY_INDEX_EMPLOYEE` ON `EMPLOYEE`(`EMPLOYEE_ID` ASC)
;

CREATE  INDEX `IDX_WORK_FOR_INDEX_WORKS_FOR` ON `WORKS_FOR`(`EMPLOYEE_ID`,
`DEPARTMENT_ID`,`START_DATE`,`END_DATE` ASC)
;

/*============================================================================*/
/*                               FOREIGN KEYS                                 */
/*============================================================================*/
ALTER TABLE `WORKS_FOR`
    ADD CONSTRAINT `FK_DEPARTMENT_HAS_EMPLOYEES`
        FOREIGN KEY (`DEPARTMENT_ID`)
            REFERENCES `DEPARTMENTS` (`DEPARTMENT_ID`)
 ;

ALTER TABLE `WORKS_FOR`
    ADD CONSTRAINT `FK_EMPLOYEE_WORKS_FOR`
        FOREIGN KEY (`EMPLOYEE_ID`)
            REFERENCES `EMPLOYEE` (`EMPLOYEE_ID`)
 ;


ALTER TABLE `SALARY`
    ADD CONSTRAINT `FK_SALARY_RELFEXIVE_RELATIONSHIP`
        FOREIGN KEY (`TABLE4_ID_1`)
            REFERENCES `SALARY` (`TABLE4_ID`)
 ;

Database connections

You can connect to your local database by simply (at least for the MySQL version that I use) defining your database system, user/password and database name.

Documentation

You can currently print your model or export it as an image. Exporting it as an image is however not limited to the actual space that your diagram uses, but this produces a full view (the 6x4 A4 pages), with the pages separation. Not the most easy and useful way to integrate it in another document.

Reverse Engineering

I tried this with the MySQL database of a tool that I tested previously. ER/Builder allows to connect easily to an existing MySQL database and import the table and columns definitions. From this starting point, you can drag the tables to your model and begin creating the relationships between them or modifying their properties.

Conclusion

Even if it is still under development, ERBuilder is a free tool that provides currently a large of features for software developers that want to manage the data model and generate databases, especially if there are different targets in development and in production. The tool is clearly more oriented towards the needs of physical databases, but the basic visual editor should satisfy a majority of the needs for data modeling, even if it lacks some of the visual relationship notations of the Entity Relationship model for cardinality (zero, one or many) that precise how entities are involved in the relationship. On the negative side, there is currently no documentation that will help you getting directly to the feature that causes you some issues, besides some videos.

---

This article was originally published in December 2018

Click here to view the complete list of tools reviews