Andy Crouch - Code, Technology & Obfuscation ...

SQL Foreign Keys Should Only Relate To One Table

Photo: Caspar Camille Rubin

In my last post, I covered why storing enumerations in your database was the wrong thing to do. The piece of work that came to me as a Pull Request that generated that post was a PR that kept giving. This post is based on a second issue I encountered in that review and why it is also the wrong thing to do.

In a database schema, a Foreign Key should only connect two tables.

The change in the PR was implementing an audit table for some notifications that are sent. The audit table was to store an Id for the sent notification, an Id for the Subscriber that viewed the notification and an Id for some content sent with the notification. All very simple. Only I could see that there was no Foreign Key defined for the SubscriberId Column. I tried to create a simple query to join to the subscriber’s table from the SuscriberId. It turned out that a subscriber table had not been created. Instead, the SubscriberId was either:

  • The Id for a fully signed up User (the projects user data is split between Customer and Supplier user data)
  • The Id for an Unknown Subscriber, someone that was not a customer but who could receive the notification.

I asked the original developer to write a query that would allow me to view the opened notification audit data. I did this to illustrate that following this approach of not using a Foreign Key would make for a very hard to maintain data schema. The reason they thought this was a good idea was that they are storing the SubscriberType which was an enumeration in the database. The query I was sent back was like the following:

SELECT    *
FROM      AuditTable Join To CustomerTable
WHERE     SubscriberType = 1
UNION
SELECT    *
FROM      AuditTable Join To SupplierTable
WHERE     SubscriberType = 2
UNION
SELECT    *
FROM      AuditTable Join To UnknownSubscriberTable
WHERE     SubscriberType = 3

Wow. So much confusion and hard work. I always have found that if I have a UNION in a query I have almost certainly got my schema or design wrong somewhere.

There was a clear need to clean this schema up. Remember

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

By creating a Subscription table and accumulating known and unknown subscriber data we can then add a Foreign Key for the audit table. The table contains data specific to the subscriber and is now managed through the subscription process. While there are some duplicate data fields at present this is a temporary step until we clean up the customer and supplier data into a single user data table. At that point, we will remove the duplicate fields and use further Foreign Keys.

Thinking about your schema is vital as you develop your application. While you may find you adapt it as you build functionality, seeing clear relationships and dependencies is key to underpinning clean code. I have taken on projects in which the database has no Foreign Keys and they are always more difficult to maintain and enhance. You will benefit from not trying to save time in developing one feature as it will almost always make dependant changes or requirements harder to implement.

If you have any comments or thoughts around this post then please contact me via twitter or email.

Storing Enumerations In SQL Server

Photo: NeONBRAND

SQL Server does not support Enumerated data types so please stop trying to force them into it.

Enumerations are used to declare a type that groups named constants. Most languages have them in a given form going as far back as Ada. In C# you declare an enumeration as follows:

public enum Colour
{
    Red, 
    Green, 
    Blue, 
    Yellow
}

A common use for enumerations is to apply a label for grouped constant values. So the above may be written as:

public enum Colour
{
    Red = 1, 
    Green = 2, 
    Blue = 3, 
    Yellow = 4
} 

There are lots of articles on the wider web about using enumerations in your language. This post is about the misuse of enumerations which is not only bad design but has a major impact on your project.

Storing an enumeration value in the database may seem appealing. You define your Colour enumeration and define your entity class.

public class Entity
{
    public Colour EntityColour{get; set;}
}

You define your SQL table column to be an int and add the scaffolding code to handle your crud operations. It all works and you move on.

Only a little while later someone else has to write a query on your table containing your Colour values. They have no idea what the values represent. Is 1 red or green or blue? Who knows. They start the process of tracking down who wrote the code. Soon they arrive at your desk but it is 4 months since you last looked at the Colour code. So you have to open the code and find the enumeration declaration and provide them with the details. Now they have the details they end up with a nasty CASE block in their query like

CASE
  WHEN Colour = 1 THEN 'Red'
END AS Colour

Removing magic numbers in our code by using enumerations, we have complicated the data schema. The better approach is to use a lookup table in your database. We then create a relationship via a Foreign Key with the table that stores our Colour data. Again there are plenty of articles on defining database tables and relationships. The point of this post is to show that storing enumeration values in the database is wrong. It leads to a confusing data schema and difficult to maintain code. Using a well-defined database schema will result with an extra Colour type in your code and an easy to follow validated dataset that is built on a standard join.

This article has made enumerations sound like they shouldn’t be used and that’s not that case at all. In a future post I will cover how and when to use them effectively.

