Documenting a Database

by Jun 24, 2020

ER/Studio has two primary use cases; designing databases and documenting databases. In this blog we will discuss documenting a database. We need to be able to document it to understand its contents. We have three approaches to this:

  1. As a Localised Project
  2. As Part of a Broader Data Modeling Initiative
  3. As Part of a Data Governance Initiative

In all three approaches we have two models: one of the information that business users can easily understand, one as a faithful snapshot of the data asset in technical terms.

The first two approaches will use traditional data modeling techniques comprises logical and physical data models.

As a Localised Project

As a localized project we will look at the database in isolation. We reverse engineer directly from the database creating a physical model in technical terms. We use ER/Studio to help us then build a logical model of the data asset with logical names and definitions that make sense to the business community which can be published through the Team Server web product. We can create Naming Standards Templates that allow us to automatically convert unintelligible technical abbreviated names to expanded logical names within the logical model. Attachments allow us to specify business properties so that we can store useful business metadata against the database.

As part of a broader data modeling initiative

As part of a broader data modeling initiative we will maintain a single view of the information of the organization as a corporate logical data model or canonical model. We can then reverse engineer data assets and map them to that single logical model.

ER/Studio has the notion of Universal Mappings which allow relationships to be created across models. When they are published to Team Server the user can explore these relationships to either explore from the logical to the physical "where is this data?" or from the physical to the logical "what does this data mean?", "who owns this data?" or "what rules apply to this data?"

As part of a broader data governance initiative

In organizations with a strong data governance initiative the information model is held in a business glossary, managed by data stewards. In this scenario we may we reverse engineer data assets then classify physical models by mapping to business terms in the Business Glossary.

Within ER/Studio you can classify models against Business Terms using Team Server. Users can then browse the models like in the previous scenario and ask those important questions about data. 

Summary

Nowadays, we find that all three approaches exist in the same organization which presents risk if they occur in silos. ER/Studio helps produce a single unified ecosystem that allows different groups who need to understand the information and data of the organization to collaborate on a single contiguous model. We can exchange the knowledge in this model with other tools that are involved in this ecosystem. This produces a single understanding of the information of the organization and the instances of these as data assets with full traceability between them.

Watch how we do it with ER/Studio

We have produced a series of videos which you can watch available in the Idera Resource Center

Documenting a Database with ER/Studio – Overview
Document a Database As Part of a Localized Initiative with ER/Studio
Document a Database As Part of a Broader Data Modeling Initiative with ER/Studio
Document a Database As Part of a Data Governance Initiative with ER/Studio