What are the best ways to understand an unfamiliar database?

Question

What kind of approaches and techniques can you employ to become familiar with an existing database if you are tasked with supporting and/or modifying it? How can you easily and effectively ramp up your knowledge of a database you have never seen before?

Answer 1

The data dictionary is your friend. Also, try reverse-engineering the database with the reverse-engineering tool on Visio and building your own set of diagrams. Because reverse engineering is interactive - you build the diagrams - it's much more engaging than reading through a data dictionary. The activeness of the process is its advantage and I find it quite relaxing to do this.

Most of the work I do is in data warehousing, where poking around source system database schemas is something of a core activity. I've done this sort of thing on quite a number of occasions and find it works really well.

Visio pro is not that expensive and the Visio modelling engine lets you share a model amongst multiple diagrams. As a bonus, you can add in missing foreign keys in the diagrams and you get a useful set of documentation for the system out the end.

Answer 2

See if the option of a Knowledge Transfer session is available to you, and if so, take full advantage of it.

Also, many DBMS's come with tools that allow you to draw/print the database schema with some helpful information (i.e. foreign keys).

Additionally, (stolen from NXC) you can reverse engineer the database via tools like Visio.

Answer 3

• The first thing I do is create an Entity-Relationship Diagram (ERD). Sometimes you can simply describe the metadata with command-line tools but to save time there are some tools that can generate a diagram automatically.

• Second, examine each table and column make sure I learn the meaning of what it stores.

• Third, examine each relationship and make sure I understand how the tables relate to one another.

• Fourth, read any views or triggers to understand custom data integrity enforcement or cascading operations.

• Fifth, read any stored procedures. Also read SQL access privileges if there are such.

• Sixth, read through parts of the application code that use the database. That's where some additional business rules and data integrity rules are enforced.

---

update: I just read an interesting article "9 Things to Do When You Inherit a Database" with a good checklist.

Summary:

1. Backups
2. Research (the schema documentation steps I mention above)
3. Talk to the former developers
4. A bug database
5. Source code control
6. Talk to the users and/or business owners
7. Establish credibility with the users by fixing a few things or making some enhancements
8. Create a development environment
9. Drop obsolete objects

Answer 4

Put a backup copy on a sandbox server and then start writing and running test queries. I always find a complex system easier to understand if I can get my hands on it and not worry about breaking it.

Also, I like to have The Daily WTF open in a browser window. Taking over someone else's design usually involves a lot of "I can't believe they did {WTF}" moments, and it helps to have somewhere to go where people understand your pain.

Answer 5

In addition to Bill Karwin's ideas, I suggest talking to the users - occasionally users know quite a bit about what their database is used for, especially if they do any reporting from it.

Answer 6

Schema Spy is a really nice tool for generating an ERD.

Answer 7

Bill gave an excellent answer. I would add that I would login to the user interface as a test user and try to understand exactly what the users do with the data. It will help you understand the why behind some of the stored procs or design. Understanding what the data means and is used for is critical to understanding a a database.

If the database is on a business function or subject you are in general unfamiliar with (say it does flight planning and you have previously only worked on financial applications), then ask the users for some reading material on the subject matter or go to the library yourself or search the Internet about the subject matter. Ask the users if there are legal or regulatory issues you need to be aware of. Again some of this subject matter background may explain what seem to be odd design choices.

Answer 8

In addition to discovering the structure of the database, I've found that it's also important to look at the data quality. Once you understand the meaning of each column you can look for any places where there are a lot of missing values. As you become more familiar with the data you can also examine where there are inconsistencies between the values in different columns.

Answer 9

http://code.google.com/p/database-diagram/

reverse engineer the DB into a diagram

Answer 10

I like to fire up a query profiler and watch what goes by on a production system. Gives me some idea of what tables are 'hot' and the kind of queries there are against them.