If you have any thoughts around this post then please contact me via twitter or email.

180-Degree Review Experiment - Part 1

Photo: Charles 🇵🇭 - Unsplash

I decided to try an experiment this week.

I have asked my team, juniors through to seniors I have worked with for over 10 years, to each write a full review, on me.

Why? Well, I undertake reviews of each of my team every three months. I have written before that this is a useful technique when running remote teams. It ensures that issues do not get to brew for too long and I can hear what’s going well and what needs improving. It also means that the team get quality one on one time with me. The reviews are simply an informal chat. We can cover anything from particular work to development techniques and training plans. I make the effort to write up the conversations and the employee gets to comment and sign them off. We can then review progress on each next review.

During reflection on the first half of the year and how its been going it occurred to me that those reviews are generally one way. I gave thought to why this was the case and how much impact doing the reviews via video chat can have. Does my team have feedback that they would not be comfortable to share in person? Does the time limit we try to stick to mean that we never get to 180-degree feedback? Have I just never asked the questions?

So I have sent the whole team a blank review form. What should I be doing? What have I done well and not done well? What should I think about focusing on? They can write as much as they like and I have made it clear that the aim is to give me direction. There is no comeback and if they don’t want to discuss points with me they don’t have to.

They have two weeks to complete them and send them back. I will follow up in a few weeks time on the results and if it was a useful exercise.

To be honest, I have never felt quite so exposed.

If you have any comments around this post then please contact me via twitter or email.

Timbuk2 Command Laptop Backpack

Photo: Andy Crouch

I recently brought a new laptop bag for day to day use. I had previously switched to a slimline messenger bag as I have been trying to carry less. My commute has extended by 40 minutes over the last year on days I go to the office. I have found that I actually need to carry slightly more to keep me amused and to see me through one or two days. For these reasons and the fact Sam, our cat, decided to pee on my previous bag it was time for a new one.

For a long time, I have been looking at Timbuk2 bags. Started in 1989, Timbuk2 initially started by selling quality messenger bags for cycle couriers. These days they have widened their range. I have heard many people that I know (and some I don’t) recommend these bags. They have a reputation of lasting and being hard wearing while retaining a unique look. They also have a reputation for lots of pockets.

To be honest I missed a good backpack after a year with a slim messenger. After a little browsing, I opted to order a Command Backpack in Jet Black.

I have now been using the bag for a little over 2 months and it’s fair to say it’s worth every penny. The bag states it can hold up to 30 litres but I have yet to fill it to capacity. There is a main compartment which I use for packing a hoodie, 2-litre bottle of water, project books, reading books and protein bottles. There is still a lot of room left. There is a second main compartment with several organiser pockets and pen and cable organisers. On the front, there is a small easy access pouch and a Tricot lined glasses compartment.

The main laptop compartment is placed on the back of the bag and unzips on three sides. It is Tricot lined and will hold up to a 15” laptop. I suspect this really means a 15” Mac but my current daily driver which is a 15.6” PC Specialist laptop does just fit. It was tight though. When you unzip the compartment there is also a small section for a tablet or a small notebook. To be honest this is where one of the negative aspects of the bag is to be found. I get that the design is TSA compliant but that is little comfort when trying to get your laptop out in a crowded train. Due to the three-sided zip and the weight of the main bag, it can be awkward to get the compartment shut. This is especially true when you have only one hand or if you have a travel mug or bottle in the side pouch. I have learned to master it but it took time.

The other negative point is the compression straps on the bottom of the bag. They are there to “pull in” the bag if you are not using all the available space. Unfortunately, this means that there is no bottom for the bag to stand on. Not a big issue but it took a little getting used to and a few bag tips.

This Command is comfortable. The shoulder straps are wide and so should fit most body shapes. Even fully loaded I don’t feel the bag is heavy. I really like this bag. It has more than enough space while being practical and it comes with a lifetime warranty. Barring the need to get used to the different positioning and zip system of the laptop compartment I would recommend this bag.

Have you got a recommendation for laptop bag brands or questions about the Command Backpack? If so then please contact me via twitter or email.

Thoughts On C# Switch Statements

Photo: Michal Mrozek - Unsplash

C# has switch statements much like many other programming languages. While they can be a useful solution in some situations they can also be a code smell.

When should you use a switch statement? When it makes the code’s intention clear or the code easier to read. For example:

if(myEnumeration.Value == 1)
{
  // execute code for value 1

}
else if(myEnumeration.Value == 2 || myEnumeration.Value == 3)
{
  // execute code for values 2 or 3

}
else if(myEnumeration.Value == 4)
{
  // execute code for value 4

}
else if(myEnumeration.Value == 5)
{
  // execute code for value 5

}
else if(myEnumeration.Value == 6)
{
  // execute code for value 6

}
else
{
  // execute code for all other values

}

