Share via

Writing SQL for your application Part 2

Introduction

First time and occasional developer tend to not fully think through a database design for their projects which can lead to writing more code as progress is made and in many cases from improper database design causes maintenance issues once deployed. In this article, part 2 on a series to write better SQL the focus will be on working with why not to store multiple values in a single field within a SQL-Server database table.

To demonstrate, a database will be used which provides working with in the realm of academia.  The question will be, how to store what days of the week a course is offered. Two different approaches will be shown with advantages and disadvantages for both methods used within Windows Form projects using a class project to acquire data from a SQL-Server database with data scripts included to provide the reader with the ability to try both versions.

Although examples work with Microsoft SQL-Server the same concept applies to all relational databases which is, don't simply sit down without mapping out how data will be stored, read, edited, using in reports and maintained.

Words of wisdom 

"Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs,  and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%." Knuth 1974 

Using a single field for storing multiple days

In the following example the code is innocuous in that is clear and easy to understand with comments as needed. The problem does not come from presentation of information but instead from how will the raw data be seen and maintained.

Viewing the table data for the last ListBox which contains days a course is offered can the values in the Days field easily be known? When working with the database design on a continual basis, sure the character "H" will be known to be a specific day of the week while not working with this data it is impossible to know what "H" means. An educated guess is M is Monday, T is Tuesday so perhaps H is Thursday etc.

To get the day names the following SELECT is used which is not easy to dissect along with almost impossible for someone without much experience with SQL to maintain the SELECT statement if changes are required.

DECLARE @CourseIdentifier AS INT  = 1045;
SELECT SUBSTRING(a.b, v.number + 1, 1) AS  DayParts
FROM   ( SELECT (   SELECT Days
                    FROM   OnsiteCourse
                    WHERE  ( CourseID = @CourseIdentifier )) AS b ) AS a
       INNER JOIN master.dbo.spt_values AS  v ON  v.number < LEN(a.b)
WHERE  ( v.type = 'P' );

The following code implements the above SELECT statement, executes a command followed by iterating the returning data using a select case to traverse each character to assign a day name.

VB.NET

Public Function  DayNamesFromSingleField(CourseIdentifier As Integer) As  List(Of String)
 
    Dim dayList As New  List(Of String)
 
    Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As  New SqlCommand With {.Connection = cn}
 
            cmd.CommandText = "SELECT SUBSTRING(a.b, v.number + 1, 1) AS DayParts FROM  " &
                              "(SELECT (SELECT Days FROM OnsiteCourse " &
                              "WHERE  ( CourseID = @CourseIdentifier )) AS b ) AS a " &
                              "INNER JOIN master.dbo.spt_values AS v ON v.number < LEN(a.b) " &
                              "WHERE  ( v.type = 'P' );"
 
 
            cmd.Parameters.AddWithValue("@CourseIdentifier", CourseIdentifier)
 
            cn.Open()
 
            Dim reader = cmd.ExecuteReader()
 
            While reader.Read()
                Select Case  reader.GetString(0)
                    Case "X"
                        dayList.Add("Sunday")
                    Case "M"
                        dayList.Add("Monday")
                    Case "T"
                        dayList.Add("Tuesday")
                    Case "W"
                        dayList.Add("Wednesday")
                    Case "H"
                        dayList.Add("Thursday")
                    Case "F"
                        dayList.Add("Friday")
                    Case "S"
                        dayList.Add("Saturday")
                    Case Else
                        dayList.Add("Unknown")
                End Select
 
 
            End While
        End Using
    End Using
 
    Return dayList
 
End Function

**C# **

public List<string> DayNamesFromSingleField(int CourseIdentifier)
{
 
    var dayList = new  List<string>();
 
    using (var cn = new SqlConnection { ConnectionString = ConnectionString })
    {
        using (var cmd = new SqlCommand { Connection = cn })
        {
            cmd.CommandText = "SELECT SUBSTRING(a.b, v.number + 1, 1) AS DayParts " +
                              "FROM  (SELECT (SELECT Days FROM OnsiteCourse " + 
                              "WHERE  ( CourseID = @CourseIdentifier )) AS b ) AS a " + 
                              "INNER JOIN master.dbo.spt_values AS v ON v.number < LEN(a.b) " + 
                              "WHERE  ( v.type = 'P' );";
 
            cmd.Parameters.AddWithValue("@CourseIdentifier", CourseIdentifier);
 
            cn.Open();
 
            var reader = cmd.ExecuteReader();
 
            while (reader.Read())
            {
                switch (reader.GetString(0))
                {
                    case "X":
                        dayList.Add("Sunday");
                        break;
                    case "M":
                        dayList.Add("Monday");
                        break;
                    case "T":
                        dayList.Add("Tuesday");
                        break;
                    case "W":
                        dayList.Add("Wednesday");
                        break;
                    case "H":
                        dayList.Add("Thursday");
                        break;
                    case "F":
                        dayList.Add("Friday");
                        break;
                    case "S":
                        dayList.Add("Saturday");
                        break;
                    default:
                        dayList.Add("Unknown");
                        break;
                }
 
 
            }
        }
    }
 
    return dayList;
 
}

Suppose a new requirement is needed or had been forgotten which is, a course(s) is not available every day. At this point the current solution is useless and a redesign is required. Another issue is that the day names are in one culture and each place the table is used (which may be multiple projects) the day names need to be changed. Another issue is the customer may want to change the indexing of days so again the design fails.

