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'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?
share|improve this question
13  
Alcohol. Lots of alcohol. It's the only good way about it that I've found. Whatever you do, don't listen in on the telecons with the "designers" of the database once a week for two years, or you'll want to either bludgen yourself or them to death. –  Joe Jan 27 '11 at 3:14
add comment

20 Answers 20

  • 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
share|improve this answer
add comment

This is not always possible, but one thing that has worked for me in certain situations is to replace some of the tables with views. You can then tidy up the tables underneath and in some cases eventually dispose of the views. As I said, only works in some cases.

share|improve this answer
    
In Oracle Materialized Views can also help with this. –  Leigh Riffel Jan 27 '11 at 19:22
    
+1 For using views. Views are your friend. –  Cape Cod Gunny May 12 '11 at 18:32
add comment

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.

share|improve this answer
    
This technique, combined with Bill Karwin's, saved my bacon when we inherited a very large, disorganized product from one of our clients' former developers ... –  John Rudy Nov 10 '08 at 16:57
add comment

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.

share|improve this answer
    
Good point! A comment from fallen888 also advocates knowledge transfer. –  Bill Karwin Nov 10 '08 at 17:25
add comment

I deal with a very ugly one for a vendor's software, that aside from making suggestions, I can't do much to change it. I'm always pushing to get things changed, but since it is outside of my control, I'm stuck with the junk.

One of the things I quickly started using, since the database has absolutely no relationships, is a general Name query for the schema:

--Find Column named like 'blah' in a specific table
SELECT O.NAME, O.ID, C.NAME, O.XTYPE
FROM SYSOBJECTS O LEFT JOIN SYSCOLUMNS C ON O.ID=C.ID
WHERE C.NAME LIKE '%SearchFor%' AND O.XTYPE IN ('U','V') AND O.Name like '%TableName%'
ORDER by O.Name

or

--Find all Columns in DB with name like 'blah'    
SELECT O.NAME, O.ID, C.NAME, O.XTYPE
FROM SYSOBJECTS O LEFT JOIN SYSCOLUMNS C ON O.ID=C.ID
WHERE C.NAME LIKE '%SearchFor%' AND O.XTYPE IN ('U','V')
ORDER by O.Name

Since some of the tables have too many poorly named columns, and far too many columns to look through to find what I might be able to use to form relationships between table.

I know this doesn't help much in the redesign part of the question, but it's very helpful in the understanding and deciphering of the bad schema.

share|improve this answer
add comment

Depends on how ugly it is, and how much control you have over the design and what interacts with it. I've had to interact with a number of ugly databases over the years at my current job, and here's how I've dealt with them:

Employee Data

There's the database that hold employee data. Its a vendor database, so I have no control over it. (Un?)fortunately, I don't have direct access to it. I get a DTS dump every morning.

Best I've been able to manage is to write a script that scrubs the input from the morning dump (yes that word choice was intentional) and migrate it into a more useful format, and work from the scrubbed data.

Even if I could change it, I probably wouldn't - only because there are a large number of other programs that rely on it being set up the way it is, and I can't force a change in them.

Online Training Data

This was a mess of my own design. I built it fresh out of college with no mentor to help me... I've since been fixing it a little bit at a time. Since I control the only program that accesses the data, as I upgrade portions of the site I'll "upgrade" the configuration of the database. I'll write a transformation script and test it vigorously on a copy so I can ensure that all the changes that need to be made get made.

Its been a long process, but its coming along nicely.

Classroom Training Data

My pilot project has been integrating data from 3 different databases, all designed slightly differently by my predecessor... who was a nurse educator that took a programming class or two.

That's been another slow process. Since I have full control over the programs that access the data, I've been changing it bit by bit like the online training data.

In retrospect, this would have been a prime candidate for starting clean... hind sight is always 20/20.

In the End...

I don't know how helpful this has been, and I can elaborate more (to a point, company legal yada yada and all). The final answer is "It Depends".

share|improve this answer
add comment

So after reading all of your answers, I give you mine:

First I look up for the "Master Table", then, with pen and paper, I start mapping the relations with other tables, after that, if there's some app code to look at I start making some raw sketches on how the data flows.

After I get a nice picture on how the db work I just beggin checking for places where to change things. That's it.

I don't know why but I prefer paper over any database modeling software.

share|improve this answer
    
I too found that I'm too often going to paper and only after that I go to the tools. Thumb up! –  Marian May 6 '11 at 19:29
add comment

Because of using it by external application you cannot change database "interface". I don't know what type of database you are using (oracle, mysql, mssql), but I see this as one of ways:

  • building database interface by using so types of objects as view and stored procedures.
  • step by step refactoring (normalizing, field renaming...)
  • changing client's application (if required)

Views, stored procedures will hide internal databases modifications (changes).

share|improve this answer
add comment

SchemaCrawler is my database discovery tool that has a couple of features that make it easy to explore an ugly database. SchemaCrawler has a "grep"-like functionality, that allows you to search for tables and columns using regular expressions. For example, you could search for tables and columns with "ACCOUNT" as a part of their name, and they would probably be related in some way.

SchemaCrawler also infers foreign key relationships, even where there are no foreign keys. It does this by finding "weak associations" using common naming conventions, such as tables are names are usually plurals, but column names are not, and column names may have a prefix of _ID. You can find related tables using these inferred relationships.

share|improve this answer
    
Cool.. There is one more.. One from Oracle's SQL Data Modeler has the reverse Engineering where you could find the Physical and Logical designs ERD's to understand the structure of the Database. –  azzaxp Feb 14 '12 at 7:41
add comment

If it is a vendor database (and I've seen some really bad ones) all you can do is complain to the vendor about it.

For applications which are built in house it usually just takes some education to the developers and you can start getting the schema changed so that performance improves. It takes time, and it is usually a slow process.

In my experience building a new database isn't really an option, as moving hundreds of GBs or TBs of data isn't all that feasible.

Leaving it alone also usually isn't an option. As the amount of data in the database grows performance will get worse and worse (granted by the time I see the problems they are usually pretty damn bad). Eventually the users won't be able to use the application because performance is so bad.

share|improve this answer
add comment

It depends on how you have to interact. For usage scenarios where batching is acceptable, I have quite often found it most cost effective (in terms of development time and thus cost to the client) to batch the data off to a more friendly structure and work against that.

share|improve this answer
add comment

If you can segment the problem into problems that you can wrap your brain around, you can attack them one at a time. Sometimes, just knowing that there's one table that isn't all buggered up can give you a beachhead to work from. This way, you extend your "clean spot" to encompass more of the database in chunks.

share|improve this answer
add comment

If you have Visio (part of Microsoft Office) you can try the reverse engineer function. It's not pretty, but it will at least give you a start (at a fraction of the cost of "real" tools like Rational Rose).

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Ah...the Ugly database, The large the enterprise is the more legacy databases that we will find.

  • Tuning for performance people don't complain about such databases until they find performance issues. So in our organization we identify individual queries and fine tune them as a patch.
  • Limiting data now we know where the stinky garbage so try avoiding the data flow through such databases. Create staging databases and redirect your data to those tables to start with and use the old ones as data dumps.
  • Avoid Data hoarding Archive / truncate old data that is not required any more. There should be a team that decides how long the data is required in a database. After that you can move it to flat files or even to tape drives.
  • Phase it out once you can achieve the data redirection and truncation. Convince the other teams to start using the new database.

It doesn't work always but if we don't put in effort it is only going to get worse. I try to redesign databases along with the applications, it might add more work for me with data migration but performance is a magic trick that I always pull out of my hat.

Good luck with your ugly girl friend ;)

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.