vs

switch(myEnumeration.Value)
{
  case 1:
    // execute code for value 1

  case 2:
  case 3:
    // execute code for values 2 or 3

  case 4:
    // execute code for value 4

  case 5:
    // execute code for value 5

  case 6:
    // execute code for value 6

  default:
    // execute code for all other values

}

As you can see the switch syntax is cleaner as it removes the need for so many brackets. It also supports “fall through” functionality which makes it easier to see mixed clauses (such as values 2 and 3). I personally like the default label which makes it clear what will be done if no values are matched. Switch statements are ideally suited to enumerations and numeric conditional tests. They are also ideally suited to instances where there is a single execution task based on a successful match.

When should you not use a switch statement? When you are working with non-numeric values. When you have multiple lines to execute on a successful match and when you can see (or smell) an Open Closed Principle violation such as the following code:

public IEnumerable<SubscriptionsModel> ApplySort(IEnumerable<SubscriptionsModel> list, string sortBy, bool asc)
{
  switch (sortBy)
  {
    case "firstname":
      return asc ? list.OrderBy(x => x.FirstName) : list.OrderByDescending(x => x.FirstName);
    case "lastname":
      return asc ? list.OrderBy(x => x.LastName) : list.OrderByDescending(x => x.LastName);
    case "email":
      return asc ? list.OrderBy(x => x.Email) : list.OrderByDescending(x => x.Email);
    case "company":
      return asc ? list.OrderBy(x => x.Company) : list.OrderByDescending(x => x.Company);
    case "typeofuser":
      return asc ? list.OrderBy(x => x.TypeOfUser) : list.OrderByDescending(x => x.TypeOfUser);
    case "telephone":
      return asc ? list.OrderBy(x => x.Telephone) : list.OrderByDescending(x => x.Telephone);
    case "createdate":
      return asc ? list.OrderBy(x => x.CreateOn) : list.OrderByDescending(x => x.CreateOn);
    default:
      return list.OrderBy(x => x.FirstName).ThenBy(x => x.LastName);
  }
}

As you can see this method is applying sorting to an enumerable SubscriptionsModel. Each case line is determining if it should order in ascending or descending order and the field to sort on. The way it is currently written means that if we add a new field into the SubscriptionModel class we would need to update the switch statement. This is far from ideal. An extra point I will make is that there is no case independent comparison here either. This is a reason I tend to not us switch statements with Strings. You have to sanitise the input as we know users can surprise use with unpredictable input.

Code like this is very common. However, C# offers a nice solution to the endless case branches, reflection. Using reflection will allow the above code to become:

public IEnumerable<SubscriptionsModel> ApplySort(IEnumerable<SubscriptionsModel> list, string sortBy, bool asc)
{
  var orderByColumnName = string.IsNullorWhiteSpace(sortBy) ? "FirstName" : sortBy;
  var orderByPropertyInfo = typeof(SubscriptionsModel).GetProperty(sortBy);

  return asc ? 
    list.OrderBy(x => orderByPropertyInfo.GetValue(x, null)) : 
    list.OrderByDescending(x => orderByPropertyInfo.GetValue(x, null));
}

This code uses a PropertyInfo instance which can be used to pass into the OrderBy and OrderByDescending methods. This allows dynamic rather than hard-coded parameters to be used removing the need to change the method when a new field is added. Mixing this into another C# favourite of mine, extensions, you might want to create a generic method such as

public static class EnumberableExtensions
{
  public static IEnumerable<T> Sort<T>(this IEnumerable<T> enumerable, string sortByField, SortOrder sortOrder)
  {
    if(String.IsNullOrWhiteSpace(sortByField))
      return enumerable;
    
    if(sortOrder < 0)
      return enumerable;
    
    var orderByPropertyInfo = typeof(T).GetProperty(sortByField);
    
    if(sortOrder == SortOrder.Ascending)
      return enumerable.OrderBy(x => orderByPropertyInfo.GetValue(x, null));
    
    return enumerable.OrderByDescending(x => orderByPropertyInfo.GetValue(x, null));
  }
}

This is a basic implementation. I have shared this with a mid-level developer recently as a starting point. The challenge was to write an implementation that contains no if statement for the sort direction.

This post has shown that you should think about the way in which switch statements are used. While useful, they need to be used in a way that doesn’t violate the SOLID principles. If you have any feedback or comments around this post then please contact me via twitter or email.