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?
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:
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.
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.
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.
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.
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.
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".
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.
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:
Views, stored procedures will hide internal databases modifications (changes).
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.
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.
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.
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).
Schema Spy is a really nice tool for generating an ERD.
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.
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.
Ah...the Ugly database, The large the enterprise is the more legacy databases that we will find.
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 ;)
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.
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.
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.
