What is Reverse Engineering?
ER diagrams are a useful tool for understanding and modeling and design of databases.
We normally build a physical database from the ER diagram – this is called forward engineering. But there are times when you want to do the opposite – create diagram from existing database schema. This task is called reverse engineering, as you first have working tool and you create model from it.
Models in MySQL Workbench
Since reverse engineering is converting live database schema into model, we need to understand how models work in MySQL Workbench.
Models are a separate entity from the databases you are connected to and are stored locally on your disk.
This is a MySQL Workbench model window:
Model is top level object used for modeling in MySQL Workbench - it holds:
- schemas that contain tables, views and routines,
- EER diagrams.
Schema is a grouping of database objects - tables, views and routines. It is the same as schema in MySQL database (or other databases).
By default each model has empty mydb schema. You don't need it and it can be deleted.
Model can hold multiple ERD diagrams. Diagram can contain elements (tables, views, etc.) from schemas but are independent from them - each diagram can contain elements from different schemas.
To reverse engineer database go to menu Database and choose Reverse Engineer... option.
Provide connection details to your database and click Next. Wait for the connection and click Next again.
When successfully connected wizard will show you list of available schemas on the server. Select the ones you want to reverse engineer.
Wait for the schemas being read and continue with Next. On next screen you have an option to select object types and filter specific objects. Let's ignore it and import all objects. Click Execute >.
Wait for reverse engineering to take place and when done continue with Next. Final screen shows you a summary of the import. Close with Finish.
Results: the model
When the process ends with success you get a new model (see above for more details) with:
- physical schemas with database objects imported from the database
- default ER diagram with all the tables and views from all imported schemas
Once you have model from physical database you can use it in various ways:
- Print diagram and share it with colleagues
- Put on a wall and look at it while drinking coffee
- Apply changes and push them to the database (forward engineering)
- Use it for better and quicker reporting
- Analyze it before applying any changes to the database