Decipher database schema [closed]

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!

回答3:

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.

回答4:

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.

回答5:

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

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.
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.

回答6:

There is a bit of open-source software out there 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.

回答7:

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.

回答8:

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.

回答9:

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.

回答10:

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.

回答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.

回答12:

This helped me with generating the ER diagrams on MS SQL Server 2012:

MS SQL Server management Studio > File menu > "Connect Object Explorer" Choose your Database node and expand it. under this node you'll find a sub-node called "Database Diagrams" Right click on "Database Diagrams" > "New Database Diagram" > Add tables that you wish to see their columns, relationships, ...

回答13:

Use Visio. If using Vision 2010, you will need to use the Generic OLEDB Provider for SQL Server to ensure that there will be no problems with connecting to the Visio Driver.