Geeks With Blogs
Patrice Calve Life's short, have fun

I have a recurring problem with Data Tiers.   Most entities have two important properties: ID and DisplayName.  The id for the link and DisplayName for the text to show.

In a 2 tier, it's easy: select ID, DisplayName and get the values directly.

Most 3-tier examples work 1 table at a time.  While groovy, not usefull in a 2 table scenario. 


  • ID: unique identifier
  • DisplayName: string
  • Field1: string
  • Field2: int

Table 2:

  • ID: unique identifier
  • Table1ID: unique identifier
  • DisplayName: string
  • Field3: string
  • Field4: int

Table1 and Table2 are “linked” via Table2.Table1ID = Table1.ID

So Table 1 has many Table 2.  Here's a typical select case to display Table 2 information

   Table1.ID as Table1ID,
   Table1.DisplayName as Table1DisplayName,
   Table2 INNER JOIN Table1 on Table2.Table1ID = Table1.ID

The list view would be something like this:

Name Parent Field3 Field4
book 1 author 1 foo 123
book 2 author 1 bar 234

Name: it would be an hyperlink to Table2.aspx?Table2ID=xxx
Parent: it would be an hyperlink to Table1.aspx?Table1ID=yyy

This is extremely easy in 2 tier.  1 Select Statement that has all the info.

How do you set it up in 3 tier?

I suppose: 2 classes: Table1 and Table2. 

Table1: ID Guid, DisplayName string, Field1 string, Field2 int
Table2: ID Guid, Table1 Table1, DisplayName string, Field3 string, Field4 int

So, in code, I can have Console.WriteLine(myTable2.Table1.DisplayName);

or in the table example above:
link.NavigateUrl = “Table1.aspx?Table1ID=” + myTable2.Table1.ID;
link.Text = myTable2.Table1.DisplayName;

I then suppose that when I “fill” the values for Table2, I need to fill values for Table1.  But, let's say that there are many hierachies, when do I stop?

How do you fill the values?

from the Web Page:

private void GetValues(Guid table1ID)
  Table1 t = new Table1();
  lblID.Text = t.ID.ToString(“D“);
  txtDisplayName.Text = t.DisplayName;
  txtField1.Text = t.Field1;
  txtField2.Text = t.Field2.ToString();


On the save, it would be quite the same, but:

private void SaveValues()
  Guid g = new Guid(lblID.Text);
  Table1 t = new Table1();
  t.ID = g;


But, for Table2, we have a link to Table1.  When and who takes care of “filling” it up?  The data tier or the presentation?

If we leave it to the Presentation, then, Table2 need to save the Guid of Table1 not the pointer.  So, we change Table2's pointer to Table1 to a simple Guid. 

table 2 now becomes:
Table2: ID Guid, Table2 Guid, DisplayName string, Field3 string, Field4 int

This requires 2 calls to the database.  One for the Table2, and one for the parent's Table1.   If table2 has “links” to more than Table1, it's one call for each.

If we leave it to the DataTier, we could have Table2 return the recordset for Table2, append the recordset for Table1.  Build the values for Table2, move to the next recordset and then build the values for Table1.  But we have the same problem as above, when do we stop?  if Table3 has a link to Table2 to Table1.  Do we fill everything up at the first call?  not very scalable.

Do we keep an internal copy of either the ID of the parent table and/or the pointer?

An idea would be to have Table2 return values for Table2 plus the ID/DisplayName for Table1.  In Table1, there would be a special state where the data wouldn't be complete: ie only the ID and the display would be there, and it would be up to the client to get the rest (or have it automatically done) when the client tries to access the values (via properties).  Something like:

public string Field1
       if(internalState!=Complete) GetAllData();

This way, Table2 doesn't have to “get” all the sub info, the presentation has enough for displaying, and Table1 has enough info to get the rest if need be.

Lastly, an idea would be to have the class Table2 have the data necessary:

Table2: ID Guid, Table2ID Guid, Table2DisplayName string, DisplayName string, Field3 string, Field4 int

Am I way off the track?  Have I smoked crack or something? 

Looking forward to your design patterns.

Posted on Sunday, October 9, 2005 12:40 PM | Back to top

Comments on this post: Need Help with 3 tier pattern

# re: Need Help with 3 tier pattern
Requesting Gravatar...
I have the same problem in my current project and I didn't found a good solution either, but IME the Data Layer should behave as if there were no database at all and should only deal with objects. Multiple calls just to get the childs of a specific object should be avoided IMO.
You should begin with a class diagram that represents the data and how you as a developer wants to access the data. Then you should create the database and the data layer to fit your class diagram.
If you find a good solution for your problems, please post it. I'm very interested in this topic!
Left by Thomas Krause on Oct 10, 2005 6:56 PM

# re: Need Help with 3 tier pattern
Requesting Gravatar...
I'm working on a Demo, so, right now, the client is in mode "shut up and code" :). They just need a basic system to show off.

It's going to be throw away code. But still, I try to write something good. The functionality won't be there 100%, but what will be there is going to be as solid as time can give me.

So, after digging a bit, I decided to fill the hole and use what I had at that point. A mix match of many things (I hate that moment when a decision between time and code quality is taken). Again, I have to remind myself: demo, demo, demo.

so, here's what I have.

I have a "connection" class that the sub classes inherit, tables like the Table1 and Table2 in the example above.

This connection is nothing fancy for now. As I think that Enterprise Library takes care of the DB connections and has goodies. After the demo, I'll have a look at that.

Then, I have Table1 and Table2. Table2 has all the info I need to display what's needed.

So, I have:

- ID: unique identifier
- Table1ID: unique identifier
- Table1DisplayName: string
- DisplayName: string
- Field3: string
- Field4: int

For the methods, I have them tailored for their use (right now, on the web page)
> Get(ID): Fills the values for records ID
> GetAll(OwnerID): Returns an SqlDataReader that the Web Page can use (ex: data bind).
> Save(): you fill up the values, it will save.
> Delete(ID):

I know I'll find bad things about this design. But for now, I have web pages showing and that enough for me, for now.

For instance, I don't like the idea of specifying the ID on a Get, but not on the Save.

I have an instance where the data is collected in two web pages: ex: properties on 1 page and the file upload on the other. So there's two separate saves. I end up with two Save() method. beuark. Man I don't like designs like this. "demo, demo, demo"...

The reason I switched from 2 tier to 3 tier is simple: 1) learning experience and 2) I had to reuse many methods!

What I might end up doing, is having a struct (or class) to hold the values, and have the methods in a "methods" stateless class.

I really like the 2 tier: it's a Get what you want tier. But, you don't have access to re-use. What about performance? What about other solutions?
Left by Patrice Calve on Oct 11, 2005 5:46 AM

Your comment:
 (will show your gravatar)

Copyright © Patrice CalvĂ© | Powered by: