What is best practice to organize / normalize database tables?

Question

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.

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.