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
A data warehouse is a database that is dedicated to data analysis and reporting. It combines data from multiple operational applications and provides one location for decision-support data. A warehouse should include staging tables — one staging table for each source table or file.
Continue reading Data Warehouses Should Have Staging Tables
Do you use SQL as part of your work? If so, would you like to use it with greater proficiency?
Check out my new free video course on Udemy — Advanced SQL Queries – Subtleties of Joins. In this course we study different ways of phrasing database joins and their trade-offs.
Continue reading Free Video Course: Advanced SQL Queries – Subtleties of Joins
picture by mab7752722 via Flickr
Once you’ve created a data model, how do you document it? You need to deliver the model’s content to others – to business sponsors, other developers, and posterity for purposes that you may not envision. This article looks at options for documenting data models and their trade-offs.
Continue reading Documenting a Data Model
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
picture by londonbonsaipurple via Flickr
Here is a follow-on to our blog from several months ago on “Ten Reasons Why Developers Ignore Data Models”. Paraphrasing, reader Lawrence Hecht asked via Twitter if there is any way to tell from a schema if the developers had used a data model.
Continue reading Ten Reasons Why Developers Ignore Data Models — Revisited
picture by xianrendujia via Flickr
Data warehouses have a much different architecture and different business motivations than operational applications. For example, operational applications manage the day-to-day data needed to support the business. In contrast, data warehouse restructure operational data and place it in a format amenable to data mining and deep analysis. Operational applications rapidly read and write transactions with small amounts of data. In contrast, users only read data warehouses and can have extensive queries involving large data sets running for multiple minutes.
Continue reading Data Warehouse Model Quality
picture by xianrendujia via Flickr
Quality is an underappreciated aspect of data models. The purpose of a model is not just to capture the business requirements, but also to represent them well. A high quality model lessens the complexity of development, reduces the likelihood of bugs, and enhances the ability of a database to evolve. There are both qualitative and quantitative measures of quality.
This is the first of a two-part series. This blog discusses quality for day-to-day operational applications. Next month’s blog will discuss data warehouses.
Continue reading Operational Model Quality
picture by Scott Speck via Flickr
MetaSQL is SQL code that generates SQL code. This is one of my favorite techniques for tapping SQL’s power.
Continue reading MetaSQL