Data and Relationships

Introduction

In the database world, a relationship is a link that exists between two objects, mainly tables, so that data can flow from one object to another. A relationship can do even more than that: it can be used to check the accuracy of information from one object to another, it can be used to update the information in one object when related information in another object has been changed.

Remember that whenever you are dealing with a group of records, also called a set of records, or a set of data, or a data set, you need an object that can "translate" the values of your records into values that are data-oriented. The object used to take care of this aspect is implemented through the DataSet class. In this case, since we want to address relationships among tables, we will need a data set object to manage such links.

Imagine that you are creating a list of employees in a mid-size to large company and want to categorize them by their employment status. You can start by creating a list of the employees:

This is a classic table. There are two common ways you can create a table. You can generate a table from a database, or you can use the DataTable class that allows you to manually create a table. Here is an example:

Imports System.IO
Imports System.xml
Imports System.Data

Module Module1

    Sub Main()
        Dim dsEmployment As DataSet = New DataSet
        Dim dtEmployees As DataTable = New DataTable("Employees")
        Dim dcEmployees(3) As DataColumn ' = New DataColumn(3)

dcEmployees(0) = New DataColumn("FirstName", System.Type.GetType("System.String"))
        dtEmployees.Columns.Add(dcEmployees(0))
dcEmployees(1) = New DataColumn("LastName", System.Type.GetType("System.String"))
        dtEmployees.Columns.Add(dcEmployees(1))
dcEmployees(2) = New DataColumn("Department", System.Type.GetType("System.String"))
        dtEmployees.Columns.Add(dcEmployees(2))

        Dim drEmplRecord As DataRow = dtEmployees.NewRow
        drEmplRecord("FirstName") = "Peter"
        drEmplRecord("LastName") = "Larsen"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("FirstName") = "Paul"
        drEmplRecord("LastName") = "Banack"
        drEmplRecord("Department") = "IT/IM"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("FirstName") = "Helene"
        drEmplRecord("LastName") = "Casson"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("FirstName") = "Anselme"
        drEmplRecord("LastName") = "Thomas"
        drEmplRecord("Department") = "Public Rel"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("FirstName") = "Bertha"
        drEmplRecord("LastName") = "Colson"
        drEmplRecord("Department") = "Corporate"
        dtEmployees.Rows.Add(drEmplRecord)

        dsEmployment.Tables.Add(dtEmployees)

        Console.WriteLine("============+===============+===============")
        Console.WriteLine("First Name  | Last Name     | Department")
        Console.WriteLine("------------+---------------+---------------")
        For Each row As DataRow In dsEmployment.Tables("Employees").Rows
            Console.WriteLine("{0}    | {1}    | {2}", _
                           row("FirstName"), row("LastName"), _
                           row("Department"))
        Next

        Console.WriteLine("============+===============+===============")
    End Sub

End Module

This would produce:

To associate each employee with a department, you can first create a table for the departments. Here is an example:

Creating a Relationship

As it should appear obvious, a relationship makes more sense in the presence of at least two tables, although a table can (also) have some type of relationship with itself. By the rules and suggestions of relational databases, for a table to participate in a relationship, the table should provide at least one column that would be used to uniquely represent or identify each record. Such a column is usually called an index. Although it can be positioned anywhere in the table, it is usually the first or most-left column. Also traditionally, the name of this column ends with No, such as RecordNo, or an ID suffix, such as RecordID.

In our first table, to uniquely identify each record, we can create a column called EmployeeID and add an incremental number to each record. In the same way, a column used to identify each department in the second table of our example can be called EmploStatusID. The tables would then be created as follows:

Imports System.IO
Imports System.xml
Imports System.Data

