This past week, I was able to finish up the project that I referenced in my Co-op Highlights 6 post. This project involved generating an EER Model on the MySQL Workbench and creating a visual diagram showing the relationships of different tables within the database. I used Schema Spy to identify the initial grouping sets and then used the EER Diagram function in MySQL to create the diagrams. Using MySQL, you are connected to the database. When you choose EER Diagram, you can create multiple diagrams within one file or create an individual file for each diagram. The list of tables in the database is visible in the left column and you simply drag over the tables onto the design surface (based on the relationships identified in SchemaSpy). Additionally, you can move the table blocks around to maximize the space on the design area. Relationships between tables are shown by lines that adjust dynamically when you move the tables to different locations.
SchemaSpy is an online tool (Java-based) that analyzes the metadata of a schema in a database. It then generates a visual representation of the database in your browser. You are able to see the hierarchy of tables in the database which shows the child and parent relationships both in a list format and diagram format. Our IT department has used SchemaSpy to help identify the relationships between tables. This EER mapping project is an extension of that.
The last piece of this project is to document my process for creating the diagrams, so that it can be recreated easily in the future, if needed. I will be writing this and sharing it with the other folks in our IT department tomorrow.
What has been interesting is to see the complexity of the database. I work with this database on a routine basis from an end-user perspective – adding names of new guests, registering guests for classes or events, and updating information such as addresses or phone numbers. The web application that the end user sees and works with is just the tip of the iceberg. Seeing the tables and their relationships in an EER Model diagram is like seeing how big the iceberg really is.
EER stands for Enhanced Entity-Relationship model or Extended Entity-Relationship model and was developed to visually display the properties and constraints of complex databases. I’m sure that there are a lot of details in the EER Model section of MySQL that I have yet not explored.