Reverse Engineer a DB [1]

In the next few posts I will tinker with database modelling and reverse-engineering tools. I plan to use: Visio, Visual UML, Erwin, Toad, Oracle Designer, DB Explorer, Squirrel, and maybe some more. A Google search for "database modeling tools" returns quite a list. But, one step at a time; first thing we need is a database.

A few years ago, I put together a simple code repository for one of my Excel VBA projects. The idea was to store separate chunks of code in a database and dynamically assemble the project code as required. The model I chose follows a typical book layout: sentence, paragraph, chapter, book. Originally I designed it in Visual UML — see the diagram — and implemented it in MS SQL Server.

Entity (Table) Book Equivalent Excel VBA Object
Snippet Sentence Chunk of code, one line minimum
Block Paragraph Procedure, Function, Comment block
Module Chapter Code module, Class, Form
Project Book VBA Project

 

Snippet is a chunk of code, minimum one line, up to a whole procedure. Block has many snippets, one snippet can belong to many blocks. For example, if a block represents a subroutine, a snippet may be ‘End Sub’. Block_mm_Snippet is a many-to-many relationship table and also holds the position of a snippet within a block. The Module table represents a code module, a class or a form in VBA. One module can have many blocks, one block can belong to many modules — keep in mind that not all modules have to belong to the same project. A project has many modules, a module can belong to several projects.

Although it worked fine for me, the model is far from ideal. It is obviously missing a few constraints. Snippet TXT field should be unique, so should relationship keys in Module_mm_Block and Project_mm_Module tables to prevent repeating a procedure within a module, or repeating a module within a project. I could also add version fields to Block, Module, and Project tables. It will be interesting to see how changes can be made in the physical or logical layer in a diagram and deployed back to the existing database.

To be continued.