How to dive into an ugly database?

I'm sure many of you are/were dealing with a ugly database. You know, that database that isn't normalized at all, that database where you have to do a large painfully query to get the most trivial data, that database that is in production and you can't change a bit... you know, "that one".

My question is, how do you deal with it?

• Do you try to make a new database?

• You give up and leave it alone?

• What advice can you give?

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

• Backups

• Research (the schema documentation steps I mention above)

• Talk to the former developers

• A bug database

• Source code control

• Talk to the users and/or business owners

• Establish credibility with the users by fixing a few things or making some enhancements

• Create a development environment

• Drop obsolete objects

StackExchange Database Administrators Q#929, answer score: 29