Packing lists into SQL columns – SQL Smells

Packing Lists into SQL Columns creates an SQL Smell
Packing Lists into SQL Columns creates an SQL Smell

Some SQL data types are amazingly flexible. As a consequence people are tempted to put all kinds of data into character columns. In his article on SQL Smells Phil Factor identifies packing lists or complex data into a column as one of the “smells”. To be frank, it stinks! One row should contain one value for each column. That value should mean a single thing. Doing anything else is inviting problems.

Let’s look at how to recognise this particular “smell”, where it comes from, the consequences of allowing designs containing it and how to remove it. I’ll also touch on the limited circumstances when it is acceptable.

Recognising “packing lists into a column”

Examples of lists packed into SQL Columns
Examples of lists packed into SQL Columns

If you are an analyst or designer, working with the Conceptual Design or Requirements for the database then you will know when you are tempted to do this. My advice is – Don’t do it! The explanation will come later.
On the other hand, this smell can be hard to recognise if it actually gets into the database design. There will be evidence in three places:

  • There may be signs of “lists” in the database design. The names of affected columns may be plural, or something like “List_of_…”. The column is likely to be defined as a character type.
  • There will be evidence in the data. This is the easiest place to find the evidence. There will usually be a separator character between the different elements of the list, like “1,2,3,4”. Beware! Fixed length character columns, divided into fields, mimicking an ancient punched card are not entirely unknown.
  • The code will provide evidence. You will know it when you see it. The code will parse the offending column into separate values based on either a separator value or column positions.

Limitations of the packing approach

This approach is not using a relational database as intended. This will impose limitations on your system.

  • You will not be able to search on the individual fields without unpacking them.
  • You won’t be able to update the fields without unpacking them and then re-packing them.
  • Changes to the implied record structure will mean fundamental changes to the database and the associated code.
  • All the packing and unpacking will cause poor performance.

Excuses for packing a list into a column

There are two reasons you may be tempted to pack a list in this way:

  • You think it will somehow be “more efficient”. Take Phil Factor’s word for it. It won’t!
  • Another system expects something in this form. In this case you would be better to “do things properly” and do the conversion close to the interface with the other system. That way you limit the effect the other system is having on you.

The only acceptable excuse is that the data in the column is going to be treated as a black box. All your system is doing is storing it.

How to avoid packing a list into a column

There is one main strategy for avoiding packing a list (or other complex data) in a column. You should aim to understand the list or data you are packing into the column. Consider breaking the complex data out into a new entity. Apply the techniques of data modelling or normalization.


A column containing complex data indicates problem with the Conceptual Model of the database. You should review the data model and apply the rules of normalization.

Where next?

Having dealt with a problem poor data modelling, in the next article I’m going to look at a general problem of data design and an associated SQL Smell – “Using inappropriate data types”.

Process Models: Who is doing what?

Process Models: Who is doing what?

Process Models
Process Model Diagram

Every Business Analyst and Business Consultant should know something about Process Models and Process Modelling. Creating a simple model of what is happening outside the IT System can be a very useful place to start. You may even benefit when there is no IT System at all!

Ways of using Process Models

A Process Model is simply a representation of what the process is doing in the real world. This representation is usually graphical. There are different notations, and a large number of tools to help you draw the pictures.

Trivial Process Model - Documented using a Process Modelling tool
Trivial Process Model – Documented using a Process Modelling tool

Process Models can be used in a number of ways, many of which overlap.

  • A model can be used as a framework to assess the process against some criteria.
  • Models can be used to explore the effect of some change to the process.
  • Models can also be used to show how the physical world and IT interact.
    Using Process Models appropriately can help ensure that any changes are beneficial to the business.

Process Models: “As-Is” and “To-Be”

Process Models can be used to explore changes to a process. The “As-Is” model shows how the process works now, and the “To-Be” model shows how the process will work after the proposed changes. Comparing the two models allows us to demonstrate how the changes will be beneficial to the business.

The changes need not be changes to IT systems. The benefits which can be demonstrated may be the elimination of roles, or reduction in time or the number of steps.

Process Models: How does IT mesh with the business?

Gears - Process Models show how things interact
Gears – Process Models show how things interact

The Swim-lane process models demonstrate how different roles collaborate or people use several different tools or IT systems to perform their work. Imagine the different roles or tools as “gears” and you will understand what I mean. Using swim-lanes helps you to visualise and communicate how the different lanes interact.

Traps you can avoid using Process Models

Where a business process involves activities in the physical world (and not just doing things at a screen) then a process model can help put the IT systems into context. Doing this may prevent you spending time on details which are not important.

Ways Process Models can trap you!

Many process modelling tools allow you to break-down individual steps into smaller pieces. Resist the temptation to break things down too early, or everywhere. If you keep your models at a high level you will reduce the amount of work you have to do, and you will not reduce the value of the models.


Process Models can be used to put the IT system into a wider business context. They can even be used to analyse and rationalise processes where the IT systems do not play a significant role. Process Models are commonly used to demonstrate the claimed benefits of a new way of doing things, the so-called “As-Is” and “To-Be” models.

The keys to success with Process Models are to present the simplest model which is appropriate for your needs and to control the amount of detail.