Solution: Establish a Big-Tent Culture of Quality

Quality simply comprises testing for most software developers. But that’s only quality control and it’s only part of the story. The entire life cycle of software engineering involves quality assurance, which includes three parts:

1. Specifying project requirements clearly and in writing.
2. Designing and developing a solution for our requirements.
3. Validating and testing that our solution matches the requirements.

We need to do all three of these to perform QA correctly, although, in some software methodologies, we don’t necessarily have to do them in this order.

We can achieve quality assurance in database development by following best practices in documentation, source code control, and testing.

Exhibit A: Documentation

There’s no such thing as self-documenting code. Although a skilled programmer can indeed decipher most code through a combination of careful analysis and experimentation, this is laborious. Also, code can’t tell us about missing features or unsolved problems.

We should document the requirements and implementation of a database just as we do with application code. Whether we’re the original designer of the database or we’re inheriting a database designed by someone else, we use the following checklist to document a database:

Entity-relationship diagram

The single most crucial piece of documentation for a database is an ER diagram showing the tables and their relationships.

Several chapters in this course use a simple form of ER diagrams. More complex ER diagrams have a notation for columns, keys, indexes, and other database objects.

Some diagramming software packages include elements for ER diagram notation. Some tools can even reverse-engineer an SQL script or a live database and produce an ER diagram.

One caveat is that databases can be complex and have so many tables that it’s impractical to use a single diagram. In this case, we should decompose it into several diagrams. Usually, we can choose natural subgroups of tables so that each diagram is readable enough to be helpful and not overwhelming for the reader.

Tables, columns, and views

We also need written documentation for our database because an ER diagram isn’t the suitable format to describe the purpose and usage of each table, column, and other objects.

Tables need a description of what type of entity the table models. For example, Bugs, Products, and Accounts are pretty clear, but what about a lookup table like BugStatus, an intersection table like BugsProducts, or a dependent table like Comments? Also, how many rows do we anticipate each table to have? What queries against this table are we expecting? What indexes exist in this table?

Every column has a name and a data type, but that doesn’t tell the reader what the column’s values mean. What values make sense in that column (it’s rarely the full range of the data type)? For columns storing a quantitative value, what is the unit of measurement? Does the column allow nulls or not, and why? Does it have a unique constraint, and if so, why?

Views store frequently ...