Module Module1

    Sub Main()
        Dim dsEmployment As DataSet = New DataSet
        Dim dtEmployees As DataTable = New DataTable("Employees")
        Dim dcEmployees(3) As DataColumn ' = New DataColumn(4)

        dcEmployees(0) = New DataColumn("EmployeeID", System.Type.GetType("System.Int32"))
        dtEmployees.Columns.Add(dcEmployees(0))
        dcEmployees(1) = New DataColumn("FirstName", System.Type.GetType("System.String"))
        dtEmployees.Columns.Add(dcEmployees(1))
        dcEmployees(2) = New DataColumn("LastName", System.Type.GetType("System.String"))
        dtEmployees.Columns.Add(dcEmployees(2))
        dcEmployees(3) = New DataColumn("Department", System.Type.GetType("System.String"))
        dtEmployees.Columns.Add(dcEmployees(3))

        Dim drEmplRecord As DataRow = dtEmployees.NewRow
        drEmplRecord("EmployeeID") = "1"
        drEmplRecord("FirstName") = "Peter"
        drEmplRecord("LastName") = "Larsen"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "2"
        drEmplRecord("FirstName") = "Paul"
        drEmplRecord("LastName") = "Banack"
        drEmplRecord("Department") = "IT/IM"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "3"
        drEmplRecord("FirstName") = "Helene"
        drEmplRecord("LastName") = "Casson"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "4"
        drEmplRecord("FirstName") = "Anselme"
        drEmplRecord("LastName") = "Thomas"
        drEmplRecord("Department") = "Public Rel"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "5"
        drEmplRecord("FirstName") = "Bertha"
        drEmplRecord("LastName") = "Colson"
        drEmplRecord("Department") = "Corporate"
        dtEmployees.Rows.Add(drEmplRecord)

        Dim dtEmplStatus As DataTable = New DataTable("EmploymentStatus")

        Dim dcEmployment(2) As DataColumn

        dcEmployment(0) = New DataColumn("EmplStatusID", System.Type.GetType("System.Int32"))
        dtEmplStatus.Columns.Add(dcEmployment(0))
        dcEmployment(1) = New DataColumn("EmplStatus", System.Type.GetType("System.String"))
        dtEmplStatus.Columns.Add(dcEmployment(1))

        Dim drEmployment As DataRow = dtEmplStatus.NewRow()
        drEmployment("EmplStatusID") = "1"
        drEmployment("EmplStatus") = "Full Time"
        dtEmplStatus.Rows.Add(drEmployment)

        drEmployment = dtEmplStatus.NewRow()
        drEmployment("EmplStatusID") = "2"
        drEmployment("EmplStatus") = "Part Time"
        dtEmplStatus.Rows.Add(drEmployment)

        drEmployment = dtEmplStatus.NewRow()
        drEmployment("EmplStatusID") = "3"
        drEmployment("EmplStatus") = "Contractor"
        dtEmplStatus.Rows.Add(drEmployment)

        drEmployment = dtEmplStatus.NewRow()
        drEmployment("EmplStatusID") = "4"
        drEmployment("EmplStatus") = "Intern"
        dtEmplStatus.Rows.Add(drEmployment)

        dsEmployment.Tables.Add(dtEmplStatus)
        dsEmployment.Tables.Add(dtEmployees)

        Console.WriteLine("===+=============+===============+=================")
        Console.WriteLine("ID | First Name  | Last Name     | Department")
        Console.WriteLine("---+-------------+---------------+-----------------")
        For Each row As DataRow In dsEmployment.Tables("Employees").Rows
            Console.WriteLine(" {0} | {1} | {2} | {3}", _
                           row("EmployeeID"), row("FirstName"), _
                           row("LastName"), row("Department"))
        Next
        Console.WriteLine("===+=============+===============+=================")

        Console.WriteLine("ID | Employment Status")
        Console.WriteLine("---+-------------------")
        For Each row As DataRow In dsEmployment.Tables("EmploymentStatus").Rows
            Console.WriteLine(" {0} | {1}", row("EmplStatusID"), row("EmplStatus"))
        Next
        Console.WriteLine("===+===================")
        Console.WriteLine()

    End Sub

End Module

As mentioned already, this type of field is used to uniquely identify each record of a table. Therefore, it is based on this field that a table can be related to another. To actually create the relationship, in our example, to associate each employee to a department, the table that holds the list of employees must have a field that represents the corresponding department from the Departments table and you must create such a new column. The most important rule you must observe is that this new field must have the same data type as the column that uniquely identifies each department in the other table. This field in the Departments table is referred to as the Primary Key. The new column created in the Employees table is referred to as the Foreign Key because this column acts only as an "ambassador". In the strict sense, it doesn't belong to the table in which it is created and in fact, its values should/must not be changed by its hosting table.

After creating the foreign key column, the relationship is not automatically applied between both tables, since neither the compiler nor the database engine (if you were working on a database) is aware of the role of this new field. To create a relationship between two DataTable objects, the Microsoft .NET Framework provides the DataRelation class. As this is a small class, its main role is to join two tables.

In order to create a relationship, you can declare a variable of type DataRelation and use one of its 5 constructors to initialize the relationship. The first constructor has the following syntax:

Public Sub New( _
   ByVal relationName As String, _
   ByVal parentColumn As DataColumn, _
   ByVal childColumn As DataColumn _
)

The first argument allows you to specify a name for the relationship.

The second argument must identify the primary key column of the table that would supply the values. In our example, this would be the primary key of the Departments table.

The third argument is the column used as the foreign key in the table that would receive the values of the other table. In our example, this would be the foreign key of the Employees table.

This indicates that you should first define and identify the columns that would be used in the relationship. Based on this description, the relationship can be created as follows: