Requirements Smells make SQL Smells!

 

Your SQL smells because your Requirements smell!
Your SQL smells because your Requirements smell!

Recently I read an article by Phil Factor on the subject of “SQL Smells”. Phil (apparently not his real name), identifies a number of “smells” which he thinks indicate that a database design or SQL code needs to be reviewed. He classifies some of these as “Problems with Database Design”. I would go further and say some of them are problems with database requirements! In other words, your SQL smells because your Requirements smell!

“Requirements Smells cause SQL smells!”

I no longer claim to be a “Developer” and I have never claimed to be a DBA (Database Administrator), though I have found myself in the position of being an “accidental DBA”. The thought that Requirements could smell bad concerned me.

This realisation made me think about problems with Requirements in general and problems with databases in particular. It is better to avoid a problem rather than cure it, so I’m writing a series of blog posts on how to recognise problems in Requirements and prevent them from becoming “SQL Smells”.

Database design and SQL smells

Any computer system contains a “model” of the world it works with. This model forms the foundations of the system. If the system does not contain a concept, then it cannot work with it!

A simplified database design process
A simplified database design process

When people start to create a system they have to decide what concepts their system needs. This is the “Conceptual Model”. This model is transformed through a “Logical Model” until it finally becomes the “Physical Model”, which is the design for the database. The Conceptual and Logical models are not just first-cut versions of the Physical Model, different design decisions and compromises are made at each stage.
This is nothing to do with “Waterfall”, “Agile” or anything to do with any specific development process. In fact, this approach is pretty universal, whether formally or not. Some people combine the different stages, but there are risks to doing that.

A simple way of looking at the Conceptual Model is to say that it is concerned with finding out:

  • What the business and system need: at the conceptual stage these are known as “Entities”
  • What we need to know about those things: these are the “Attributes” of the Entities
  • We also need to document “Business Rules”: some of these will be represented as “Relationships”.

During the design and development process:

  • Entities will tend to become table definitions
  • Attributes will become the columns within those tables
  • Business Rules may become so-called “constraints”.
Different Requirements become affect different aspects of the database
Different Requirements become affect different aspects of the database

A poor Conceptual Model or bad design decisions can lead to systems which are difficult to build, maintain and use, and which do not perform well either. Once again,

“Requirements Smells will cause SQL Smells”

The idea of “smells” can help us address potential problems earlier and more cheaply.

Where are these “Requirements smells”?

I’m going to group my bad smells in a slightly different way to Phil Factor. I primarily work as a Business Analyst, so I am going to concentrate on “smells” to look for at the Conceptual and Logical Stages of specifying the Requirements for a database, starting with the smell that Phil describes as “The God Object”!

Business Analysis: Pictures versus Words

Business Analysis: Pictures versus Words?

Image expressing "Pictures versus Words". An image of a pipe and the text "This is a pipe"
Pictures versus Words: This is a pipe

Business Analysts use models which are presented as pictures. Business Analysts also need to write text. This may give rise to a debate:

“Pictures versus Words – which is better?”

(In the picture I’m making a playful reference to Rene Magritte’s famous painting “The Treachery of Images”). Unfortunately, the honest answer to this question has to be “it depends”. It depends on what:

  • You trying to achieve with your model or text?
  • The business side wants and understands?
  • IT side needs and understands?
  • You comfortable with?

Inevitably the answer is going to finish up as a compromise.

The limitations of words

Words used as a bridge between Business and Information Technology
Words alone may form a weak bridge

Words are marvellous! You are reading this blog post! Informally, most projects start off with a short statement “we want to do something”. We add detail to that statement and it becomes a list of “requirements”. That is good, but as we start to describe our “pipe”, sometimes we find that “a picture (or model) really is worth a thousand words”.

In the “Pictures versus Words” debate, the strength of words is found when they are brief and used to define the details. They are less good for explaining how things relate to one-another.

Words alone may make for a weak bridge between business and IT. And beware! Lawyers have been arguing about the precise meanings of words since time immemorial!

The power of models (or pictures)

Orthographic projection sketch of a chair
Orthographic projection sketch of a chair

“Orthographic Projection” is one standard way of arranging different views of the same object. You know very quickly that the diagram describes a type of chair.

Picture models like engineering drawings are strong when they are used to provide overview and show how things relate to one another. By using different views of the same thing we can check each one against the others and detect inconsistencies at an early stage.

Conclusion

In summary, pictures and models are useful and so is text. Use both as appropriate. Add detail as you need it, but limit the amount of detail to what you need. Choose the models you use with care so that you can use them to validate the requirements. My advice is to apply Ward Cunningham’s comment “The simplest thing that could possibly work” to documentation and requirements.

Simple ideas are the best – BA toolkit?

Today I stumbled across a blog post from a year ago where Ron Healy suggests creating a “Temporary Whiteboard” to carry about. The “whiteboard” is created by laminating a sheet of white paper! Sometimes simple ideas really are the best. You can’t get much simpler than that!

Ron suggests having parallel lines on one side which can be used for:

  • multifunction swimlane
  • activity diagram
  • sequence diagram
  • class diagram
  • hierarchical chart
  • system & subsystem diagram

This is such a good idea. As I commented on Ron’s blog – I’m a Business Analyst and I have been been using a daily plan made the same way for years and it didn’t occur to me to extend the idea.

Simple ideas - Laminated Daily Plans
Simple ideas – Laminated Daily Plans

I have two “plans” which I use which you can see in the picture. There is an A5 size one which is punched with two holes so I can have it at the front of an A4 ring binder and an A6 size one which I keep at the front of my Time Manager.

The layout of my plan is based on the forms I used to use in my Time Manager, but I like to remind myself of the “Elephant” and “Frog” tasks I am dealing with at the moment.

Once way in which I differ from Ron is that he uses dry wipe markers. On the other hand, I use a permanent marker and then wipe the plan down with alcohol at the end of the day when I am planning what to do the next day.

More simple ideas: What do you keep in your toolkit?

“Simple ideas” got me thinking about the Business Analysis or BA “toolkit” I carry around with me. For at least some of my working life I needed to use public transport, and that encourages you to carry the minimum you need.

My toolkit contains:

  • Laptop
  • Laminated plan
  • Diary
  • A4 ring binder to contain the current notes
  • Pack of Post-It notes
  • Pack of file cards
  • Foolscap or A4 wallet folder to hold loose bits together
  • A4 spiral bound notebook
  • Whiteboard Markers

What do you keep in your toolkit?