Working with Different Kinds of Data: Using OUTER JOINS and Inheritance

I am working on combining a lot of data into one engine where items can be searched and linked together, but there are several data type, each with unique data fields. In my case, I am working with (1) audio/video content and (2) text content such as articles and news items. To represent this data in a database, I could make one large table with lots of fields, some common to both data types and some specific to each one. Then this data can be represented with a single class and my ORM is very simple.

Instead I’ve chosen to use a combination of several tables to separate out the data, but then I join them together on the programming side with inheritance. Here’s the data model (simplified for this example), where the shared elements are in the GenericItems table:

CREATE TABLE GenericItems (
    ItemID uniqueidentifier,
    Title nvarchar(255),
    CreationDate datetime,
    IsActive bit,
    Description ntext,
    ItemType int)
CREATE TABLE TextItems (
    ItemID uniqueidentifier,
    ArticleText ntext,
    AuthorID int,
    MagazineID int)
CREATE TABLE MediaItems (
    ItemID uniqueidentifier,
    Filename nvarchar(50),
    Filesize int,
    Duration int)

I then create three classes to model represent this (again, shortened for space):

public class GenericItem {
    protected Guid _itemID;
    protected string _title;
    public Guid ItemID {
        get { return _itemID; }
        set { _itemID = value; }
    }
    public string Title {
        get { return _title; }
        set { _title = value; }
    }
    // ... other fields
}
public class MediaItem : GenericItem {
    protected string _filename;
    public string Filename {
        get { return _filename; }
        set { _filename = value; }
    }
    // ... other fields
}
public class TextItem : GenericItem {
    protected string _articleText;
    public string ArticleText {
        get { return _articleText; }
        set { _articleText = value; }
    }
    // ... other fields
}

For the data layer, I can now make calls which pull both MediaItem objects and TextItem objects together into a List<T> object with type GenericItem. For example, the following method gets recent items of both types and uses an LEFT OUTER JOIN to bring them into a single query:

public static List<GenericItem> GetRecentItems() {
    List<GenericItem> itemsList = new List<GenericItem>();
    string sql = @"
SELECT
   TOP 10 *
FROM
   GenericItems
   LEFT OUTER JOIN
      TextItems  ON TextItems.ItemID = GenericItems.ItemID
   LEFT OUTER JOIN
      MediaItems ON MediaItems.ItemID = GenericItems.ItemID
ORDER BY
   CreationDate;";
    // ... connection code
    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
    while (sqlDataReader.Read()) {
        object item = null;
        // first, find out which object type this will be,
        int itemType = (int)dataReader["ItemType"];
        // second, instantiate the object and get its unique fields
        if (itemType == 1) {
           item = new TextItem();
           TextItem textItem = item as TextItem;
           textItem.ArticleText = (string)dataReader["ArticleText"];
           // ... other fields
        } else if (itemType == 2) {
           item = new MediaItem();
           MediaItem mediaItem = item as MediaItem;
           mediaItem.Filename = (string)dataReader["Filename"];
           // ... other fields
        }
        // get the properties shared in the GenericItems table
        ContentItem contentItem = item as ContentItem;
        contentItem.ItemID = new Guid(dataReader["ItemID"].ToString());
        contentItem.Title= new (string) dataReader["Title"];
        // ... other fields
        itemsList.Add( (GenericItem) item);
    }
    // ... close connection, etc.
    return itemsList;
}

We now have a List<T> with data in that can be used in other controls. In ASP.NET, this data can be bound to Asp:Repeater controls and which can show data based on the type. In the following example, the repeater shows the Title (which all items have), but then shows either “read” or “watch” depending on the type of the object:

<asp:Repeater id="RecentItemsRepeater" runat="Server" >
    <ItemTemplate>
        <div class="item">
            <%# Eval("Title") %>
            <a href="view.aspx?ItemID=<%# Eval("Title") %>"><%# (Container.DataItem is TextItem) ? "read" : "watch" %></a>
        </div>
    <ItemTemplate>
</asp:Repeater>

You can also programmatically sort the data using anonymous methods as long as you sort on the shared fields:

// from above...
List<GenericItem> recentItems = GetRecentItems();
// re-sort on title
recentItems.Sort (delegate(GenericItem a, GenericItem b) { return a.Title.CompareTo(b.Title); });

I should note that there is quite a bit of JOINing, boxing, and unboxing going on here which means this may not be the best solution in all cases. But it is a helpful way of bringing together different kinds of data without needing to have one giant SQL table or one giant class.