Decipher database schema

Question

I've recently inherited the job of maintaining a database that wasn't designed very well and the designers aren't available to ask any questions. And I have a couple more coming my way in the near future.

It's been tough trying to figure out the relationships between the tables without any kind of visual aid or database diagram.

I was wondering what tools are recommended for this. I know about Visio, but I was hoping there were some good open source/freeware applications out there. I don't need it to change the database at all. Just read it and create some kind of visual aid to help me understand how things are laid out and try to figure out what the designer was thinking about how the data should relate.

---

Additional answer data: SchemaSpy was the kind of thing I was looking for, but having not done a lot with the command line in ages, I opted to use SchemaSpyGUI. There was also some configuration to get used to since I don't work with Java much, but the end result was what I was looking for (on open-source replacement for Visio's ER diagrams).

Answer 1

What DBMS (Database Management System) are you using? Many modern DBMS's like SQL server and Access can create an E-R diagram for you.

Microsoft Visio is an excellent tool and can reverse engineer SQL from any datasource.

DDT (Database Design Tool) can reverse engineer from raw SQL on windows and is very lightweight (very small free download).

MySQL Workbench is one of the more popular MySQL tools and has a freely downloadable version.

SQLFairy can do the same for MySQL on Linux.

Answer 2

I use mysql workbench (http://www.mysql.com/products/workbench/) for mysql databases. You can attach the workbench to your database and it will draw the ER digram for you.

Answer 3

Using pgsql/win32 I found the easiest solution was to write a perl script that made use of Graph::Easy from CPAN. Query the database for foreign key relationships, make a directed graph with tables as nodes and FK relationships as links. If this is your setup, I can post the code.

Answer 4

There is a bit of open-source software out thee but Visio Professional's tool for reverse-engineering database schemas is quite good because it de-couples the process of reverse-engineering and diagramming. I use this a lot because it tends to be readily available at most sites.

One nice feature of visio is that you can reverse engineer and then construct your own diagrams from the reverse-engineered schema. Doing this is a very good way to explore the schema and understand it as you are doing this work as a part of interactively building a reference document for the schema. I've used this technique to reverse engineer everything from Activity Based Costing Systems to Insurance Underwriting Systems, typically without much help from the vendor. Tinkering about with Visio diagrams is quite relaxing.

Between this and a little hypothesis testing about FK relationships (If the FK is not physically present on the table) you can make sense of quite complex schemas. I've found this diagramming approach makes Visio a head-and-shoulders leader because you can easily interact with the reverse-engineered model in a fairly convenient way. You can fill in missing foreign keys, build subject area diagrams and add annotations on the diagrams. The interactivity of this process makes it a good learning tool.

This is a somewhat subjective view but the interactivity works very well as a learning proces for me and it's by far my preferred approach. Most sites won't begrudge you the £300 or so for a license - if they don't already have it available. The only site I ever worked where they had to get it in was because they had Visio Standard instead of Pro. I asked nicely and the PHB signed it off.

Answer 5

I like to try and see if the applications that use the database have ways of logging the SQL they use (or the DB backend itself, but that tends to be less tractable). Getting a feel for what requests performed on the database helps you concentrate on the important tables.

As with most things, the 80/20 rule applies here: 20% of the tables will do 80% of the interesting stuff. Once you've figured them out, a diagram is rarely necessary.

Answer 6

Try DBVis - download at http://www.minq.se/products/dbvis/ - there is a pro version (not needed) and a open version that should suffice.

All you have to do is to get the right JDBC - database driver, the tool shows tables and references orthogonal, hirarchical, in a circle ;-) etc. just by pressing one single button. Enjoy!

Answer 7

Try SchemaSpy. I ran it against a rather complex database and I was quite pleased by the result, with advices on optimization.

Answer 8

dbdesc is not free, but I've heard very good things about it. It works with several of the major databases out there.

I have been lucky in that I haven't had to decipher other people's database schemas yet. I have use a set of templates that come with CodeSmith.

Answer 9

Look at the primary key foreign key relationshsips that have been set up as a starting place.

Since a database without existing diagrams, may not have relationships set up formally, I look at the table structures and names and make my best guesses as to what might be related to what, then dig into the structures to see if there are obvious (but undefined) foreign keys. I look at the stored procs to get an idea as to how the tables are joined and what fields are being queried on.

While automated tools to figure out the database can be spiffy, I find that when I really dig into the details of the database myself, I end up with a much better understanding than I can get from any picture created automatically.

Answer 10

Firstly, may I say that I feel your pain!
Here are a couple of my tips:

1. In general, a tool will only be helpful if the designers have correctly defined all the primary and foreign keys, so be aware that a tool might not pick up all the important relationships.
2. The most useful thing is to see what queries are being performed by the client code. This will tell you not only what relationships exist, but which tables and relationships are the most frequently used - that's where you'll want to concentrate your effort.

Answer 11

I have some pretty good experience with Aqua Data Studio for reverse engineering a DB schema. It is very feature rich and supports even more exotic databases like Informix or Sybase.