What is the most appropriate design for an object who's database key is made up of multiple columns?

Question

Suppose I have a table in my database that is made up of the following columns, 3 of which uniquely identify the row:

CREATE TABLE [dbo].[Lines]
(
    [Attr1] [nvarchar](10) NOT NULL,
    [Attr2] [nvarchar](10) NOT NULL,
    [Attr3] [nvarchar](10) NOT NULL,
    PRIMARY KEY (Attr1, Attr2, Attr3)
)

Now, I have an object in my application that represents one of those lines. It has three properties on it that correspond to the three Attr columns in the database.

public class Line
{
   public Line(string attr1, string attr2, string attr3) 
   {
        this.Attr1 = attr1;
        this.Attr2 = attr2;
        this.Attr3 = attr3;
   }

   public Attr1 {get; private set;}
   public Attr2 {get; private set;}
   public Attr3 {get; private set;}
}

There's a second object in the application that stores a collection of these line objects.

Here's the question: What is the most appropriate design when referencing an individual line in this collection (from a caller's perspective)? Should the caller be responsible for tracking the index of the line he's changing and then just use that index to modify a line directly in the collection? Or...should there be method(s) on the object that says something to the effect of:

public GetLine(string attr1, string attr2, string attr3)
{
     // return the line from the collection
}

public UpdateLine(Line line)
{
     // update the line in the collection
}

We're having a debate on our team, because some of us think that it makes more sense to reference a line using their internal index in the collection , and others think there's no reason to have to introduce another internal key when we can already uniquely identify a line based on the three attributes.

Thoughts?

Answer 1

Your object model should be designed so that it makes sense to an object consumer. It should not be tied to the data model to the greatest extent practical.

It sounds like it is more intuitive for the object consumer to think in terms of the three attributes. If there are no performance concerns that speak to the contrary, I would let the object consumer work with those attributes and not concern him with the internal workings of data storage (i.e. not require them to know or care about an internal index).

Answer 2

I always prefer to just use a single column ID column even if there is a composite key that can be used. I would just add an identity column to the table and use that for look up instead. Also, it would be faster because query for a single int column would perform better than a key spanned across three text columns.

Having a user maintain some sort of line index to look up a line doesn't seem very good to me. So if I had to pick between the two options you posed though, I would use the composite key.

Answer 3

I think the base question you are encountering is how much control the user of your API should have over your data, and what exactly you expose. This varies wildly depending on what you want to do, and either can be appropriate.

The question is, who is responsible for the information you wish to update. From what you have posted, it appears that the Line object is responsible the information, and thus I would advocate a syntax such as Collection.GetLine(attr1, attr2, attr3).UpdateX(newX) and so forth.

However, it may be that the collection actually has a greater responsibility to that information, in which case Collection.UpdateX(line, newX) would make more sense (alternatively, replace the 'line' arg with 'attr1, attr2, attr2').

Thirdly, it is possible, though unlikely (and rarely the best design IMHO) that the API user is most responsible for the information, in which case an approach you mentioned where the user handles tracking Line indices and directly modifies information.

Answer 4

What is the most appropriate design when referencing an individual line in this collection (from a caller's perspective)?

If the caller is 'thinking' in terms of the three attributes, I would consider adding an indexer to your collection class that's keyed on the three attributes, something like:

public Line this[string attr1, string attr2, string attr3] {
   get { 
      // code to find the appropriate line...
   }
}

Indexers are the go-to spot for "How Do I Fetch Data From This Collection" and, IMO, are the most intuitive accessor to any collection.

Answer 5

You do not want the calling object to "track the index of the line he's changing" - ever. This makes your design way too interdependent, pushes object-level implementation decisions off onto the users of the object, makes testing more difficult, and can result in difficult to diagnose bugs when you accidentally update one object (due to key duplications) when you meant to update another.

Go back to OO discipline: the Line object that you are returning from the GetLine method should be acting like a real, first class "thing."

The complication, of course, comes if you change one of the fields in the line object that is used as part of your index. If you change one of these fields, you won't be able to find the original in the database when you go to do your update. Well, that is what data hiding in objects is all about, no?

Here is my suggestion, have three untouchable fields in the object that correspond to its state in the database ("originalAttr1", "originalAttr2", "originalAttr3"). Also, have three properties ("attr1", "attr2", "attr3") that start out with the same values as the originals but that are Settable. Your Getters and Setters will work on the attr properties only. When you "Update" (or perform other actions that go back to the underlying source), use the originalAttrX values as your keys (along with uniqueness checks, etc.).

This might seem like a bit of work but it is nothing compared to the mess that you'll get into if you push all of these implementation decisions off on the consumer of the object! Then you'll have all of the various consumers trying to (redundantly) apply the correct logic in a consistent manner - along with many more paths to test.

One more thing: this kind of stuff is done all the time in data access libraries and so is a quite common coding pattern.

Answer 6

If the client is retrieving the Line object using three string values, then that's what you pass to the getter method. From that point on, everything necessary to update the object in the database (such as a unique row ID) should be hidden within the Line object itself.

That way all the gory details are hidden from the client, which protects the client from damaging it, and also protects the client from any future changes you might make to the dB access within the Line object.