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.
