Challenge

Red Book is running a very large legacy database on MS SQL Server. After so many changes and additions within almost two decades, the team decided to create an ERD to learn more about the database itself. During those years, they have developed a dozen different tools and it became clear that it is very crucial to have a central location for documentation. The last ERD for this database was created in 2001, and since then, there have been made plenty of changes. Having only one or two people knowing how the entire structure works was not sustainable. The IT management team at Red Book Solutions decided to change that.

A Data Engineer was tasked to learn even more about the database and make it understandable for other developers. The database has over 500 tables, each with an average of 50 columns, and includes over 1,000 other objects such as views, functions, and stored procedures.

The first try was to use SQL Server Management Studio to make ERDs and export them as PDFs, but the live diagrams couldn’t be shared easily and PDF outputs were quickly outdated and un-readable.

Solution

First, after importing the database schema into Dataedo, the Data Solutions Engineer grouped objects into modules which reflected existing user interface modules. This made it easy for developers to understand the relations between tables with different names and columns.

The legacy database had few foreign key constraints to create diagrams—it was barely enough to define relationships as required (within Dataedo repository). To make this task manageable, Dataedo delivered a script that generates foreign keys based on the names of columns (editorial note: this functionality will make its way into the core product). The Red Book Solutions’ Engineer had to make slight adjustments to work with its database, and it made nearly 3000 connections all at once. Within a short period of time, the database was published to the IT team.

Having the tool needed for this endeavor, this Red Book Solutions Data Engineer created a diagram for each of the modules defined in the repository.

Documentation included links and images what made documentation more convenient and informative.

Entire documentation was exported to an internal (shared & secured) website where it can be easily browsed by Red Book staff and learn about the database.

Results

The first result of this approach was a widespread surprise at the size and complexity of the database. This was easily managed, and now, there is documentation of the database (fields, table relationships, and diagrams) in a central repository that the Red Book Solutions team can conveniently browse in an internal website.

There are at least five users who benefit from viewing the published website to gain some knowledge, which has already definitively happened.

On the entire task fraction of the time was spent on what was expected.

Summary of products created during implementation:

1. Definition of foreign keys
2. Visualization of a database schema in a number of ER diagrams
3. Description of each database field
4. HTML database documentation shared internal website

Key benefits:

1. Knowledge about the database was captured and preserved in the organization
2. Improved efficiency of writing accurate code
3. Saving time on documentation effort
4. Easier sharing of knowledge
5. One source of reference about the schema of the database
6. Easy access to convenient database documentation for the entire team
7. Understanding and documentation of database table relationships (nearly 3000)

The future

The number of users that use this documentation may grow in the future as Red Book Solutions is working with its HotSchedules offices in Austin and San Francisco where other application development takes place. In addition, they looking into expanding the use of Dataedo to include other databases in order to share information effectively.