Normalizing to store days using multiple tables

Rather attempting to store day names as single characters in a single field use a table, in this case CourseDay which stores the day index of the week which has WeekDayName table with day names. CourseDay has a field CourseID which can be used to get day name and a field Offered which is a bit column seen as a Bool/Boolean in C# and VB.NET.

To get day names in SQL

SELECT CD.id ,
       WDN.DayName AS  Name ,
       CD.DayIndex ,
       CD.Offered
FROM   dbo.CourseDay AS CD
       INNER JOIN dbo.WeekDayName AS  WDN ON  CD.DayIndex = WDN.WeekId
WHERE  (   CD.CourseID = 1050
           AND CD.Offered = 1 );

Results for a single course.

The following function is uses the query above to return a list.

Namespace Classes
    Public Class  CourseDay
        Public Property  Id() As  Integer
        Public Property  Name() As  String
        Public Property  DayIndex() As  Integer
        Public Property  Offered() As  Boolean
        Public Property  CourseID() As  Integer
        Public Overrides  Function ToString() As String
            Return Name
        End Function
    End Class
End Namespace

Note by default only available days are read using Offered field equal to True as the parameter for Offered is an optional parameter set to true.

VB.NET

Public Function  DayNamesFromReferences(
    CourseIdentifier As  Integer,
    Optional Available As Boolean  = True) As  List(Of CourseDay)
 
    Dim courseDaysList As New  List(Of CourseDay)
 
    Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As  New SqlCommand With {.Connection = cn}
 
            cmd.CommandText = "SELECT CD.id , WDN.DayName AS Name ,CD.DayIndex ," &
                              "CD.Offered FROM dbo.CourseDay AS CD " &
                              "INNER JOIN dbo.WeekDayName AS WDN ON CD.DayIndex = WDN.WeekId " &
                              "WHERE  ( CD.CourseID = @CourseIdentifier AND Offered = @Available);"
 
            cmd.Parameters.AddWithValue("@CourseIdentifier", CourseIdentifier)
            cmd.Parameters.AddWithValue("@Available", Available)
 
            cn.Open()
 
            Dim reader = cmd.ExecuteReader()
 
            While reader.Read()
                courseDaysList.Add(New CourseDay() With {.Id = reader.GetInt32(0), .Name = reader.GetString(1), .DayIndex = reader.GetInt32(2), .Offered = reader.GetBoolean(3), .CourseID = CourseIdentifier})
            End While
 
        End Using
    End Using
 
    Return courseDaysList
 
End Function

C#

public List<CourseDay> DayNamesFromReferences(int courseIdentifier, bool available = true)
{
    var courseDaysList = new  List<CourseDay>();
 
    using (var cn = new SqlConnection { ConnectionString = ConnectionString })
    {
        using (var cmd = new SqlCommand { Connection = cn })
        {
 
            cmd.CommandText = "SELECT CD.id , WDN.DayName AS Name ,CD.DayIndex ,CD.Offered " + 
                              "FROM dbo.CourseDay AS CD " + 
                              "INNER JOIN dbo.WeekDayName AS WDN ON CD.DayIndex = WDN.WeekId " + 
                              "WHERE  ( CD.CourseID = @CourseIdentifier AND Offered = @Available);";
 
            cmd.Parameters.AddWithValue("@CourseIdentifier", courseIdentifier);
            cmd.Parameters.AddWithValue("@Available", available);
 
            cn.Open();
 
            var reader = cmd.ExecuteReader();
 
            while (reader.Read())
            {
                courseDaysList.Add(new CourseDay()
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    DayIndex = reader.GetInt32(2),
                    Offered = reader.GetBoolean(3),
                    CourseID = courseIdentifier
                });
            }
 
        }
    }
 
    return courseDaysList;
 
}

Since each item in the ListBox which displays the days is a class item the properties of CourseDay are available which will be needed to perform other actions like store this information for when a day is selected in another window which has a CheckedListBox for a student to select a day out of multiple choices of days the course is offered.

VB.NET

Dim SelectedDay As CourseDay = CType(DaysCourseAvailableListBox.SelectedItem, CourseDay)

C#

CourseDay SelectedDay = (CourseDay)DaysCourseAvailableListBox.SelectedItem;

Proper code placement

Not only is writing maintainable SQL important but how code is written. A good rule to follow is to place code in a form that can not exists outside of a form.

Ask the following question each time data is involved in a project.

  • Why would code to access data be placed into a form
    • Because its easy
    • Everyone else does it

Selecting either of the above reasons is incorrect! Many examples found on the Internet have code in a form because it's easier to demonstrate in a form than to take more time and add a class which would be responsible for interacting with a backend database. Look past the simple example found on the Internet and place code to access data into classes.

Placing code into classes compartmentalizes code

  • Makes for ease of maintenance rather than code everything in a single form.
  • Permits code to be reused.

These reasons can be compared to SQL in that a focus should be not only placed on finishing a project but also having the project code potentially usable in other projects. In SQL there are two main choices, place SQL in a class project or in a Stored Procedure.

Summary

In this article both code and SQL have been presented to show and explain how to move away from the thought of storing multiple values in a field within a database table.  If the reason for this was to be concerned about stored huge number of rows SQL Server has been built to handle a tremendous number of rows with a proper design and proper indexing.

Source code

VB.NET https://github.com/karenpayneoregon/SqlNormalization
C# To follow

Resources

See also