Understanding a Database

I ran across a post that asked a good question, one which I want to ask you today: how do you learn about a database?

I’ve run into quite a few databases in my career. Some were third party systems, like Dynamics and JD Edwards World. Some were databases that custom designed and built by developers and database modelers of widely varying skills. Some were well built in order to normalize data and define referential integrity, and other databases were put together in a piecemeal fashion over time, lacking keys and consistent naming. I’ll leave it to you to guess if there were more of the former or the latter.

When a developer or DBA comes across a database, what’s the way that they can decode what fields and columns mean? Certainly names help at times, especially when the purpose of the database is understood, but all too often the names don’t quite make sense. This is especially true in many vendor databases. The one common theme I’ve seen in many databases is that there is no data dictionary provided by anyone.

Trying to understand a database has been a trial and error detective task for me in the past. Usually this starts when I need to do some work that is requested by users: write a report, change data, etc. In these cases, I often will ask users to access certain data related to the change from their application while I run Extended Events and note which entities are accessed. I can then start looking for data elements, and note which columns might be mapped to which fields in an application.

Often I’ve built a data dictionary of sorts outside of the database using something like ErWin, ER/Studio, or another tool. That has been somewhat flawed, as it’s hard to share the information with others. These days I think I’d make extensive use of Extended Properties to document what I learned, so that all my knowledge is available for anyone else that needed to work on the system. They can just look at the properties for various entities.

If you’ve got other methods, share them with us today. I’m sure there are plenty of DBAs and developers out there that would like some tips and tricks for decoding a database design.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.

2 Responses to Understanding a Database

  1. Kurt Zimmerman says:

    Steve;
    You have touched on a subject that I’ve spent the last 10+ years working with preexisting databases either on-prem or third-party. I have come to the conclusion there is no pure design however a variation in design based upon many fingers in the pie. The only really good design databases that I’ve encountered are the ones that I have been able to see from design to deployment. EVERY third party database structure has been a computerize in normalization.

    Other things that I take into account is getting the overall dynamics of a database. I want to know which tables grow at faster rates than others. I want to know which tables are being accessed most frequently as well as which SPROCs are being utilized.

    The dynamics of a database will allow me to focus on where to expect issues/problems due to poor running queries, deadlocking, etc.

    One of my larger databases that I manage has over 1000 tables and several 1000 sprocs & functions. After understand the dynamics of a database I have been able to focus in some of the problem areas, introducing necessary indexes and statistics to help these process along.

    I have found that this approach allows me to get up to speed with a database that is performing poorly allowing me to attack some of the main issues. In other words, it allows me to identity the low hanging fruit to address issues much quicker.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.