How do you bring Denormalized Values Into your Business Objects?

Question

I have two Tables.

Order - With Columns OrderID, OrderStatusID
OrderStatus - With Columns OrderStatusID, Description

I have an Order Object which calls to the database and fills its properties for use in my code. Right now I have access to Order.OrderStatusID, but in my application I really need access to the "Description" field.

How do you handle this elegantly with good OO design?

Answer 1

You can use a SQL select statement with a Join to the OrderStatus table, and include the columns yo want from each table ...

Select O.OrderId, O.OrderStatusId, S.Descriptiuon
From Order O
    Join OrderStatus S 
        On S.OrderStatusId = O.OrderStatusId
Where OrderId = 23 -- or whatever

Answer 2

Is it a one-to-one relationship between Order and OrderStatus? I guess it depends on the purpose to why you would have an OrderStatus table as I would argue that there isnt actually any need for a separate OrderStatus table?

Basically all that table gives you is the ability to change the description of the order status. From within code, you would then be writing code according to either a predefined OrderStatusID (from seed data?) or via the description. If this is the case then why not have the Order table contain an OrderStatus column which is an integer and that can map to an enum type?

Answer 3

The system I'm currently working with creates an instance of the other business object and sets the Id. The other business object is then retrieved when it is used. e.g.

Order Properties

int OrderId = 5
int OrderStatusId = 3
OrderStatus OrderStatus_ref
{
get
{
    if OrderStatus_ref == null
        OrderStatus_ref = new OrderStatus(OrderStatusId)
    return OrderStatus_ref
}
}

That's the general idea anyways.

Answer 4

My Order object would probably include the status description field (readonly [to non internal classes]), as well as any other similar fields.

Under the hood my getters (e.g. LoadByID, LoadAll, etc) would probably use a View (e.g. OrdersView) that contains all of those descriptive fields. Those description fields are readonly so that you don't accidentally set those fields thinking that you can save the changes to the database.

Answer 5

Usually I prefer to handle lookups as Value objects. I also use Null Object pattern.

public class Order {
  private int statusID;

  public OrderStatus Status {
    get {
      return OrderStatus.Resolve(statusID);
    }
    set {
      statusID = value != null ? value.ID : null;
    }
  }
}

public class OrderStatus {
   public static OrderStatus Resolve(int statusID)
   {
     OrderStatus status = null;
     // read from cache or DB
     ...
     // if not found return Null object
     if (status == null)
       status = new OrderStatus(null, string.Empty);
     return status;
   }
}