Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I manage group that uses a database that is about 12 years old.

There are more than 200 tables / views that have been created by various people over the years, many of them no longer with us.

Most of the table and field names are descriptive, but with the number of table/fields we've lost track of what is used for what. Consequently, whenever a new process is created, we create new tables rather than looking for the data in existing tables so the problem just gets bigger.

I'm looking for suggestions on documenting, normalizing and getting the multiple tables under control. Any comments, recommended books, articles, etc would be appreciated.

share|improve this question
    
Any Recommendation on best practice to document the schema? –  Jeff Dec 3 '12 at 23:46

1 Answer 1

Database refactoring is worth reading.

I think a sane procedure would follow these steps.

  • Audit
  • Analyze
  • Deprecate
  • Revoke
  • Remove
  • Kill

Audit

Determine which tables, views, stored procedures, etc., are still being used. Determine which applications are accessing your database. There's more than one way to do that, and the best way depends in part on your dbms.

Auditing can hurt performance. This will take time; faster hardware might be justifiable.

Analyze

Analyze the results of your audit. Distinguish currently used database objects from seemingly unused database objects. There's no such thing as "definitely unused database objects"; you might have a table that's used only once a year for some kind of accounting process.

Deprecate

Decide which objects to end support for, and deprecate them. Communicate this change both widely and loudly. Remain open to changing your mind when you find you've stepped on someone's toes.

Revoke

Revoke permissions on formerly deprecated database objects. If you've overlooked an application or user, they'll eventually complain. You might want to approach this in several steps, spread out over time.

Remove

Remove the formerly deprecated database objects in such a way you can easily replace them.

Kill

Finally, kill the unused database objects. Breathe easier.

Each step takes time. Allow more time than you think you need.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.