Generating Database from ERD

Once you have designed your database with ERD, you can generate the actual database from it, or to generate the DDL file for manual database creation.

Generating Database from ERD

Select Tools > DB > Generate Database... from the toolbar. This opens the Database Generation window.

Database Generation window
Select Create Database for Generate Database.

Create database
Check and/or uncheck Export to database and Generate DDL. Sometimes, you may want to review and modify the DDL scripts for database creation, instead of having the database be created completely based on your design. If this is what you want, uncheck Export to database and check Generate DDL. If you want database to be created automatically based on your ERD, check Export to database.

Select Export to database
Make sure you have chosen and configured the default database. You can only generate database if you have chosen the default database.

Default database specified
Configure the other generation options. Read the next sections for details about those available options.
Click OK. If succeed, and if you have chosen Export to database, you will see the database schema be created in the chosen database. If you have chosen Generate DDL, you can find the DDL file in the folder specified in the Output Path field.

Overview of Database eneration window

Database Generation window

Option	Description
Output Path	The folder to store the generated DDL files,.
Generate Database	The kind of action to perform, which will influence the outcome. For example, if you select Create Database, a new database will be created base on your ERD. If you select Drop Database, database tables modeled in ERD will all be dropped in database. Create Database - generate create statements only. Update Database - query existing object in database, generate create and alter statement depends on object not exists or outdated in database, or do nothing if database is up-to-date. Drop and Create Database - generate drop statements first, then generate create statements. Drop Database - generate drop statements only.
Schema	Select the schema for database generation.
Export to database	Check this option to create tables in the actual DBMS.
Generate DDL	DDL (Data Definition Language) is a syntax for defining data structure. E.g. CREATE TABLE PHOTO (…); DROP TABLE PHOTO; Very often, you don't have direct access to the production database during designing/development. By using a DDL, you can execute the table create scripts on the production database manually, and to automate the table creation process as part of the database re-initialization routine.
Generate Comment	Generate entity and column description as comments of tables and their columns. (Only available to My SQL, DB2, Oracle, Postgre SQL)
Upper Case SQL	Force the content of the generated DDL to be in upper case.
Formatted SQL	Apply proper line breaking and indentation to make the content looks prettier.
Custom DDL	Click to customize the generated DDL by adding custom comment and/or the DDL to execute before and after the generated DDL. You can include the date and time of generation, and the author name as comment. You can also add custom description that stats the assumption or the DB configuration required for executing the DDL script (e.g. must run in a blank DB). Custom DDL can be added before and after the generated DDL for environment initialization and cleanup.
Generate Individual DDL	Split table creation statements and foreign key constraint creation statements into two files.
Separate Create/Drop DDL	Split table creation and drop statements into two files.
Generate Sample Data	You can enter sample table records for entities. This option enables the generation of sample table records into database. This allows the same team to share a common set of sample data when in development and testing.
Quote SQL Identifier	Words like SELECT, ORDER are known as reserved words. Reserved words are permitted as identifiers if they are quoted in SQL statements. E.g. CREATE TABLE 'Order'... You can keep this option "Auto" or "Yes" to let us add proper quotes for you. However, we don't recommend the use of reserved words. This is to avoid potential errors.
Column Order	By default, we generate CREATE TABLE statement by following the column order presented in ER model, You can enforce the column order by having key and index column created before the other columns. It can be a good practice to have key and index generated first as this may avoid potential problems in data insertion.
Table Charset	Select the table charset to use, such as UTF8, BIG5, GB2312 and LATIN1. Only available for MySQL users.
DDL Extension	Generate the DDL as .ddl file(s) or .sql file(s).
Connection Provider Class	A strategy for obtaining JDBC connections. Just leave it as-is if you are unsure.
Connection	For use with Hibernate (ORM). JDBC - standard Java database API. Datasource - use database connection from application server. JDBC + Datasource - generate two configuration files for JDBC and database.
JDBC	Connection Pool Options - Connect to and disconnect from the database is an expensive operation, using the connection pool to share the opened connection can dramatically increase the application performance. You can deselect the Use connection pool option to disable the use of connection pool. Production - Normally, database connectivity is stored in project and shared among team members in a team environment. If you have your own database connection setting for your environment, you can select Personal and enter the connection URL there. The setting you filled will not be committed to server, which means that you can keep your own set of database connection setting. For details, please read the next section. Database Options - Set and configure database. Note that you need to select a default DBMS in order for database generation to function. Test Connection - Click to verify the connection settings entered.

Overview Database Generation

Personal/Production Connectivity

Database is one of the key components of an information system. In order to keep a system stable, separate databases are used for development and production so that database changes can be made freely. And when tested, changes can be patched to the production database.

Visual Paradigm enables you to share database design through VPository.com, a cloud -based repository of software design projects. This allows development and production environments to work consistently with the same and latest set of database design. Although different environments can share the same database design, it is impractical to have them share the same set of database connection information. For instance, developer Peter may have 'demo' as database name, with 'peter' as login, while developer Mary may have 'dev' as database name and 'mary' as login. The production environment, certainly won't name the database as 'demo' or 'dev'.

Due to the fact that different environments can have their own set of database settings, it is a bad idea to bind the database connection setting required in generating ORM code and updating database along with the design. If that's the case, all users will require a re-configuration of setting for their own environments prior to generating ORM code and/or updating database schema. This is not just cumbersome, but may corrupt important data in database if a wrong connection setting is supplied by mistake. For these reasons, Visual Paradigm supports personalizing the database connection information so that each user can have their own set of database connection setting. To be specific, the personalized setting is stored within workspace instead of project. Therefore, it will not affect any other user and the production environment. It affects only the generated Hibernate files and the database in specific environment.

To use this feature, simply click on the drop-down menu labeled Production, inside the JDBC section. Then, select Personal and enter your own connection URL. Database generation will apply the connection URL you supplied.

Select Personal database setting

Related Resources

The following resources may help you to learn more about the topic discussed in this page.

Generating Database from ERD