Category Archives: Reverse Engineering

Revised ERwin model after DBRE

A Database Reverse Engineering Case Study

In a blog last year we discussed database archaeology, which is another name for database reverse engineering. Reverse engineering is the inverse to normal development. We start with an application and work backwards to understand the software and infer its content.

This month we’ll take a further look at database reverse engineering, from the perspective of a simple case study. We’ll reverse engineer the database beneath WordPress and populated with a snapshot of the data for this website. The case study illustrates mechanics and the kinds of insights that reverse engineering can provide.

Continue reading A Database Reverse Engineering Case Study

A suggestive picture for the concept of database grading.

Database Grading, Part 2: Database Models

picture by Greg via Flickr

This article is the second in a series of two blogs that present our grading scale for database quality. We assign separate grades for the quality of a database design (previous blog) and the underlying model (this blog). The design grade measures the quality of the database syntax. The model grade measures the quality of the semantic concepts underlying the database. Applications can have different design and model grades.

Continue reading Database Grading, Part 2: Database Models

A suggestive picture for the concept of database grading.

Database Grading, Part 1: Database Designs

picture by Greg via Flickr

While working on database projects, we often find ourselves doing reverse engineering. Reverse engineering is the inverse to normal development. Developers start with an application and work backwards to understand the software and infer its intent. Reverse engineering can apply to a variety of artifacts, such as hardware, programming code, and databases. Our focus here is on relational databases.

There are many reasons for database reverse engineering. One reason is to assess software quality. For example, you may want to assess the quality of a vendor product or an internal legacy application. Information systems revolve about a database, so you can use database quality as an indicator of software quality.

This article is the first in a series of two blogs that present our grading scale for database quality. We assign separate grades for the quality of a database design (this blog) and the underlying model (next blog). The design grade measures the quality of the database syntax. The model grade measures the quality of the semantic concepts underlying the database. Applications can have different design and model grades.

Continue reading Database Grading, Part 1: Database Designs

Runners as a metaphor for agile

Agile Techniques Are Helpful with Databases

picture by Kanban Tool via Flickr

I’ve been practicing agile database techniques for about twenty years now. My use of agile techniques didn’t start as an explicit plan. Rather it evolved over time as I was working on consulting projects. It made sense to look for ways of working faster and better and with greater customer interaction.

I can think of at least three kinds of agile database techniques.

  • For data modeling.
  • For data warehouse development.
  • For database reverse engineering.

Continue reading Agile Techniques Are Helpful with Databases

Database Design Errors

Miscellaneous Database Design Errors

picture by strange little woman on stream via Flickr

This is the third and final blog in a series about database design errors. The two previous blogs addressed primary key and foreign key errors as well as confusion with many-to-many relationships. Now let’s discuss several other design errors. Our coverage is clearly not complete, but these are common mistakes that are found in practice.

Continue reading Miscellaneous Database Design Errors

Database Design Errors

More Database Design Errors – Many-to-Many Relationships

picture by strange little woman on stream via Flickr

A many-to-many relationship is an intersection of two entities. A person may own stock in multiple companies. A company can have multiple stockholders. The combination of a person and a company stock has an attribute of the number of shares owned. A many-to-many relationship depends for its existence on the underlying entities.

Relational databases and SQL have robust support for many-to-many relationships. Referential integrity keeps relationships consistent with their underlying entities and SQL can efficiently combine them in queries. In contrast, programming languages lack support for keeping references consistent with referents and providing easy traversal. As a result programmers have to use workarounds to handle many-to-many relationships in their code. Sometimes they make the bad decision to contaminate a database with their programming representation.

Continue reading More Database Design Errors – Many-to-Many Relationships

Database Design Errors

Primary Key and Foreign Key Errors to Avoid

picture by strange little woman on stream via Flickr

Few IT applications are truly grass roots. Rather most new applications are intended to replace or complement existing applications. As a result, we often find ourselves working with legacy databases. This article is based on 50 legacy databases that we’ve studied over the years. In our experience about 20% of database designs are clean and problem free. The remainder have design errors. This blog discusses primary key and foreign key errors. Subsequent blogs will address other design errors.

Continue reading Primary Key and Foreign Key Errors to Avoid

Mayan ruins

Database Archaeology

picture by Abby Sasser via Flickr

Few database projects start with a clean slate. Many operational applications have legacy databases as a source of data and ideas. Analytical applications have the operational databases that are feeding data. Developers often encounter existing databases that are poorly documented and need to figure them out. We use the term database archaeology to refer to the study of database artifacts.

Continue reading Database Archaeology