Information systems keep changing at all time. Very often, those changes involves the introduction of new business rules, which requires storing new data, and thus requires the introduction of new database tables, columns and relationships. Since database is an integral part of an information system, to modify database structure we cannot just throw away the old one an re-create everything from scratch. Instead, we need to take good care of existing data and structure to ensure the system will remain working properly both throughout and after the transition.
Visual Paradigm supports the patching of design changes to existing database. We compare the differences between your design and a database, and patch the differences to your database accordingly. Throughout the process, your existing data will remain intact. You don’t need to worry about data lost or system stability.
Patching Database from ERD
- Select Tools > DB > Generate Database… from the toolbar. This opens the Database Generation window.
- Select Update Database for Generate Database.
- Check and/or uncheck Export to database and Generate DDL. Sometimes, you may want to review and modify the DDL scripts for database patching, instead of having the database be altered completely based on your updated design. If this is what you want, uncheck Export to database and check Generate DDL. If you want database to be patched automatically based on your ERD, check Export to database.
- You can check Include drop table/columns that not exist in ER model if you want the patching process to drop tables and/or columns that do not exist in your ER diagram. Note that dropping a column or table will disregard the data contained. Therefore, think carefully if you want to enable this option.
- Make sure you have chosen and configured the default database. You can only patch database if you have chosen the default database.
- 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 be patched. If you have chosen Generate DDL, you can find the DDL file in the folder specified in the Output Path field.
Overview of Database Generation
| Option | Description |
|---|---|
| Output Path | The folder to store the generated DDL files, source files and library files (if any). |
| Generate 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. |