FunctionX Practical Learning Logo

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.

Table Preparation

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 as follows:

First Name Last Name Department
Peter Larsen Accounting
Paul Banack IT/IM
Helene Cassavoy Accounting
Anselme Thomas Public Relations
Bertha Um Corporate

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.Data

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents btnCreate As System.Windows.Forms.Button
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnCreate = New System.Windows.Forms.Button
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'btnCreate
        '
        Me.btnCreate.Location = New System.Drawing.Point(8, 8)
        Me.btnCreate.Name = "btnCreate"
        Me.btnCreate.TabIndex = 0
        Me.btnCreate.Text = "Create"
        '
        'DataGrid1
        '
        Me.DataGrid1.AlternatingBackColor = System.Drawing.Color.Lavender
        Me.DataGrid1.BackColor = System.Drawing.Color.WhiteSmoke
        Me.DataGrid1.BackgroundColor = System.Drawing.Color.LightGray
        Me.DataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.None
        Me.DataGrid1.CaptionBackColor = System.Drawing.Color.LightSteelBlue
        Me.DataGrid1.CaptionForeColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.FlatMode = True
        Me.DataGrid1.Font = New System.Drawing.Font("Tahoma", 8.0!)
        Me.DataGrid1.ForeColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.GridLineColor = System.Drawing.Color.Gainsboro
        Me.DataGrid1.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None
        Me.DataGrid1.HeaderBackColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.HeaderFont = New System.Drawing.Font("Tahoma", 8.0!, System.Drawing.FontStyle.Bold)
        Me.DataGrid1.HeaderForeColor = System.Drawing.Color.WhiteSmoke
        Me.DataGrid1.LinkColor = System.Drawing.Color.Teal
        Me.DataGrid1.Location = New System.Drawing.Point(8, 40)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.ParentRowsBackColor = System.Drawing.Color.Gainsboro
        Me.DataGrid1.ParentRowsForeColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.SelectionBackColor = System.Drawing.Color.CadetBlue
        Me.DataGrid1.SelectionForeColor = System.Drawing.Color.WhiteSmoke
        Me.DataGrid1.Size = New System.Drawing.Size(408, 216)
        Me.DataGrid1.TabIndex = 1
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(424, 262)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.btnCreate)
        Me.Name = "Form1"
        Me.Text = "Relationships Fundamentals"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim dsEmployment As DataSet = New DataSet
        Dim dtEmployees As DataTable = New DataTable("Employees")

        Dim dcEmployees(3) As DataColumn
        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") = "Cassavoy"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

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

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

        dsEmployment.Tables.Add(dtEmployees)
        Me.DataGrid1.DataSource = dsEmployment
        Me.DataGrid1.DataMember = "Employees"
    End Sub
End Class

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

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim dsEmployment As DataSet = New DataSet
        Dim dtEmployees As DataTable = New DataTable("Employees")

        Dim dcEmployees(3) As DataColumn
        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") = "Cassavoy"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

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

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

        dsEmployment.Tables.Add(dtEmployees)
        Me.DataGrid1.DataSource = dsEmployment
        Me.DataGrid1.DataMember = "Employees"

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

        Dim dcEmployment As DataColumn = New DataColumn
        dcEmployment = New DataColumn("EmplStatus", System.Type.GetType("System.String"))
        dtEmplStatus.Columns.Add(dcEmployment)

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

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

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

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

        dsEmployment.Tables.Add(dtEmplStatus)

        Me.DataGrid1.DataSource = dsEmployment
        Me.DataGrid2.DataSource = dsEmployment
        Me.DataGrid1.DataMember = "Employees"
        Me.DataGrid2.DataMember = "EmploymentStatus"
End Sub
 

Creating a Relationship

As it should appear obviously, 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 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:

 

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim dsEmployment As DataSet = New DataSet
        Dim dtEmployees As DataTable = New DataTable("Employees")

        Dim dcEmployees(4) As DataColumn
        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("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") = "Cassavoy"
        drEmplRecord("Department") = "Accounting"
        dtEmployees.Rows.Add(drEmplRecord)

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

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

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

        Dim dcEmployment(2)
        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(dtEmployees)
        dsEmployment.Tables.Add(dtEmplStatus)

        Me.DataGrid1.DataSource = dsEmployment
        Me.DataGrid2.DataSource = dsEmployment
        Me.DataGrid1.DataMember = "Employees"
        Me.DataGrid2.DataMember = "EmploymentStatus"
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        End
    End Sub

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. Therefore, 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. Therefore, the 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 it 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 created 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:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim dsEmployment As DataSet = New DataSet
        Dim dtEmployees As DataTable = New DataTable("Employees")

        Dim dcEmployees(5)
        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))
        dcEmployees(4) = New DataColumn("EmplStatusID", System.Type.GetType("System.Int32"))
        dtEmployees.Columns.Add(dcEmployees(4))

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

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

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

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

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

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "6"
        drEmplRecord("FirstName") = "Renée"
        drEmplRecord("LastName") = "Bright"
        drEmplRecord("Department") = "IT/IM"
        drEmplRecord("EmplStatusID") = "3"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "7"
        drEmplRecord("FirstName") = "Jeanne"
        drEmplRecord("LastName") = "Tristan"
        drEmplRecord("Department") = "Corporate"
        drEmplRecord("EmplStatusID") = "1"
        dtEmployees.Rows.Add(drEmplRecord)

        drEmplRecord = dtEmployees.NewRow()
        drEmplRecord("EmployeeID") = "8"
        drEmplRecord("FirstName") = "Sandrine"
        drEmplRecord("LastName") = "Holland"
        drEmplRecord("Department") = "Public Relations"
        drEmplRecord("EmplStatusID") = "4"
        dtEmployees.Rows.Add(drEmplRecord)

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

        Dim dcEmployment(2)
        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(dtEmployees)
        dsEmployment.Tables.Add(dtEmplStatus)

        Dim colParent As DataColumn = dsEmployment.Tables("EmploymentStatus").Columns("EmplStatusID")
        Dim colChild As DataColumn = dsEmployment.Tables("Employees").Columns("EmplStatusID")
        Dim drEmployeeStatus As DataRelation = New DataRelation("EmployeeStatus", colParent, colChild)

        dsEmployment.Relations.Add(drEmployeeStatus)

        Me.DataGrid1.DataSource = dsEmployment
        Me.DataGrid1.DataMember = "EmploymentStatus"
End Sub
 

Previous Copyright © 2005-2012 FunctionX